Import from "flat-file" to multiple related tables Advice please
Hi my current database has tables for dogs, owners, classes, and a few more. From my website I get a GoogleDoc spreadsheet filled in by people that want to join. I would like to add that data into several tables in the current database. See picture. I expect people to either fill in a mobile phone number or an email address and assumed that both are unique. This is a recurring event.
I need to create an Owner record, Dog record and JoinDogClass record and fill some of the fields. Given the fact I also need the relationship between the different records I was wondering what the correct format would and what potential pitfalls I need to lookout for.
Also is it adviseable to keep the records in Imported? If so I could setup a portal to it for some info I only need for new dogs, or is it better to copy everything over and scrap the records after import?
My intended script would look something as per below, any improvements are obviously very welcome
What sort of tests should I build in:
I need to "translate" text (YES/NO answer) into boolean numeric, I can do this with an If statement or can I just copy the YES.
Goto 1st record
Goto Owner Layout
create new Owner record
copy fields from Registration to Owner
Goto Dog Layout
create new Dog record
copy fields from Registration to Dog
copy Owner::Id to Dog::OwnerIdFk
Goto JoinDogClass Layout
create new JoinDogClass record
copy Dog::Id to JoinDogClass::DogIdFk
**here I need to find something clever to link the LevelAgility to Class::Level and fiend the ClassId
set JoinDogClass::ClassIdFk to found ClassId
Go back to original Layout(next record,exit after last)