Enter find mode and specify a lone ! operator in the field that contains the duplicate values. Perform the find.
This can be a manual find or scripted.
Replace Field Contents can then "mark" the entire found set. Sort records can group them by duplicated value.
I did not state that Prior to executing the above script to find duplicate records, I am starting with a found set. I cannot use the ! to find the duplicate records again because it finds them in the source table, not in the found set. If the script finds three duplicate/matching records, for example, the first of the three will not receive an "X" in the Mark field, only records 2 and 3 will have an "X" in the Mark field. I need to be able to identify that number 1 matches number 2 and 3.
Thank you for further thoughts.
Note that I am not recommending that you use the above script as it was not intended for the purpose to which you are attempting to use it. (It is designed to keep one record out of each group of duplicates and that's why the first is not marked.)
Can you more fully describe the found set that you are using? What criteria? Does it include both duplicate and unique values?
Most importantly, will a record in the found set possibly be a duplicate of a record that is NOT in the current found set?
Thank you. This is a subscription database. One customer can have made multiple orders as they renew their subscription over the years. I can also have a customer that is new and only has one record, or I can have a customer that tried the subscription once and never renewed. Therefore, I have a combination of duplicate and unique values. I can also have a customer who is a duplicate that is not in the found set. I have a CustomerID field and an Expiration field among others. My order entry form computes the expiration date and places that date in the Expiration field. Those expiration dates come from a table that identifies the quarter by name and the quarter number. For example, my June 2015 expiration quarter is number 30 and my September quarter is number 31 and so on.
Every quarter I send out an email newsletter. It is easy to do the sendouts as I do a simple find on the expiration field for those customers who are eligible to receive a newsletter. Find Expiration >29. This works great.
On the other hand I have to send out renewal letters and this is where the problem lies. I only want to send renewal letters to those subscribers (based on CustomerID) who have not renewed their subscription. I want to send renewal letters to those subscribers who have not renewed in the prior two quarters. The first thing I do is a find to identify those subscribers who are eligible to get a renewal letter. I say eligible because when I do my find I am picking up not only those who should receive a renewal letter, but those subscribers who are eligible to receive their subscriptions way into the future, so I need to exclude them.
My first step is the find to produce customers in the prior two quarters who have not renewed and along with that comes those who have renewed. In an ideal world I could filter this found set by the ! operator and omit duplicates because I know they have renewed and are eligible for their newsletter. The next step is to constrain the found set and exclude all current subscribers from the found set because they do not get a renewal letter. I am then left with lapsed customers who should get a renewal letter (those in Q27 and Q28 who have not renewed). For example, Find >Q27 gives me my current subscribers and those in the prior two quarters who should get a renewal letter (Q28 and Q29)
I spent countless hours trying to get the ! operator to work only to find that it doesn't work within a found set. I thought I found salvation when I discovered the" identify duplicate records within a found set script" in the Knowledge Base. Woe is me when I discovered that it found duplicates of the original record and did not identify the original record as matching the duplicates which led to my post on this forum.
I currently export my found set to Excel and do the filtering through a macro, but really need to find a way to do it within Filemaker 12.
Perhaps my approach in Filemaker is flawed. I am grateful for your help and any ideas you may have to fix my current approach to the issue or to do it in a different way. I originally thought I could do this as a calculation, but I could never get my head in the right place to find the formula so I tried to set it up as a filter that is so easy to do in Excel.
You need a table for customers, one record for each customer, no duplicates and a table for subscriptions. The two can be linked by Customer ID and the relationship can be sorted to make the most recent subscription record the "first" related record. Each subscription record can use a calculation field that show the status of the record as "current" or "expired". This can be an unstored calculation field that compares its expiry date to Get ( CurrentDate ), or, for faster finds and sorts on this field, a Script can periodically find all "current" subscription dates with ExpiryDate < Get ( CurrentDate ) and change their status to "expired".
This should then enable you to perform a find for all customers (on the customers table) with a date showing it as recently expired, but an omit request can omit those that also have a subscription record that is "current".
That should then produce the desired list of potential renewal customers without any duplication.
Thank you. I do have a customer table that has no duplicates and an orders table which includes the Expiration field that are linked by CustomerID. I have some additional learning to do to execute your solution. Thanks again for your help.