9 Replies Latest reply on Sep 30, 2014 10:49 AM by philmodjunk

    Importing Records with a Loop

    flamecoder

      Title

      Importing Records with a Loop

      Post

      I need to set up a script to import records. I'm going from a one table db to multi tables db so while importing I need to relate records with scripts I already have in place for making a new related record in another table. My basic question first is can I import the records one at a time and have them go thru the script steps then when finished it goes to the next imported record or do I need to import them all into a temporary table first and loop thru it?

      Further question will be on the new db I have a popup list to display names but it relates to a name ID from another table. If the imported record name field is "John" how do I tell that layout to set that field since it needs the name ID to actually relate to the foreign table?

        • 1. Re: Importing Records with a Loop
          philmodjunk

          You'll need to import all at once, but whether you need to import into a "staging table" and then move the data from there is not a question I can answer from the info provided.

          Further Question...

          Set up two relationships, one that match records by ID and a second that matches records by name. This requires two occurrences of one of your tables to get the two relationships. Use the name matching relationship with Replace Field Contents to copy over the matching ID. This only works if: names are unique and your names match perfectly (no Phil instead of Phillip, no typos...)

          • 2. Re: Importing Records with a Loop
            flamecoder

            Made a sample db to try to understand the Further Question part... Doesn't work the way I have it set up. Perhaps you can see my wrong?

            • 3. Re: Importing Records with a Loop
              philmodjunk

              Table 1 would need an artist name field and such is missing from your sample file. That then allows you to match Table1::artist_Name to Table 2 2::artist_Name.

              Then, on a layout based on Table 2 2, you can use Replace Field Contents to copy over Table1::__pk_Table1ID to Table 2 2::__kp_ArtistID.

              • 4. Re: Importing Records with a Loop
                flamecoder

                Don't understand the last part. Table1::__pk_Table1ID to Table 2 2::__kp_ArtistID. Never really intended to use Table1::__pk_Table1ID for anything.... I somehow need to translate Artist_Name in Table 2 to give Table1 the primary key for field Table1: _kf_ArtistID... are you doing something special in "Replace Field Contents"?

                • 5. Re: Importing Records with a Loop
                  philmodjunk

                  Sorry, I misread your example relationships. And a second look leaves me confused.

                  Which table represents the table of newly imported data that contains an artist name but to which you want to link by Artist ID?

                  Which represents your existing table of artist_ID values and Artist Names.

                  Table 1 doesn't seem to be either of those tables as it doesn't have the expected Artist_Name field that should be in both tables.

                  • 6. Re: Importing Records with a Loop
                    flamecoder

                    Table1 will hold the imported data. A popup menu will holds the _kf_ArtistID and displays all the names from Table2. This is set up so pop up box selects from a value list using values from Table2 but only shows values from second field Artist_Name. See attached on how pop up box for Table1:_kf_ArtistID is set up.

                    • 7. Re: Importing Records with a Loop
                      philmodjunk

                      Then table 1 is missing the artist name field.

                      quoting from your original post:

                      If the imported record name field is "John"

                      • 8. Re: Importing Records with a Loop
                        flamecoder

                        Since this is only to get the data into the new db I resolved this by

                        Putting the imported name into a variable.

                        Perform find in Table2 for the distinct name.

                        Putting the __kp_ArtistID from the found name in a variable.

                        Set the field _kf_ArtistID to the ArtistID variable and name shows in popup box.

                        • 9. Re: Importing Records with a Loop
                          philmodjunk

                          That works, but a Single Replace Field contents could have done the same thing provided you had the tables and relationships correctly set up.

                          But hey it worked! on to the next task...wink