How to keep 4 tables consistent

[jwnacnud]

I have three different websites that I feed from my Filemaker Database. The website feeds require specific column names in order to import correctly. Thus I need to have 4 tables because some of the column names (field names) have the same name across different websites but different data.

I have one main table that has all the pertinent data and a Unique Identifier called "Model Number"

I have an ebay export table

I have an Amazon export table

I have a Website export table

Each table has a relationship back to the main table based on Model Number. Different fields will pull in the data they need when Model Number matches.

My problem is how do I keep the three other tables up to date with the model number field in the main table. For example, I add a new product to the main table, how do I make sure that model number gets added to the other 3 automatically?

Can I run a script that works in the background to do this on the server? OR, is there a much better way? Is there some sort of compare script step or function that will allow me to do this?

[comment]

What is the export format?

[jwnacnud]

Because I need the export format to retain the field names as "column headers" I am exporting as a .mer (merge) file. I then rename this .csv and upload the feed.

i haven't found anything else that retains the field names

[comment]

Keeping 4 tables synced just for the export does not seem like a good idea. Fortunately, there are other options:

1. Export as XML, using 3 different style-sheets.

2. Export the file as .csv and use Applescript to add the field names.

3. Keep the 4 tables, but sync them only when exporting, as part of the exporting script.

[jwnacnud]

Option 3 (though 1 and 2 sound better) - How would I sync them? Thank you in advance.

Jeffrey

[comment]

The primitive method: in the source table, show all records (or find the records you want to export). In the target table, show all records, delete all records, import from source table.

This can be smartened up by importing only new records (import with matching on ID). But then you need to make sure that existing records are deleted when the original is deleted in the source table (this can be done in the relationship's definition).

Note that with the primitive method, you don't need any relationships.

[jwnacnud]

Can I import from a table within the main file. I only have one FM file with multiple tables within.

[comment]

Sure, you can import from another table in the same file.

[jwnacnud]

how do I import from one active file back into the same active file

[comment]

Sorry, I do not understand the question.

[LaRetta]

Can I run a script that works in the background to do this on the server?

Note that, as far as I can tell, you can't (*yet) use server-side script if import is involved. This does not mean you should dump the idea of using imports; only that you can't fire it with hands-off capability using FMS.

*simple guess that FM will remove this limitation in the future.

[comment]

Missed the point about server - thanks for catching that. But perhaps that was related to the question about synchronizing the 4 tables, while the actual exports are user-initiated?