3 Replies Latest reply on Apr 29, 2013 8:50 AM by philmodjunk

    Making import easier for users via a script/extra table?



      Making import easier for users via a script/extra table?


           Hi there,

           I've got a short import script set up because the data needs to be sent to 2 different tables (members and attendance). However, my tables have many more fields than the spreadsheet we're importing, and my users are finding it difficult to match them up and remember which are match fields and which aren't, etc.

           It'd be easier for them if I made a table just for the imports, with just the fields they're familiar with from the spreadsheets - no dragging field names around or remembering symbols, settings, etc. Then I could take the data from that table and put it in the 'real' tables via a script.

           My problem is that I rely on the import process to check if the record is new or an update to an existing one. If I keep adding to the new import-only table, I can do that, but I don't want to have to work through the whole table (growing with every import) to send the data to the real tables.

           Is there a way to constrain my set variable/set field section of the script to just the records from the latest import? Or some other way to handle this more simply?

        • 1. Re: Making import easier for users via a script/extra table?

               Does importing with the "update matching" option work for you? A single import can update matching records and create new ones. If there's a creation date field that auto-enters the creation date and you enable auto enter options during the import, newly added records will show today's date in this field, updated records will not.

               If you do find that need to go with an intermediary table--this can be needed to process some imports to get issues "cleaned" before sending data to final table, keep in mind that the data in this table should be of temporary nature. once you have processed the data from the intermediary table into the final tables, you should be able to delete all records from it, starting over with an empty table for the next import.

          • 2. Re: Making import easier for users via a script/extra table?

                 I'm currently using that "update matching" option, and it works perfectly for me and the data. But my users can't seem to get their heads around which fields need to match using the equals sign icon, which can be left with the arrow icon, which field names belong to which spreadsheet column names, and why there are extra fields in the database they should be ignoring. I figured it'd be simpler to just give them an intermediary table with field names that exactly match the spreadsheet column names, and then the script can shuffle the data around to the right tables from there.

                 The only problem then is that as you recommend, the intermediary table should be cleaned out at the end of each import. Then how will I be able to use the update-matching option, since the intermediary table will be empty? Or is that what the creation date field is for? I'm afraid I'm not quite getting it yet!

            • 3. Re: Making import easier for users via a script/extra table?

                   You should use a script for importing the data. Then the users do not need to make any decisions about how to match fields for import. At most, they would need to select the script for import when a dialog appears asking them to select a file. The rest of the process should be automatic.

                   The Date modified field would not be part of the temporary table, it would be a field in the final destination table(s). Thus, this information would be retained when you clear the temp table after processing the imported data into the needed destination table(s).