Combining records from multiple tables

[Mark Mcdonald]

This is a noob question I imagine, but how does one combine records from multiple tables into a new table?

Specifically, I have three excel reports that report on different attributes of a list of products. Not every product ID is on each report, but there is overlap.

So what I am wanting to create is a table that all the product IDs from each source consolidated and filtered to remove the duplicates. While I could do that manually in excel, these reports are updated multiple times a day and I'd love to be able to have this automated.

Any suggestions?

[T-Square]

Welcome aboard.

Your post is a little confusing: are you going to continue to use Excel? If so, it sounds like you are wondering about how to import the data consistently, on a regular basis, which is a problem I'm not going to answer (mainly because I'm no expert on the ins and outs of FM importing).

If your plan is to use Filemaker to capture and store your data, then that's different. In that case, I'd create a single unified table for products that contained ALL the product attributes needed for the different reports. Then I'd build the reports as separate layouts, each with the view I need in that report.

David

[Fenton]

Short answer: You can put the products (with unique IDs) in a single table. When you Import you use the option for Update Matching records, with the Product ID as the match field (click on its arrow again, and it will change from an arrow to an equals sign). This will update any attributes (we call them fields). Also choose the Add new records option, to bring in any you don't have yet.

You must show all records first, as you can only match against IDs in the found set of records.

This can be automated, with a script using the Import step (whose dialog is the same as a manual import, but which saves the settings). But only if the Excel files have exactly the same columns each time.

Edited February 22, 2009 by Guest

[Mark Mcdonald]

Thanks guys, I think I worked out a solution. To answer your question, T-Square, yes I'm stuck with excel source data as these are reports generated by different companies and systems. I'm using FM to consolidated this info.

Fenton, that's more or less what I've ended up doing to create a "master" list of IDs. But rather than storing data in additional fields in this table, I'm using it as a reference list, and pulling portals from the matching source data.