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.
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.
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.
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.
You need a third (Join) table "store_legislator" with the fields:
Store_LegislatorIDpk (auto enter Get(UUID))
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.