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.
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.
-Jane in LR
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.
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.
Go To Record [ First ]
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 ) ]
Set Variable [$I ; Value $I + 1 ]
Exit Loop if [ $I > 5 ]
IF [ Not Isempty ( GetValue ( $FacList ; $I ) ) ]
Set Field [ Facilities::License ; $Lic ]
Set Field [ Facilities::Facility ; GetValue ( $FacList ; $I ) ]
Go To Layout [original layout]
Go To Record [ Next ; Exit after last ]
I must be tired today or something I seem to only be thinking of the hard way to do things.
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.
Thank you both. I will give each a try. It's all about learning, isn't it?!
-Jane in LR