10 Replies Latest reply on Feb 19, 2014 4:51 PM by arronman

    Select most recent dated records, ignoring older duplicates

    arronman

      Title

      Select most recent dated records, ignoring older duplicates

      Post

           I have a large set of records (1000+) each of which contains data of individual visits to shops. There are approximately 400 shops, some of which have been visited up to 4 times, many others less often.

           The goal is to sort through the records, select only one of each record, filtering out the rest, but displaying only the shop record of the most recent date.

           I have been assisted in the process of making a manual import from one table to another and using a unique ShopID field to copy across only one record for each shop, then using a portal in Form View to display different visit data on separate lines of the portal. All good.

           However when the layout is viewed in List View, only the oldest visit data is displayed. I think this happens because the manual import selected only the oldest related record.

           I still need to find a method of displaying in List View each shop, but displaying data fields from only the most recent visit. It needs to be easily updateable.

        • 1. Re: Select most recent dated records, ignoring older duplicates
          philmodjunk

               This post seems very familiar. Did you post it previously?

               

                    ...then using a portal in Form View to display different visit data on separate lines of the portal. All good.

               

                    However when the layout is viewed in List View, only the oldest visit data is displayed. I think this happens because the manual import selected only the oldest related record.

               If you set up a one row portal on your list view just like the portal that is "all good" but then specify a sort order that sorts by visit date in descending order (or by an auto-entered serial number in descending order), your one row portal in the body of your list view layout will only show the most recent related record.

                

          • 2. Re: Select most recent dated records, ignoring older duplicates
            arronman

                 Yes, as you discovered, I reformulated my query as a new post.

                 Thank you, I now have a working one line portal which displays the latest date, but I have discoverd there are secondary, but significant problems with the overall structure:

                 I need to be able to create new shops.

                 Trouble is, I can create the new record in the original "Table" with all the records, but this, or any other new shop record is not present in the separate "Shops" record (manually filtered using Unique ShopID on Import to the second table)

                 I can create the new shop record in "Shops", but then it's not present in "Table", but it needs to be so that on-going reports can be generated, which use past and present data.

            So. Can I set up a procedure (in Filemaker Pro 11) to automatically duplicate the newly created new shop record in the other table? I'm not overly worried about file size and speednas most of the shop records in each file (separate geographical areas) have already been created

            • 3. Re: Select most recent dated records, ignoring older duplicates
              arronman

                   Another problem ...

                   When I create a new line in the portal and commit by clicking outside the portal I get:

                   "ShopID is defined to require a value, but it is not available on this layout. Use another layout to assign a value to this field."

                   I am forced to revert. I have placed the field "ShopID" all over the place thinking that is the problem, but problem persists.

                   Don't know how to resolve.

              • 4. Re: Select most recent dated records, ignoring older duplicates
                philmodjunk

                     Yes, you need to create a record for each new shop in the shops table, but you don't actually need a record in the visits table until you actually need to log a visit.

                     If you have a portal to visits, this can be as simple as adding the new data to a blank row in the portal. Or you can click a button on the shops layout that copies the Shop ID into a variable, switches to the Visits layout. Create s anew record and uses set field to copy the value of the variable into the shopID field of the new visits table record.

                     

                          When I create a new line in the portal and commit by clicking outside the portal I get:

                     And exactly how are you creating a "new line in the portal"?

                • 5. Re: Select most recent dated records, ignoring older duplicates
                  arronman

                       Created portal long ago: can't remember where the option was, but there is always an empty row displayed below the most recent entry. Portal  (in Shop table) accesses records from Table.

                  • 6. Re: Select most recent dated records, ignoring older duplicates
                    arronman

                         Clarification: ie by entering any data in any field of the "new line"

                    • 7. Re: Select most recent dated records, ignoring older duplicates
                      philmodjunk

                           Is ShopID a match field defined in the portal's relationship? ("Allow creation of records via this relationship" is what provides that "add row" to your portal, but you said that you were "Creating a new blank row" and that puzzled me.)

                           How does ShopID get a value in your Shops table? It should be an auto-entered serial number field.

                      • 8. Re: Select most recent dated records, ignoring older duplicates
                        arronman

                             ShopID is not a relationship match field. And you're right about "Allow creation of records via this relationship". Thanks

                             ShopID is auto calculation "Shop Name & Suburb". At one stage I had a serial number but that - I thought, probably incorrectly -  was creating issues when importing from "Table" to "Shops"

                             Should I try resetting the ShopID field to serial number in a copy of the file containing the original records, then go through the process again of importing from one table to the other?

                             Is it possible to create unique serial numbers to recordsd that already exist?

                        • 9. Re: Select most recent dated records, ignoring older duplicates
                          arronman

                               Re serial numbers: original file does have auto generated serial numbers in a field "ID" so I can use that.

                               Will now re-process everything using the field "ID" across both tables and let you know what happens.

                          • 10. Re: Select most recent dated records, ignoring older duplicates
                            arronman

                                 Problem solved using ID containing serial number.

                                 New shop record created in Shop table. OK

                                 New visit entered via portal in Shop::new record creates new record in Table. Excellent.

                                 I think it all works as intended now, but have to test the reporting structures previously set up.

                                 Thanks so much for your patience and assistance.