10 Replies Latest reply on Jul 3, 2017 7:53 AM by philmodjunk

    Table of unique records

    cbuck

      I recollect reading somewhere but can't seem to find instructions on  using the value list to find unique records and then using that to create a table. I have a table that contains several duplicates and would like to only show one unique record and basically consolidate the rest. Curious if there is a way to do this.

        • 1. Re: Table of unique records
          FileKraft

          BTW in FMP16 is a new UniqueValues functions.

           

          If I understand you correctly you want a "normalized" table where you have only one unique record of an entity and want to create that for each distinct field.

           

          So what version of FM are you on? 16?

          2 of 2 people found this helpful
          • 2. Re: Table of unique records
            wimdecorte

            The ValueListItems() function will omit dups for you and give you the list you are after

            So will using ExecuteSQL() and using DISTINCT() in your query

            So will using the new UniqueValues() function in FM16

            3 of 3 people found this helpful
            • 3. Re: Table of unique records
              philmodjunk

              But if you are going to create an actual table, you can use Import Records to move the records into the table. Set up a unique values, validate always, field option and the dups will be omitted from the import.

              2 of 2 people found this helpful
              • 4. Re: Table of unique records
                coherentkris

                Other options are to set up a self join relationship to identify duplicates or find ! on the field.

                You dont need a value list or an extra table to identify duplicates for manual cleanup/consolidation

                • 5. Re: Table of unique records
                  cbuck

                  Thanks I found more info about this and it appears like it will help get unique values. How would one go about building a new tablet that is a subset of another table using this uniquevalues function?


                  Can it be done dynamically so as new values are added to the transactional table the uniquevalues table is updated for new entries.?

                   

                   

                   

                  FileMaker Pro 16 Help

                  • 6. Re: Table of unique records
                    cbuck

                    HI Philmodjunk I like the import approach because I would like an actual table to use and add fields that I can customize is there a way though to dynamically have this table update for new records based on new unique values in the other table?  For example the one table has a bunch of addresses but many duplicates. I would like to grab only each unique address and in the new unique table be able to add notes etc to each address.

                    • 7. Re: Table of unique records
                      wimdecorte

                      cbuck wrote:

                       

                      Thanks I found more info about this and it appears like it will help get unique values. How would one go about building a new tablet that is a subset of another table using this uniquevalues function?


                      Can it be done dynamically so as new values are added to the transactional table the uniquevalues table is updated for new entries.?

                       

                       

                       

                      Sure, if records are created under scripted control then it is a simple as checking if the value already exists in the 'unique' table and if not add it there.

                       

                      I'm struggling a bit with the "why?" for needing to keep a separate table with unique values, perhaps there's a better way that does not require maintaining another table with what is in essence somewhat duplicated data.  Can you expand a bit on what you use the unique table for?

                      • 8. Re: Table of unique records
                        cbuck

                        Thanks, I get transactional data on a list of addresses but the data provider basically provides a running data so it duplicates. I could import only the latest but I would like to have the historical data as well for certain reports etc. The main reason I guess for making a unique table listing each unique address is that I would like to keep internal notes in a field. I would then like to show the last transactional record data along with that notes field. I was thinking I could just add an internal notes field to the table and maybe make a self join that pulls the last transactional update. So the notes may be on an old record but it shouldn't really matter though. Just not sure what the best approach would be.

                        • 9. Re: Table of unique records
                          wimdecorte

                          You could keep the notes in a separate able, joined to the transaction data by that transactional record's primary key.

                           

                          Or do you want the same note to show up for the same address, of which you have multiples in the transactional data?

                          • 10. Re: Table of unique records
                            philmodjunk

                            A lot depends on how easy it is to identify truly unique data in your imported data. If that is very clear cut--such as it comes with a customer ID field or something that uniquely identifies each record, you can just keep importing into this field with the validation set up as previously described and it will keep rejecting all records that already have a matching record in the table.

                             

                            You can also update existing records with an "Update matching records in found set" type of import.