7 Replies Latest reply on May 12, 2010 12:04 PM by user447

    Importing multiple excel columns into a portal

    user447

      Title

      Importing multiple excel columns into a portal

      Post

      I have an excel spreadsheet with one person working at five facilities.  Columns are FirstName, LastName, License #, then Facility 1, Facility 2, Facility 3, Facility 4, and Facility 5.

      I want to import their data into a Filemaker record for the name and license # with a "facility" portal and have those entries as the first five portal rows.  How do I set up the import?

      Can't seem to think straight!

      Thanks for any guidance.

      Jane in LR

        • 1. Re: Importing multiple excel columns into a portal
          philmodjunk

          You'll need to import the data into two tables and they need to be linked by a key that uniquely identifies each group of facility records.

           

          Does License # unqiuely identify each row of your spreadsheet or does it appear more than once?

           

          You can import the spreadsheet into a table with one field for each facility and then run a script that loops through the newly imported records to create new records in a related facilities table.

          • 2. Re: Importing multiple excel columns into a portal
            user447

            Thanks Phil!

            Yes, the License # is unique to each row of the spreadsheet.  Can you helpo me on the script steps for looping through 5 facility records and putting them into the portal rows? Or are you saying I import name and license# into the first table, then import the facilities into the related table?

            I just don't know how to get the excel columns now all on one row to import into 5 seperate records for the portal.

            Thanks again!

             

            -Jane in LR

             

            • 3. Re: Importing multiple excel columns into a portal
              aammondd

              you will probably have to import the related table 5 times changing the import order from 1 column to the next but mapping it to the same field in your related table you dont want to match but just add new.

               

              You can create  the import script step specifying the Facility 1 column in your import order then duplicate it 4 times and change the import order in the others to each import a different facility record.

               

              If any of the facility fields are blank then you can delete any rows in the related table where there is not data in the facility field.

              • 4. Re: Importing multiple excel columns into a portal
                philmodjunk

                You can use two imports but one is all you really need here. You import the facility data in to 5 different fields and then copy the data from the 5 separate fields into 5 related records in a different table.

                 

                Once you've imported your data, your found set will consist of the records just created by the import.

                 

                Your script:

                Freeze Window

                Go To Record [ First ]

                Loop

                   Set Variable [$Lic ; Value: YourTable::License]

                   Set Variable [$FacList ; List ( Yourtable::Facility1 ; YourTableFacility 2; YourTable::Facility 3; YourTable::Facility4; YourTable::Facility5 ) ]

                   Go To Layout ["Facilities" ( Facilities ) ]

                   Loop

                        Set Variable [$I ; Value $I + 1 ]

                        Exit Loop if [ $I > 5 ]

                        IF [ Not Isempty ( GetValue ( $FacList ; $I ) ) ]

                            New Record/Request

                            Set Field [ Facilities::License ; $Lic ]

                            Set Field [ Facilities::Facility ; GetValue ( $FacList ; $I ) ]

                        End IF

                     End Loop

                     Go To Layout [original layout]

                     Go To Record [ Next ; Exit after last ]

                End Loop

                • 5. Re: Importing multiple excel columns into a portal
                  aammondd

                  I must be tired today or something I seem to only be thinking of the hard way to do things.

                  • 6. Re: Importing multiple excel columns into a portal
                    philmodjunk

                    I think your post gives a reasonable alternative. Your approach avoids the need for scripting and may be the easier way to go for a new user who is only importing a relatively small amount of data.

                    • 7. Re: Importing multiple excel columns into a portal
                      user447

                      Thank you both.  I will give each a try.  It's all about learning, isn't it?!

                       

                      -Jane in LR