Importing into multiple tables

[djhopalong]

I'm a noob - so, if there is an easy idoitic solution, forgive me.

I was hired to do some web work and along the way I became the filemaker setup guy. I created a form online that outputs data into a text file. My client just got filemaker and asked me to set it up for them, which I've done. The problem is I designed the data base with different tables to hold similar info. The tables are: contact info, career goals, education, and references. I can output the data any which way. Originally, it was a list of fields and their values ($field = value). I was having trouble getting that in. Eventually I just made it tab delinated but the info spans all the tables I've created. I can't figure out a way to import external file data without having to open the text file a separate time for each table. It's driving me nuts. Any help is greatly appreciated.

[Fenton]

If you write the file, on your web server, as an xml file it will have all the data in one file, and it will have the structure to import into the separate FileMaker tables. It is not really much different from writing name:value pairs, but a heck of a lot more flexible and powerful.

It will still require separate imports. But this should not be a problem. You will need to provide the xsl stylesheets to select the data for each table's import. They can reside on the web server. The import should be scripted, so that it does what it should and appears to be a single action.

XML imports from a web-served xml file do not have to "open" the file via the operating system. It can import directly into FileMaker fields, using the xsl to select the data. The elements, attributes, and whatever fixed text you add to the xsl will appear in the FileMaker Import dialog just as if they were field names (or column names), with the usual options.

[djhopalong]

Thanks for the quick reply. I'll give it a try and let you know my progress.

[Fenton]

Just a quick note. XSL can reach "up" its hierarchy of "nodes", so you really only need to include the ID for things once, in the logical place for it. But you must include either IDs or unique fields, so the separate tables can tie the data relationally.

[There is actually a "generate-id()" function in xsl, which can do something like this. Though I wouldn't necessarily recommend it for beginners; I find it a bit tricky -]

[djhopalong]

I understand that for my office and really for longevity I should export as an xml doc with a xsl sheet. However, my real hang-up is the scripting part. If I have to have the script open multiple times anyway the quick fix solution would be to just have it open my current tab delinated file multiple times and import the specifed fields. I'll have to do the same thing for the xml docs anyway, right? It sounds like that script should be simple but I can't work it out. I've read through the script steps reference on the filemaker website to no avail. I can get it to create a new record and fill in the first table but on subsequent openings it creates brand new records for the other tables instead of filling in the rest of the fields in the same record. If you could even point me in the right direction for a comprehensive online resource for filemaker scripting, that would be a big help.

[Fenton]

You should read the FileMaker Help about importing. Perhaps you have. Look carefully at the Import dialog. On the left side is your source document (which is already choosen by the time you get here). On the right side is the target table, to receive the data, and its fields. That is where you tell FileMaker which table the data goes in.

This statement, "I can get it to create a new record and fill in the first table but on subsequent openings it creates brand new records for the other tables instead of filling in the rest of the fields in the same record." shows poor relational design, either in the source file or in the FileMaker file.

I can't see why you couldn't get all the data for 1 table in one import step.

But I will let that pass. As I've said, I'd do it with 1 XML file and multiple xsl stylesheets. To do what you want, you need to check one of the options:

1. Update existing records in found set

If you just imported the lines from a file. This will bring in data into the same lines in FileMaker.

or

2. Update matching records in found set

A bit more flexible and reliable. Choose a field (or fields) for the matching key(s). Click them again and the arrow will change to a double-sided arrow <=>.

In both cases, be sure to unselect the ( ) Add remaining data as new records. That's why it's creating new records.

[djhopalong]

Thanks again for your prompt reply.

I was brought on to do a straight html form and have since been dropped into working out the PHP and the filemaker parts of the process. I do understand how the file import window works. Undoubtedly, I have not designed my filemaker database in the best manner. But I also think I've been unclear. I designed the database (on filemakers suggestion) with similar data in different tables. For instance I have one table with "contact info" and another with "academic background", etc. I probably should have just done one long table. But I have since designed layouts that took me forever. I mistakenly assumed importing would be an easy process, since I can output the data as CSV, tab delineated, txt, or excel file all of which filemaker excepts.

I can't see why you couldn't get all the data for 1 table in one import step

I can get my file to import and populate all fields in ONE table. My issue is getting it to populate all the fields in ALL the tables (there are five of them). My form exports around 60 fields and their values. All the values in the "contact" table are filled out (no problem for that table). The values in the next table "academic background" are filled out in a new record, and so forth for the rest of the tables. That has to do with how the data is saved. But even if I change it to be one line for fields and another for values it still won't import for all the tables in one step.

I guess my options are to bite the bullet and just redesign the database as one long table or bone up on my xml and follow your original suggestion. I was hoping that I missed some process that allowed for you to specify different fields to different tables. Making separate XSL files and automating multiple imports for 1 record seems a convoluted process.

[Fenton]

You say "import all the tables in one step." Each table gets its own import "step". But it could still be one script.