4 Replies Latest reply on Dec 20, 2013 12:05 PM by Daniel

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

    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

        • 1. Re: Import from "flat-file" to multiple related tables Advice please
          philmodjunk

          *here I need to find something clever to link the LevelAgility to Class::Level and fiend the ClassId

               Is LevelAgility a field defined in Registration?

               Will the data in this field exactly match to a Value in Class::Level?

               Will this value match to exactly one record in Class or multiple classes?

          • 2. Re: Import from "flat-file" to multiple related tables Advice please
            Daniel
                 

            Is LevelAgility a field defined in Registration?

                 Yes 

                 

            Will the data in this field exactly match to a Value in Class::Level?

                 No there is not an exact match one is a Level numbered1-5 the other is: Beginner, Beginner+, Novice, Competition G1-3, Competition G4-7. SO in fact I could match

                 Level1 = Beginner

                 Level 2 is Beginner+

                 Level 3 Beginner+ to Novice

                 Level 4 Novice to Competition,

                 Level5 Both Competition Classes.

                  

                 

            Will this value match to exactly one record in Class or multiple classes?

                 No it will match multiple values in classes as I have for instance 3 beginner classes; thinking about it I could "park" them in a special class and then sort them by hand, there's only about a dozen or two per Month.

                  

            • 3. Re: Import from "flat-file" to multiple related tables Advice please
              philmodjunk

                   You could define a calculation field with a case function that "translates" the LevelAgility value into the matching Class::Level value. That would make possible the option to set up a relationship matching this calculation field to an occurrence of Class to its Level field.

                   But only if that matched to one and only one record in Class. Perhaps there is additional info in the registration record that can be used to narrow this down to a single matching record? After all, if you can do it by hand just by looking at the data, there might be a way to do it via script or relationship.

              • 4. Re: Import from "flat-file" to multiple related tables Advice please
                Daniel

                     I'll have a rethink of that and maybe alter the question on the form to make it a bit more identical to the Class levels, it's not something that you can have a set of rules for unfortunately. Thanks.

                     At the moment I am wondering if my intended script is following the correct order of creating records and am wondering what sort of test (if any ) necessary. I have little control over what is filled in in Google Docs only that there are mandatory fields.

                     Also what to do with the Records in the Registration table, these are a 1:1 copy of the data from GoogleDocs via a CSV file. I'd also rather have this automated and am not tied up to google docs, my Website is made with WordPress.