omitting duplicates dependent on count of duplicate records in combined fields



  • 1. FREEZE WINDOW BUG - please test file for me
    Hello, Could someone please test a small test file on win XP with FMP 9? Email me privately and I will send the file to you. For some reason the freeze window script step does not work on either of my computers. email me here XXXX@XXXXX.COM Darren
  • 2. feed info to customers?
    fm 6/windows/server 5.5 soon migrating to 9... I am working on a solution to allow customers to access status info on deliveries I am doing for them. It just hit me, why don't I feed their status info directly to them like in a little weather bug info window? Is that possible with 9 and the fhp api? Can anyone point me toward published info on how to do it?

omitting duplicates dependent on count of duplicate records in combined fields

Postby Richard Winters » Fri, 19 Jan 2007 18:42:29 GMT

I have two fields:

I first find all duplicates of PatientID.
Then I would like to omit only the records where the duplicate count of 
the same ProviderID is the same as the duplicate count of the PatientID.

For example:

PatientID  ProviderID
1              1
1              1
2              1
2              2
2              1
3              1
3              1
3              1

I would like for the records of PatientID 1 and 3 to be omitted.

How do I go about doing this?
I would guess it involves creating variable $combinedID = PatientID&" : 

I am having difficulty figuring out the scripting of this.

Thanks much!


Re: omitting duplicates dependent on count of duplicate records in combined fields

Postby Remi-Noel Menegaux » Sat, 20 Jan 2007 00:12:22 GMT

Are you sure, from the example you give, that what you want is right, when 
you say "I would like for the records of PatientID 1 and 3 to be omitted", 
because if I read you correctly, I'd expect 3 records of PatientID 1, 2 and 
4 be omitted, instead of only 1 and 3. Or else omit 2, 4...
Please clarify to make me understand better.


Re: omitting duplicates dependent on count of duplicate records in combined fields

Postby Richard Winters » Sat, 20 Jan 2007 01:54:50 GMT

The PatientID needs to have duplicates and the StaffID needs to be the 
same for each of the corresponding duplicate PatientID records.

For example,

PatientID  StaffID
ron                a
ron                b
ron                b
george         c
george         b
george         a
mary             a
mary             a
mary             a
joe                b
theresa        b
theresa        b
theresa        b
theresa        b
rochelle       c
rochelle       c

The result would OMIT:

mary. There are 3 instances where PatientID="mary" and 3 instances 
where StaffID="a".
The same StaffID ("a") for each of the PatientID="mary" duplicates.

theresa. There are 4 instances where PatientID="theresa" and 4 
instances where StaffID="b".
The same StaffID ("b") for each of the PatientID="theresa" duplicates.

rochelle. There are 2 instances where PatientID="rochelle" and 2 
instances where StaffID="c".

The result of the script would INCLUDE:

ron. There are three duplicates of PatientID="ron". However, there are 
two different StaffID (1 "a" and 2 "b") associated with PatentID="ron".
george. There are three duplicates of PatientID="ron". However, there 
are also three different StaffID ("a","b", and "c").
joe. There are no duplicate records where PatientID="joe".

Thanks much!


On 2007-01-18 07:12:22 -0800, "Remi-Noel Menegaux" 
<> said:

Re: omitting duplicates dependent on count of duplicate records in combined fields

Postby Grip » Sat, 20 Jan 2007 09:26:35 GMT

our description is still somewhat unclear. Is this process being
applied to all your records or just a found set? How are your tables
set up and what exactly are you trying to accomplish? Is your end
result the list of patient names or a found set of the patients and

Two methods come to mind.

The first is to create two self-join relationships. One new table
occurence is PatientSelf and it's related to the original table based
on PatientID = PatientID. The other TO is CalcPatientStaffSelf based on
a newly defined text calc field = PatientID & " " & StaffID.

Add a new calc field (defined in the context of your original table):
Dupes = ( Count(PatientSelf::PatientID) =/=
Count(CalcPatientStaffSelf::PatientID) )

That field will return 1 for the records you want to find and 0 for
those you don't. You can do a find and get a found set of patient and
staff records.

The second one method is to write a script with a couple nesting Loop
and If statements. This will give you a list of Patient IDs. I
haven't tested this, but you get the idea...This will give you a global
field with a list of the relevant PatientIDs.

Go To Record First
Set Variable $patID = PatientID
Set Variable $staffID = StaffID

If $patID = PatientID and StaffID =/= $staffID
Set $Patients = $Patients & "|" & PatientID
Go To Next Record (exit after Last)
Exit Loop If $pat =/= PatientID
End Loop
End If
Exit Loop If $pat =/=PatientID
Go To Record Next (Exit after last)
End Loop

End Loop

Set Field gNames = $Patients



Richard Winters wrote:

Similar Threads:

1.Scripting omitting duplicate records in found set?

