1 Reply Latest reply on Jun 24, 2015 3:56 PM by philmodjunk

    Populate Primary Key when child records are imported first

    DennisFlight

      Title

      Populate Primary Key when child records are imported first

      Post

      I have a solution with 2 tables the child table called Race Data and the parent table called Races

      everyday i import approx 150 records into the child table and of those 150 records approx 12 to 15 are related by the date track and race # (ex. 13 players in race 1 at certain track on a specific date)

      What i would like to do is auto create records in the parent table so the portal shows all the players for each race

      Relationship is between primary key and foreign key

      Here is a link to a similar issue Creating Parent Records from Child Table/Layout/Record

      Child table                        Parent table

      ::Foreign Key                    ::Primary Key (Serial Number)

      ::Date                                 ::Date

      ::Track                                ::Track

      ::Race #                             ::Race #

      ::Player Name

      ::Player #

      One solution i have managed to get to work is to use a calculation field  combining date-track-race# as the id in race data and do the same in the races table..... but i would much rather use the serial numbers for safety.

      Thanks

        • 1. Re: Populate Primary Key when child records are imported first
          philmodjunk

          If you set up a field in the parent table with an auto-enter calclulation that combines the date, track and race number values in a single text field. You can set up a "unique values, validate always" field option on that field and then you can import this data into the parent record and the duplicates will be omitted from the import giving you one new parent record for each unique combination of race, track and date.

          To link the child and parent records, I'd use two table occurrences of the parent table in order to set up two relationships.

          One would match by Date, track and race and the other by foreign key to primary key. Set up the Foreign Key field to auto-enter or look up the value of the matching parent record's primary key. You then use the primary key to foreign key relationship throughout your database, and only use the other relationship to set up the matching values in the primary to foreign key relationship.