2 Replies Latest reply on Dec 2, 2013 6:38 AM by Cadburys

    Unique records



      Unique records



           I know that there are similar posts ...but I dont get them. I am very very new to FM and am struggling to get my head around the issue I am having.

           I have a db that contains a table called wodata.  Wodata is populated via an import script which references a .csv file.  The data is very repetitive in that there could be 15 rows of information where the first 5 fields contain the exact same data. eg.

                          4                     WW1303-11.btw                     HK00003223W                     8414074                     WW1303/11                     10
                          4                     WW1303-11.btw                     HK00003223W                     8414074                     WW1303/11                     12
                          4                     WW1303-11.btw                     HK00003223W                     8414074                     WW1303/11                     14
                          4                     WW1303-11.btw                     HK00003223W                     8414074                     WW1303/11                     16
                          4                     WW1303-11.btw                     HK00003223W                     8414074                     WW1303/11                     18
                          4                     WW1303-11.btw                     HK00003223W                     8414074                     WW1303/11                     20
                          4                     WW1303-11.btw                     HK00003223W                     8414074                     WW1303/11                     22
                          4                     WW1303-11.btw                     HK00003223W                     8414074                     WW1303/11                     24
                          4                     WW1303-11.btw                     HK00003223W                     8414074                     WW1303/11                     8

           What I would like to do is get the first five columns to go into one table...BUT to only show the first occurrence... effectively creating a parent table for the repetitive info.

           I would still like the original import to take place as this would then be the child table.

           Does this make sense? Can anyone help me/guide me with really simple step by step instructions if this is even possible?

           Thank you in advance...


        • 1. Re: Unique records

               You can import into two tables by importing twice to get your parent and child tables. To populate the parent table, define a table that has all 5 of these fields that might be the same and add a text field with an auto-enter calculation similar to this:

               Field1 & " " & Field2 & " " & Field3 & " " & Field4 & " " & Field5

               Specify unique values and validate always as field options for this field. Now you can import into this table and the validation options will omit the duplicate records, importing one record for each unique combination of values for these 5 fields.

          • 2. Re: Unique records

                 Thank you PhilModJunk!!

                 So easy once you know how :-)