Dumb question of the week. Filemaker 6 (client won't pony up for upgrade 
:-). I've created a file that does the counter thing with a self-join 
that flags records as "Unique" or "Duplicate". It's based wholly on a 
single email address field. That part works fine when I'm in Show All 
Records set; however when I'm in a smaller set of found records within 
the same file it doesn't work as all of the records in the file are 
being compared whether they're in the found set or not (at least that's 
what I think is happening). My need is simple: if I'm in a found set of 
email addresses and there are duplicates, I want to script the omitting 
of any dupes so we can export that particular group of email addresses 
while not deleting anything in the found set or any other existing 
records in the file. If I can't get the counter thing to work, can 
anyone suggest or show me a simple looping script to do what I'm 
describing? Many thanks for any suggestions. I'm sure this won't be 
rocket science for 99% of you!


2.Count repeat of value across all records (and marking duplicates only)

You may use the Count function :
You may first create a selfrelationship with 'Name' as link, and a field 
'Constant' (calc) = 1. Then do NoOfCount (calc) = 
For example looking for Duplicates I do :
Duplicate (calc) = Case( Count(Yr Link::Constant) > 1, "Duplic"; "")
The only problem I have doing that is that if there are the same value 
twice, for example, both records have the word 'Duplic" in the 
'Duplicate' field. Which one to choose deleting ? So I have two ways to 
continue :
- I add another field named 'test', that I empty first, then I choose by 
hand the extra copy(ies) I want to delete in putting an "X" in 'Test'. 
Then I search for "X" in Test and delete all found records. A little 
complement to this is that I fill 'Test' in all Duplicate records with 
an "X", and I choose the reverse ie I take away the "X" in the ones I 
want to keep. It's quicker if there are many duplicates for the same 
field (here 'name'). I often make a little script named 'FlipFlop' 
assigned on that 'Test' field that puts an X if there none and a none if 
there is one, so just clicking on 'Test' puts an "X" or removes the 
existing one. I can import those little scripts almost as is from file 
to file.
- I make a little more complex script, where I first 'find' all 
Duplicates, I sort them by 'Name', put the value of 'Name' into a global 
gName, then I make 2 loops one inside the other : the first to see if 
Name = gName, the 2nd to skip the first and put an "X" in 'test' of the 
next found in the loop,and also SetField(gName,Name), etc. to close both 
loops. Easier to do than to explain. I can send an example to anyone who 
wishes so.
Nevertheless, the best is to avoid duplicates at record creation. But if 
you import ...
Last, there is another method given in the Help, which is supposed to 
isolate the other duplicates and not the first one, but I never got to 
make it work, as when set it puts 'duplicates' in all records (of course 
the word 'Duplic' disappears when hitting on it but it fools the user). 
Is there a trick I missed ?

"GJPeacock" < XXXX@XXXXX.COM > a rit dans le message de news: 
> Is there a way of using a calculation field to tell me how many times 
> a
> value has been used/entered in the same field across all the records?
> I have a field and i need to know when the value has been entered 3
> times or more in previous records...  so i am basically looking for a
> count function.
> So if field NAME contains the same name 3 times or more.
> I have been looking through the functions list but cant seem to find
> one that will do this, as always, any advice is greatly appreciated.
> Gavin

3.Counting records with duplicates

I have a file with 1000 names.  About 1/2 of them are duplicates, some 
entered two, three or more times.  I would like to generate a report that 
gives me a total count of the individuals.  I have created a report with a 
sub summary section and entered the name.  I found all records and sorted by 
name.  Now the report is listing each person once (that is good). I entered 
a field for the Record Number but it is giving me a total of all the people 

I am looking for a number like 450, meaning there are 450 individual names 
and omitting all the duplicate records.

I don't know how clear I am <sigh!>, but can this be done?  Where am I going 


4.Combining several fields into one, omitting empty lines?

5.aggregate field X for duplicate field values Y

I'd like to create a field (a calculation/summary field?) that 
calculates the total value of a field Z whose field X matches a grouped 


x: friuit  y: banana  z:19
x: friuit  y: apple  z:10
x: vegetable  y: carrot  z:3
x: vegetable  y: tomato  z:12
x: vegetable  y: squash  z:2

therefore, the totals woul be:
  fruit: 29
  vegetable:  17

and th aggregate calculation field would compute as:
x: friuit  y: banana  z:19  c_aggregateField: 29
x: friuit  y: apple  z:10  c_aggregateField: 29
x: vegetable  y: carrot  z:3  c_aggregateField: 17
x: vegetable  y: tomato  z:12  c_aggregateField: 17
x: vegetable  y: squash  z:2  c_aggregateField: 17

however, I'm stumped as to how to implement this in Filemaker.

any ideas?

thansks in advance.

6. counting omitted records

7. Duplicating set of records

8. Deleting duplicate records in Filemaker Pro 7

Return to filemaker


Who is online

Users browsing this forum: No registered users and 70 guest