Hi all,

I have a solution to this and it is working but it is painfully slow.
Was wondering if anyone had any bright ideas to speed it up...

This is for a runtime solution.

The user needs to be able to import from a spreadsheet of their choice.

Each users spreadsheet is going to be entirely different, there is no set
form. The only set rule is the spreadsheet cannot have more than 17 columns
and no header row.
For various reasons I do not want to give them access to the standard
FileMaker Import Records dialog box where they move the fields around to set
the order.

So I have built my own import layout.

Now I could say to them your spreadsheet has to have the columns in this
order... but I don't want the user to have to start moving columns around in
spreadsheets, especially as the majority of them are not going to be Excel
literate enough to understand dragging columns etc without a lot of hand
holding and support.

So this is what happens at the moment.

I have a table temp_import. It has 17 fields on it (just named Field1,
Field2 etc)
They select their spreadsheet through an import script step and the initial
import takes place and imports records into the temp table. Sometimes this
could be up to 100,000 records.
This part is reasonably fast, no issues and I'm happy enough with this.

Next they are taken to a layout which lists the 17 fields, each with a
dropdown next to it.
The dropdown contains a custom value list of the field names of the main
table where the records need to be eventually.

Next to each of their 17 fields they select from the dropdown which 'real'
field that column from the spreadsheet refers to.

I then run a script. If the dropdown is not empty set a variable for the
fieldname and the fieldvalue

e.g.

$fieldname1 = "MyTestField"
$fieldvalue1 = "just testing"

It runs through and sets 34 variables in all (all the way up to
$fieldvalue17)

Then we go to the real table, add a new record and set the fields based on
the variables we're holding. For this we use Set Field By Name
["RealTable::" & $fieldname1; $fieldvalue1]
And so on...

Back to the temp import table and we are in a loop now through all the
records, setting the 34 variables, and creating a new record in the real
table for each.

The above script is where I just sit and wait, and wait, and wait...

I then delete all records from the temp table and we're done.

Like I said, it works but I am sure there must be a better (quicker) way I
haven't thought about.

Any ideas?

Thanks

#############################################################
This message is sent to you because you are subscribed to
the mailing list FMPro
FMPro@lists.lassosoft.com
To unsubscribe, E-mail to: <FMPro-unsubscribe@lists.lassosoft.com>
Send administrative queries to <FMPro-request@lists.lassosoft.com>