Newbies Dan-te Posted August 10, 2007 Newbies Share Posted August 10, 2007 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] Link to comment Share on other sites More sharing options...
Mandu Posted August 12, 2007 Share Posted August 12, 2007 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 Link to comment Share on other sites More sharing options...
William Mead Posted January 8, 2008 Share Posted January 8, 2008 Wow, this is really helpful. Thanks for posting this. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6088 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now