AnsweredAssumed Answered

Import from "flat-file" to multiple related tables Advice please

Question asked by Daniel on Dec 20, 2013
Latest reply on Dec 20, 2013 by Daniel

Title

Import from "flat-file" to multiple related tables Advice please

Post

     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.

      

     Script:

Goto 1st record

Loop

Goto Owner Layout

create new Owner record

copy fields from Registration to Owner

Commit Record

Goto Dog Layout

create new Dog record

copy fields from Registration to Dog

copy Owner::Id to Dog::OwnerIdFk

Commit Record

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

Commit Record

Go back to original Layout(next record,exit after last)

End Loop

      

      

Screen_Shot_2013-12-20_at_13.14.53.png

Outcomes