You could import the data into different fields of the same table set up to hold imported names and then set up relationships that match by the name values solely to use for a replace or data lookup on this data within FileMaker to get the correct ID's into the proper fields.
I think I got this working OK and have moved on to setting up the import script step.
1. I am using a 3rd party hosting service for the server. Will I still be able to set up a import script?
2. Can I have the script to prompt the user for the file name but use the mapped fields of the script, when I do not specify a file it seems it has noting to map to. This is to accommodate changing file names with new data like orders051515 and orders052015. Same data field maps but different data from different weeks.
1. yes and there is more than one way to do so, including downloading the file to your computer, doing the import and then replacing the hosted copy with the new. But you can also set up the script and run it right in the hosted file also.
2. This is possible, but not as simple as you might like as it requires using a container field and a $path variable. Here's a rough outline:
Put a container field on a layout. This can be a global field. Your script goes to this layout and then uses Insert File with the "store a reference" option enabled to insert a file into the container field. The user sees a dialog where they can find and "open" the file from which they want to import the data. The script then extracts the file path to the file from this container field and uses it with Import Records to import the data.
For more on $Path variables, Import Records and Container fields, see: Exploring the use of a $Path Variable in Scripts
So this will be different when hosted then when running the local copy. The reason I ask is when I omitted the file check box in the script step and then still mapped the fields in the order to blank import file fields, then when I ran the script from a button on my form it did prompt me for a file name and then I selected that and it then did map as I had set the order in the script and all came in OK. I am sure its not that easy is it?
No it will not be different. But you may want to take the file offline to do this or do it during times when DB usage is low.
The scripted method I described was so that the user only selects the file and is not presented with the field mapping dialog where they might make a change or select an option that they should not select.
What I cant get done is anyone to replace the toilet paper when its out. Maybe I should work on an import script for that.
Maybe if I put it in bold letters red and large font they would do it but I don't think I will count on it.
Now being serious that makes sense thanks I will give it a try.
The imported text values of my cites are all caps my value table is only 1st letter cap. When I do the match and lookup it wont match is there a workaround for this?
Had nothing to do with caps, I had a trailing blank space after every city name in the my Value List table.
I know that you are joking, but:
Users will do the darndest things with your DB solution and getting a series of dialogs where they are supposed to only click OK makes even the careful ones nervous.