Keeping Duplicate While Omitting Original

[tussing515]

I have a database where clients can come in for multiple visits under the same Client Number. The report that I pull will have them listed a couple times but what I'd like to do is omit the first time they are listed on the report and only keep the second. I have a script in place that will mark all duplicates with the following script:

 

Go to Record/Request/Page [First]
Replace Field Contents [Perform without dialog, Specify target field 'Mark', " "]
Loop
  Set Variable[$id,'ClientID']
  Go to Record/Request/Page [Next, Exit after last]
  If ['$id = ClientID']
     Set Field ['Mark', '"X"']
  Else
     Set Variable[$id,'ClientID']
  End If
End Loop
Perform Find [Restore] Find records when Mark = X

 

This doesn't solve my problem all it really does is show me which are duplicates. Does anyone have an idea on how I could omit the original and keep the duplicate listing?

[Rick Whitelaw]

Do you have a date and/or time field in this table? If so, it's a simple matter of doing a find on the client id and then constraining or omitting by date . . .

[MartieH]

if you don't already have a date, time or timestamp field, you could probably create a new calc field and use Get(RecordID) - the larger the number of the record ID, the younger the record - to constrain the found set of dupes to the oldest ones.

[tussing515]

Rick - Unfortunately I don't think that would work because the script is for a report that pulls a certain dates worth of clients. It searches for a month and then I get the report with names on it. I don't think the date constraint would work.

 

MartieH- The client ID is unique to the client but not the visit so there is no unique marker for each visit.

[Rick Whitelaw]

You didn't answer my question. If there is a date field there's no reason you can't report for other than "month".

[tussing515]

There is a date field but the report IS for a month a time. Let me try and explain what I'm doing.

 

We see clients for a pregnancy test and an ultrasound. My report pulls all due dates during mm/dd/yyyy to mm/dd/yyyy. If the client has had both services done they show up on my report twice, once for pregnancy test and once for ultrasound. I only want to see the date from their ultrasound which is done at a later visit. I can't only pull ultrasound due dates because then I miss the pregnancy test only dates.

 

Does this help?

[MartieH]

The RecordID is an id number that is generated by FM and is unique to each record - it is not necessarily the same as an ID (such as your clientID) that is created to identify a client. A recordID identifies each record. you can set a calc field to Get(RecordID) and see this number - and use it if you wish. You will have to create this field, of course - but, each number will be unique to each record.

[tussing515]

Do you know a way to tell FM to not show the older recordID without a date constraint through?

[MartieH]

there are probably a couple of ways to do this, but I think that if you create the calc field for the Record ID and if you then sorted on the client ID and the Record ID fields before doing your loop to mark the dupes, then the dupes marked would always be the newest records.

 

I had a more complicated way - but, I don't think it's needed - the sort should do it.

 

hth,

Martie