6 Replies Latest reply on May 25, 2015 2:24 PM by fp2146

    Duplicate Records Within Found Set Identify First Record Problem

    fp2146

      Title

      Duplicate Records Within Found Set Identify First Record Problem

      Post

      I have successfully used the following script found in the Knowledge Base to identify duplicate records within a found set, however it does not identify or "Mark" the first record of the duplicated records, only those that are duplicates of the first.  I need to be able to identify all the duplicate/matching records within the found set for further manipulation.  Is there anything I can add to this script to help me Mark the first record within the group of duplicates/matching records?

      Thanks for any help.

      Show All Records
      Sort Records [Restore; No dialog] <--- Sort by the UniqueID field in ascending order
      Go to Record/Request/Page [First]
      Set Variable [$id; Value:SampleTable::UniqueID] <-- Sets "$id" variable to be equal to UniqueID field
      Replace Field Contents [No dialog; SampleTable::Mark; " "] <--- Replace the contents of the "Mark" field with a blank (" ")
      Loop
           Go to Record/Request/Page [Next, Exit after last]
           If [$id = SampleTable::UniqueID]
                Set Field [SampleTable::Mark; "X"] <--- Marks record as a duplicate by placing an "X" in the "Mark" field
           Else
                Set Variable [$id; Value:SampleTable::UniqueID]
           End If
      End Loop
      Perform Find [Restore] <-- Finds all records where Mark = X.  You will need to define the find criteria for this script step

        • 1. Re: Duplicate Records Within Found Set Identify First Record Problem
          philmodjunk

          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.

          • 2. Re: Duplicate Records Within Found Set Identify First Record Problem
            fp2146

            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. 

             

            • 3. Re: Duplicate Records Within Found Set Identify First Record Problem
              philmodjunk

              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?

              • 4. Re: Duplicate Records Within Found Set Identify First Record Problem
                fp2146

                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. 

                Thank you.

                • 5. Re: Duplicate Records Within Found Set Identify First Record Problem
                  philmodjunk

                  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.

                  • 6. Re: Duplicate Records Within Found Set Identify First Record Problem
                    fp2146

                    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.