5 Replies Latest reply on May 17, 2016 1:46 PM by bigtom

    Import and Relate Fields Through Script

    BillR_1

      I have an Excel file of store locations with addresses, as well as the related state senator and assemblymember whose district includes each particular store. Every store is entered twice in the spreadsheet (once for senate, once for assembly) so the spreadsheet can be sorted a variety of ways. I know how to import the stores with their address info, and I can repeat the import for legislators, but how do I write a script to make the relationship? There are multiple stores in each senate and assembly district.

       

      Thanks in advance.

      Bill

        • 1. Re: Import and Relate Fields Through Script
          bigtom

          Bill,

          Does the legislator appear on the same line/record in the excel file?

           

          You would need to have at least one piece of data in the store import that will reference the connection to the legislators.

           

          Then use a looping script to run through all the stores or legislators and generate new records in a join table that will hold an ID for the store and legislator.

           

          I would use executeSQL to grab the data and set the fields. First starting with a list of IDs for the loop. This should do about 60-80 entries per second. Not sure how many you have.

          • 2. Re: Import and Relate Fields Through Script
            BillR_1

            Thanks bigtom -- to answer your question, the senator appears on the same line as store A, and then on the next line in the spreadsheet, the assemblymember is on the same line as a repeat of store A. For example:

             

            Store A, 123 Main St., NYC, Sen. Jones

            Store A, 123 Main St., NYC, Assmb Franklin

            Store B, 700 South Ave, Albany, Sen. Peters

            Store B, 700 South Ave, Albany, Assmb Anderson

             

            It was done this way originally in Excel so that you could sort by Senators or Assemblymembers, and see a list of all stores in their individual district.

             

            Bill

            • 3. Re: Import and Relate Fields Through Script
              bigtom

              There are a couple ways you could structure this but going with the idea that all the legislators are in one table...

               

              All the tables need primary keys of course.

               

              Step one is get all the unique legislators and give them a record in the legislators table. How you do this really depends if "Sen." and "Jones" are separate fields or not.  Hopefully there are not two Sen. Jones that are actually different people in the list.

               

              You need a join table that will hold a key for store and legislator.

               

              Loop in a loop. Outer loop runs through a list of legislators. Inner loop gets the unique stores for that legislator and runs though making the join records with the keys from both.

               

              I would use ExecuteSQL.

              • 4. Re: Import and Relate Fields Through Script
                BillR_1

                Thank you, but I'm still not certain how to link the records. I can set up a table with legislators, and set up a table with stores, and get the unique ID for each legislator, but I'm failing to see the next step as to how the database knows that this store relates to these 2 legislators.

                • 5. Re: Import and Relate Fields Through Script
                  bigtom

                  You need a third (Join) table "store_legislator" with the fields:

                  Store_LegislatorIDpk (auto enter  Get(UUID))

                  StoreIDfk

                  LegislatorIDfk

                   

                  In the graph connect the StoreIDpk with the StoreIDfk. Do the same thing for legislator. This way there is a another table in between. This handles each store having more than one legislator and each legislator having more than one one store.

                   

                  Having the file and excel file will help give you advice on how to best do this.

                   

                  If there is only ever one Sen. and one Assy. for each store you can use a table for Sen. and a table for Assy. that would be a simpler concept, but without seeing the data or knowing how you use it I cannot suggest that is a good idea.