I have two fields: PatientID ProviderID 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&" : "&ProviderID I am having difficulty figuring out the scripting of this. Thanks much! Richard
omitting duplicates dependent on count of duplicate records in combined fields
4 posts
• Page 1 of 1
- Mathematica
- xml
- Visual Studio .Net
- hp
- Windows
- Windows XP
- delphi
- MS SQL SERVER
- VC
- MS SQL SERVER
Jump to:
- 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?
Next
omitting duplicates dependent on count of duplicate records in combined fields
by Richard Winters » Fri, 19 Jan 2007 18:42:29 GMT
Re: omitting duplicates dependent on count of duplicate records in combined fields
by Remi-Noel Menegaux » Sat, 20 Jan 2007 00:12:22 GMT
Hi, 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. Remi-Noel 2007011801423316807-drrwinters@yahoocom...
Re: omitting duplicates dependent on count of duplicate records in combined fields
by 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!
Richard
On 2007-01-18 07:12:22 -0800, "Remi-Noel Menegaux"
<rnmenegaux_AT_free.fr> said:
Re: omitting duplicates dependent on count of duplicate records in combined fields
by 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
staff?
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.
//BEGIN SCRIPT
Go To Record First
Loop
Set Variable $patID = PatientID
Set Variable $staffID = StaffID
Loop
If $patID = PatientID and StaffID =/= $staffID
Set $Patients = $Patients & "|" & PatientID
Loop
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
//END SCRIPT
G
Richard Winters wrote:
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
staff?
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.
//BEGIN SCRIPT
Go To Record First
Loop
Set Variable $patID = PatientID
Set Variable $staffID = StaffID
Loop
If $patID = PatientID and StaffID =/= $staffID
Set $Patients = $Patients & "|" & PatientID
Loop
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
//END SCRIPT
G
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! pd
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) = Count(YrLink::Constant). 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 ? Remi-Noel "GJPeacock" < XXXX@XXXXX.COM > a rit dans le message de news: XXXX@XXXXX.COM ... > 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 entered. 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 wrong??? Thanks
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 value. e.g.: 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.
4 posts
• Page 1 of 1
Who is online
Users browsing this forum: No registered users and 70 guest