Merging two tables with duplicates

[Dan-te]

Hello,

I'm pretty new to Filemaker Pro and could really use some assistance here. I've got two scenarios I can work with, as I'll explain:

(1) I have two tables in a database which contain wine cellar data (they were done originally in excel on two separate computers for speed). The fields under which they are cataloged are identical in the two tables. I now need to take these two tables and merge them. Ideally, I'd like an automated solution that can basically merge the two tables, where duplicate entries (based on the fields "Winery", "Name", "Vintage", and "Size") are combined in such a manner that the "Bottles" field from both are added together and then only ONE entry exists, and then all bottles unique to each list are added as normal with their respective data.

So: if duplicate bottle (based on the criteria of 4 identical fields listed above) create only one instance of that bottle with combined bottle count from both entries in "bottle" field, and if unique bottle (no duplicates), simply add information from fields as normal.

Ok...so...SCENARIO 2 (if the above situation is too complicated)

I managed to create in excel a worksheet with the bottles from both lists, and each bottle has a unique ID (basically I made a new column which contains the contents of the 4 fields all-together). So duplicates have the same "Unique ID" field. Is there a way to use THIS list and similarly have Filemaker Pro filter out or remove the duplicates after it combines the bottle count into one of the entries?

I'm sorry if this sounds convoluted, but I'm extremely lost on how to approach this. There are too many bottles to manually do it in any small amount of time. If anybody can help, I'd be extremely grateful. Thanks for your time.[color:red]

[Mandu]

Is this basically a one-time process, to convert an old database into a new one? Or will this be done periodically? If one-time (or very infrequently) here's a rather complicated and messy way ;-)

First, you combine the two tables into one table.

Then, you define a key using an unstored calculation field that concatenates the multiple fields comprising the key.

You define a summary field that automatically totals the qty-on-hand fields.

You sort your table by the summary key.

You Export using the Group By option.

The exported file will contain one record per unique summary key, with totals.

At this point you've got the data you want, but not necessarily in the form you wanted, depending on how you exported it. You'll probably need to then import.

That's what's messy. If it's a one-shot deal, though, no sweat.

See attachment for table defns and scripts.

foobar.fp7.zip

[William Mead]

Wow, this is really helpful. Thanks for posting this.