finding duplicate dates by client

[swf]

I have a database of clients. If client1 is billed for date1, date2, date 3 then I can bill for 3 days. However if the entries for client1 are date1, date1 date3 then I can only bill for a total of 2 days. I have a summary field that counts the number of dates and places the total per client in a report. Obviously sometimes that field overcounts due to duplicates (2 different transactions occuring on the same day)

If I try to first find all duplicates then filemaker sees client1 date 1 as duplicate to client2 date1.

In summary I would like to look for duplicate dates within each individual client and ultimately identify them by bolding them or if possible adjusting the summary field down by 1 for every duplicate. Not sure if this is possible.

any suggestions appreciated.

[spb]

Make a concatenation work field where the concat is something like:

DateToTex [DateField] & ClientIDNumber

Search this field for dupes with an exclamation point.

This is an FM 6 suggestion which will probably work the same way in 7. You can go further and automate hunting down and deleting all but one of the dupes with a script. If you have laying around the old hardcopy manual for FM 5 or 5.5 (I don't know if they had this in 6's), the chapter on Scriptmaker uses an elegant little script to do this as its example. I have adapted this script many times for many purposes.

Steve Brown

[swf]

That works great to isolate a found set of the duplicates. Now how can I do something to show the duplicates. I do not want to delete them but rather place them in bold in a report or place a star next to them. It would be great if I could do that with the find in a script. Eg. The search would isolate a found set of duplicates then go into the field and change all the dates in the date_field in the found set to "DD/MM/YYYY*". One problem I foresee is the field is date format. I guess it could also make a star_field next to it which I could add a * to. Then on my layout I could place date_field next to star_field. Only in the duplicates would a "*" appear. So I guess the question is how to make it place the * in the star_field of the found set?

[spb]

Here is where you'd need the FM script example I mentioned. Essentially, it takes a found set of dupes, sorts it by the key ID field, then loops through the set. It starts with the first record and sets a global field to the key field. Then it goes to the next record and compares its key field to the global. If it is the same (i.e., a dupe), then a one-or-zero flag field is set and it goes to the next record. If the key field does not match, then the loop sets the global field to the new key field and does not set the flag.

When you're done, you have flagged every record but one in each group of dupes. At this point you do a simple find on the flag field and do whatever you want with the set. You could make the flag field itself your star field, for example.

Now that you have control of these, you need to decide how you'd iddeally want the dupe dates to ddisplay. If you wanted them to display in red text (with just one "master" dupe staying black), you could do that with a single calculation field.

Steve Brown

[swf]

I found an example of this looping script in which you set the key field to a global field and loop through each record. If there is a match then my special field is flagged. It works great except for the last record. The loop is exiting before it evaluates the last record. my attempts to move the go to next record further down cause the loop to freeze up and not work. I will post my script.

[swf]

Here is the script

Go to record [first]

set field [global_field, key_field]

loop

Go to record[next, exit after last]

if[key_field=global_field]

set field [flag_field, "x"]

else

set field [global_field, key_field]

end if

end loop

[Søren Dyhr]

Just a quick note, both the use of globals for this purpose as well as the use of concatanations for keys is bygones as techniques by the introduction of 7.0. The former is better changed to the use of scriptparameters and the later is better off being a multicriteria relation. This is especially the case when wan'ing where two short indexes transfered is much faster than their cartesian product and globals are poxy for wan'ing again due to the size of indexes transfered.

--sd

[spb]

I figured that FM 7 probably had a better way to get the job done. However, to fix the problem of the loop not hitting the last record, move the Go to Record [Next, Exit After last] command down between the End If and the End Loop.

It is exiting just before it evaluates that last record. Let it evaluate first, then proceed to the next record.

Steve Brown

[swf]

could anyone explain further how to do this with scriptparameter and multicriteria relation.

[Søren Dyhr]

could anyone explain further how to do this with scriptparameter and multicriteria relation

Try to downlaod this: http://www.filemakerpros.com/DevCon2003.zip and poke deep in the templates and find this file: DUPMARK.FP7.... Then you can get the gist of multicriteria relations by investigating this template: http://www.newcenturydata.com/downloads/filter.zip

--sd