2 Replies Latest reply on Jun 18, 2017 6:57 AM by erolst

    Group addresses and select latest update

    cbuck

      I have a table of addresses. There are a lot of duplicates in there. Each record has an update field. What I would like to do is group by address and then only select the one with the latest update based on an update (date) field that I have.  What is the easiest way to do this in Filemaker?

        • 1. Re: Group addresses and select latest update
          philmodjunk

          It depends on your data, the design of the table and how you got the duplicates in the first place.

           

          You can enter Find Mode, put an ! in the address field and perform the Find to find all records with duplicates in the field, then sort on that field then your modification date field to group them by address with the most recent first or last.

           

          But minor differences in spelling or abbreviation will keep duplicate addresses from being identified as duplicates.

          1 of 1 people found this helpful
          • 2. Re: Group addresses and select latest update
            erolst

            You could use a script to sort by address and update descending field, then select (i.e. collect the id) the first record in every group.(Search for 'Fast summary' to learn how this can be tuned.)

             

            Or create a self-join by address, and a calculation field like

             

            dateUpdate = Max ( SelfJoin::dateUpdate )

             

            and search this field for the value 1. Might take a bit since the value is par force unstored.

             

            Alternatively, try this SQL code

             

            ExecuteSQL ( "

              SELECT a1.id

              FROM Addresses a1

              WHERE dateUpdate =

              ( SELECT MAX ( a2.dateUpdate ) FROM Adresses a2 JOIN a1 ON a1.address = a2.address )

              " ; "" ; ""

            )

             

            or this

             

            ExecuteSQL ( "

              SELECT MAX ( dateUpdate )

              FROM Addresses

              ORDER BY dateUpdate

              GROUP BY address

              " ; "" ; ""

            )

             

            and use the found IDs via a relationship (another self-join) to isolate the matching records.

             

            Both untested, so maybe do not waste too much time on it.

            1 of 1 people found this helpful