Eliminate duplicate records

[bob sherman]

My program allows users to record a client's visit dates; each visit is recorded with a new record. Each record contains visitor's name, visitor's ID number, reason for visit and the user's name. I would like to be able to pull up a list of visitors by the user name without every visit being displayed. Thus generating a single client list. I have solved this problem by using a "first visit" field and placing a "yes" in that field for the first visit only. I can then do a find on the user name and "yes." This works. But there has to be a better way. For example, is there a way to do a find on all visits by user name, and then add a script that will eliminate duplicate ID numbers?

[Fitch]

Discussed in this recent thread . Also here .

[bob sherman]

Thanks for the threads, but I don't want to delete any records. And I didn't really se the relationship of the second thread to what I need to accomplish, but I'll read them again. The user needs be able to do a find on his or her name and receive an unduplicated list of clients.

[Fitch]

You want to Omit duplicates. The first thread I mentioned shows you the technique; the script is identical, except where the script says "Delete record," you'll instead use "Omit record." Here it is again:

Find(records for your user, let me know if you need help with this step)

Sort (by visitor name)

..Set field (gCompare, "")

Loop

..If(gCompare = visitor name)

....Omit record

..Else

....Set field (gCompare, visitor name)

....Go to record(next, exit after last)

..End If

Exit Loop If (Status (CurrentFoundCount) = Status (CurrentRecord) )

Exit Loop If (Status (CurrentFoundCount) = 0 )

End Loop

The second thread I mentioned includes links to articles about dealing with duplicates. Here they are again:

http://www.filemaker.com/ti/103852.html

http://www.filemaker.com/ti/104772.html

http://www.filemaker.com/ti/104753.html

[peg]

If I'm following you correctly, perhaps you could make the visit date field a repeating field? Then each time Mr. Jones visits, you pull up his record and add the next visit date. This may be simplistic, but may work, depending on volume, I suppose. Then, in one layout, you can have displayed all of the dates for each visitor and in another layout you can have just the first visit date listed, so it could be a single line per customer. There's probably a way to display the last date in the repeating field instead of the first, but I don't know that part, unfortunately. Hope this helps! Peg ([email protected])

[Ugo DI LUCA]

My understanding of the "unduplicated" list is slightly different.

I think Bob is taking his list of visitors from a "Line Item", thus leading to a list of all appointments for all visitors, while he wants to see only one record per visitor.

As you said you have a Visitor_Id, I assume you have a Visitor File, with one record per "Customer".

The ValueListItems calc is then what you need here.

1. Create a Value List of Visitors involving a relationship Main:Username::Visits File:Username ---> call it 'RelatedVisitorsList', picking the Visitor_Id field.

2. Then, in your Main file, create a calculation

c_VLItemRelated = ValueListItems(Status(CurrentFileName),"RelatedVisitorsList")

3. Create a relationship from the Main File to the Visitor File (not the Line Item)

Main:c_VLItemRelated::VisitorFile:Visitor_Id

4. Draw a portal using this relationship or Script a Go To Related Record to the Visitor File.

If you don't have a Visitor File, then you could use the Max() function with a selfJoinOnVisitor_Id in the Visit File to purge the list and retrieve the last entry for that visitor (c_showlast = Case(Max(SelfJoin:Record_Id = Record_Id, 0,1), and use it as a sortkey for your relationship Main:Username::Visit File:Username.

If you want a portal view, it would be rather straightforward dropping in the rows another calculated field that would display the customer or not based on the previous calc Choose(c_Showlast,Customer Name,""), still sorted by the c_showlast.

If you want a print, this would involve (for example) a loop and flag through related records, stopping the loop when c_showlast>0.

[LaRetta]

Hi Bob,

I think there's a bit of confusion here. Your subjectline is 'Eliminate Duplicate Records' and I think that threw everyone off. You twice referred to wanting a unique list of User Names and their corresponding unique Visitor Names and that is very simple.

Create a layout Columnar List/Report. Select 'Report with grouped data'. Select two fields only - your User Name and Visitor Name. Organize by Category User Name then Visitor Name. Sort by User Name then Visitor Name. Don't select sub-totals. Let FM create the script for you so your 'switch to this layout, sort order and preview mode' script-steps are automatically created.

This will provide a list by User Name with each unique Visitor Name listed. You don't need a body (for each individual visit-date record) in a list layout at all. Sub-summaries provide the unique aspect for you.

Then add an Enter Find Mode [Pause] script-step (in which your Users would find either a specific UserName or * for all Users) to the beginning of the above script. And remember that, upon leaving this script after adding your find script-step, you will want to KEEP the sort order originally stored when you created the layout.

If you modify your layout, by Layouts > Part Setup, select UserName, change and click 'page break after every one occurance' your list will have each User with their unique Visitors on a new page.

A complete listing of all Users as separate lists can be generated searching for *, or a list of only one UserName can be produced by finding only one User. Both can use the same report because the only thing that would ever change would be what you enter in the User Name field for your find request.

Cheers,

LaRetta

[bob sherman]

Thanks for everyone's ideas. I'll do some more work on this and let you know what I end up with. The necessary result will be a one step procedure (single script and button) that the user can click on, to find his/her unique caseload taken from the user's file of individual visits.

Bob