2 Replies Latest reply on Jun 13, 2017 5:48 AM by fmpdude

    End user data import to FM DB - which way is best?


      Hi there


      I'm currently at a crossroads on a solution I'm working on which requires the end user to import data from Excel.  There's a couple of clear ways this can be done, both of which are via the use of friendly green buttons for the end user and scripting for me.


      1)  Import the data initially to a temp table where the user can examine and confirm they're happy with it.  Pause the import script and use another friendly green button to resume the script, whereupon the data is transferred to its final table within the DB.


      2) Import the data directly to the main data table without the checking step as a part of the import routine, but flag the data instead as 'locked' (or something similar) so it's not used until reviewed by the user.


      I'm really looking to see what other people may have done in similar scenarios.


      The benefits of 1) above are the ability to cross check data, but the cost is the time taking to move the (sometimes large volume of) data from the temp table to the data table. (I'm just setting variables in the script, flicking to the data table, updating the fields and then returning to the temp table in a loop, exiting after last).


      The benefits of 2) above are obviously speed, but if there's anything wrong with the data or if the import routine borks, then the main data table is at risk.


      Any thoughts on this discussion gratefully received



        • 1. Re: End user data import to FM DB - which way is best?

          A lot depends on the data. I have one client that does a variety of imports from their clients. Some scripts they can run themselves. These are imported into a temp table and extensive error checking is done on each record. If an error is found the record is flagged and ignored. An additional variable ($Errors) is set and checked at the end of the script. If there are errors I send them to the temp table and show the flagged errors.

          • 2. Re: End user data import to FM DB - which way is best?

            I would recommend (not listed) option 3. Do not let users freely import spreadsheets.


            Keep data imports totally scripted and behind the scenes. If a user wants to do an spreadsheet import, that spreadsheet should be programmatically generated for consistency and the only way to import it is via a script tightly controlled by the program.


            If you're sure about the format, you could use either of your two methods depending on project factors.