1 2 Previous Next 16 Replies Latest reply on Nov 12, 2013 9:59 AM by philmodjunk

    Auto populate foreign key

    aklobby

      Title

      Auto populate foreign key

      Post

           I have a database that is tracking an item through a process. Item fields are imported from a spreadsheet from a flat database format. On that import is the item number, the different places it will visit in the process and a current location code. I am trying to link the places the item will visit to the list of locations it could visit with a join table, itemID to Location ID through the following: 

           fkItem to fkLocation

           I am able to auto populate the item foreign key using a set field script step but not sure how to go about populating the other foreign key automatically. Do do this manually will be very tedious and so would like to make this automatic. 

           Now to make this more complicated, there is a current location code that will change as the item moves through the process. I need to tie that current location code (from the item table) to the location it is visiting in the Locations table. 

           I am probably missing something very simple here, and I can sort of make this all work by manually populating the foreign keys through pop up menus but that will take all day with over 1000 items. 

           Any help is most appreciated. 

           Allison

        • 1. Re: Auto populate foreign key
          philmodjunk

               fkItem to fkLocation are the names of what should be different info that should not match directly in a relationship. fkItem identifies the item. fkLocation identifies the location. (you may already understand that, but I need to be sure that you do.)

               It sounds like you have these relationships in place:

               Items-----<Item_Location>-----Locations

               Items::__pkItem = Item_Location::_fkItem
               Locations::__pkLocation = Item_Location::_fkLocation

               Is that what you have?

               What does one row in your spreadsheet represent? does one row = one item or does one row = one item at one location?

               How is the item's location identified in the spreadsheet?

               If there is data in the spreadsheet that identifies the location, we can use that data via scripts and relationships to set up the necessary records and fk field values.

          • 2. Re: Auto populate foreign key
            aklobby

                 One line in the spreadsheet represents the item with many locations listed by location 1, 2, 3 etc....the join table should probably have location number as what differentiates the item location making the one to many. 

                 The relationship set up is what you suggested. 

                  

                 Allison

            • 3. Re: Auto populate foreign key
              philmodjunk

                   I was afraid that was the case. It's manageable, however, and may not need any scripting to get this job done, but how is the location identified in each of those columns? Does the data in these columns match to values in your Locations table? If not, could your locations table be set up with such matching values?

                   Often, when importing such data, you use one relationship to temporarily match records to related data using imported data in the match field and then use Replace field contents or a looping script to replace that link with an ID number generated in the related table. Thus, you could match records by location name in order to copy over the location ID.

              • 4. Re: Auto populate foreign key
                aklobby

                     The values from the spreadsheet match exactly the values in the locations table. So if in the items table location one is SFC in the location table there is a location called SFC.

                     Allison

                • 5. Re: Auto populate foreign key
                  philmodjunk

                       Then you are in very good shape to do what I am describing. You can initially match values by this field in order to look up the ID numbers.

                       There are two basic approaches you can use to import this data:

                       Import into your join table over and over, each time specifying a different location column in the field mapping. If you have 6 columns of location data, you'll have to import 6 times. You may want to set up a "not empty" validation rule on the location name field to keep the import from generating records with an empty location name field.

                       Then you can use replace field contents to pull in the Location ID into an Location ID field in your join table.

                       OR

                       You can import the data once into a separate table with enough fields to receive all the location name data. Then use a script that loops through the records and creates your new join table records by creating one new record for each item-location combination by checking each location field in turn and generating a new join table record if the location field is not empty.

                  • 6. Re: Auto populate foreign key
                    aklobby

                         I think I have done what your suggestion recommends. I have written a script that loops through all of the records populates the join table FK on the Item side and creates the Location Referral for each location on the Items (Bills) side. The problem I have is how to then match the Location Referral with the Location (committee)  automatically. I am able to do it manually with a pop-up menu but with over 1000 referrals, this is tedious. My script is below (not elegant by any stretch of the imagination, but it does get all of the Location Referrals into the join table. 

                         Thanks so much for your guidance. 

                         Allison

                            Populate Referral Fields_FK

                    Go to Layout [ “Import Utility” (Bills) ]

                    Go to Record/Request/Page

                    [ First ]

                    Loop

                    Set Variable [ $IDbills; Value:Bills::_ID_Bill ]

                    Set Variable [ $Referral1; Value:Bills::Committe1 ]

                    Set Variable [ $Referral2; Value:Bills::Committe2 ]

                    Set Variable [ $Referral3; Value:Bills::Committe3 ]

                    Set Variable [ $Referral4; Value:Bills::Committe4 ]

                    Set Variable [ $Referral5; Value:Bills::Committe5 ]

                    Set Variable [ $Referral6; Value:Bills::Committe6 ]

                    Set Variable [ $Referral7; Value:Bills::Committe7 ]

                    Set Variable [ $Referral8; Value:Bills::Committe8 ]

                    Go to Layout [ “_cDevReferrals” (Referrals) ]

                    View As

                    [ View as Form ]

                    New Record/Request

                    #creates first referral

                    Set Field [ Referrals::_fk_Bills; $IDbills ]
                    Set Field [ Referrals::Referral; $Referral1 ]
                    Set Field [ Referrals::ReferType; "1st Committee" ] #create second referral
                    If [ not IsEmpty (Bills::Committe2) ]

                    New Record/Request
                                        Set Field
                    [ Referrals::_fk_Bills; $IDbills ]
                    Set Field [ Referrals::Referral; $Referral2 ]
                    Set Field [ Referrals::ReferType; "2nd Committee" ]

                    End If

                    #create third referral

                    If [ not IsEmpty (Bills::Committe3) ]
                    New Record/Request
                                        Set Field
                    [ Referrals::_fk_Bills; $IDbills ]
                    Set Field [ Referrals::Referral; $Referral3 ]
                    Set Field [ Referrals::ReferType; "3rd Committee" ]

                    End If

                    #create fourth referral

                    If [ not IsEmpty (Bills::Committe4) ]
                    New Record/Request
                                        Set Field
                    [ Referrals::_fk_Bills; $IDbills ]
                    Set Field [ Referrals::Referral; $Referral4 ]
                    Set Field [ Referrals::ReferType; "4th Committee" ]

                    End If

                    #create fifth referral

                    If [ not IsEmpty (Bills::Committe5) ]
                    New Record/Request
                                        Set Field
                    [ Referrals::_fk_Bills; $IDbills ]
                    Set Field [ Referrals::Referral; $Referral5 ]
                    Set Field [ Referrals::ReferType; "5th Committee" ]

                    End If

                    #create sixth referral

                    If [ not IsEmpty (Bills::Committe6) ]
                    New Record/Request
                                        Set Field
                    [ Referrals::_fk_Bills; $IDbills ]
                    Set Field [ Referrals::Referral; $Referral6 ]
                    Set Field [ Referrals::ReferType; "6th Committee" ] 

                          

                    • 7. Re: Auto populate foreign key
                      aklobby

                           I have also tried a replace field to populate the foreign key but it does not tie it to the proper primary key if I do the replace field. If I use a pop up button, it ties to the correct primary key. Not sure what I am missing when I do a replace field. 

                           AKS

                      • 8. Re: Auto populate foreign key
                        philmodjunk

                             Am I correct that the above script works as far as it goes? It enters the location name into each field of the join table?

                             The next step requires a relationship that matches records by the location name fields so that replace field contents can access the ID number of the related table in the Locations table. Do you have that relationship in place?

                             Is your locations table populated with all of these same locations, each with an ID number and a name that exactly matches?

                        • 9. Re: Auto populate foreign key
                          aklobby

                               Yes, the script enters the location name into each field of the join table and places the UUID I have used for the Item into the foreign key of the join table. The next relationship is the foreign key for the location equaling the ID_Location. The Location table uses an auto entered serial number between 1 and 29 (there are only 29 possible locations which is why I went with a simple serial number instead of a UUID). 

                               ID_Item --> fkReferral

                                                    fkLocation -->ID_Location

                               I am unclear on how to get then the foreign key for location to hold the id of the Location without a popup. I have created a calculation field on the join table that substitutes the text of the location name with the same serial number of the Location, and could use that as the match instead of the foreign key, but I am still not able to match everything properly. I don't know what the calculation should be for the replace field script step.

                          • 10. Re: Auto populate foreign key
                            philmodjunk

                                 There are two different match ups in two different relationships between your join table and the location table. The first is by location name. The second is by location ID. You don't want to use location name in other parts of your database, that's what matching by location ID is for and is a more secure way to link data, but matching by name is the only possible match immediately after your script has been performed.

                                 So you need something like this in your relationships:

                                 Locations------<JoinTable>-----Locations|Name

                                 Locations::__pkLocationID = JoinTable::_fkLocationID
                                 Locations|Name::LocationName = JoinTable::LocationName

                                 Once you have run your script to populate JoinTable::LocationName with location names, you can use:

                                 Replace Field Contents [ no dialog ; JoinTable::_fkLocationID ; JoinTable|Name::__pkLocationID ]

                                 To copy over the needed ID so that the relationship that matches by ID's is functional.

                                 Locations and Locations|Name would be two Tutorial: What are Table Occurrences? with the same data source table.

                                 For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                            • 11. Re: Auto populate foreign key
                              aklobby

                                   Thanks, Phil. 

                                   One more question: I get that Locations and Locations Name are two TO's of the same data source (Locations or Location Name?), but is the join table a new table with just the foreign key and a LocationsName field in it? 

                                   Allison

                                    

                                   Note: Location Name is the join between Item and Location

                              • 12. Re: Auto populate foreign key
                                philmodjunk

                                     The join table is the table into which your script is already setting up these records.

                                • 13. Re: Auto populate foreign key
                                  aklobby

                                       Thanks Phil, 

                                       The import of the records went well and the replacing the field contents worked for about half of the records. I am still investigating where I may have an issue and why the other records are not picking up the ID. 

                                       Thank you again for all of your help. 

                                       Allison

                                  • 14. Re: Auto populate foreign key
                                    philmodjunk
                                         

                                              I am still investigating where I may have an issue and why the other records are not picking up the ID.

                                         I'd look for possibly very minor variations in a location name. The presence or absence of a single character, such as a space or period would be enough to keep the records from matching as expected.

                                    1 2 Previous Next