Compare and combine price and description records

[captainboom]

I have two Excel files from a supplier that contain related data.  I can import the files and use the Product ID / Item Number as the key to relate the records.  The two files may have a different number of rows of items, so I can't just sort them and compare in Excel or TextWrangler.  I have over 400 records, but less than 1000 records to analyze.

One of the files has data in columns like this:

ITEM #, Item Name, Description, Other columns of data...

The second file has data in columns like this:

Product ID, Status, Description, Category, Notes, Item price, Case price, Other columns of data...

I would like to:

a.) Match the records using ITEM # and Product ID (they are the same thing, no idea why the supplier used two names for the same data).

b.) Display all of the records that match, with all of the fields from each record.

c.) Export the matching records and all the fields to Excel.

d.) Do the same thing for records that *do not* match.

My ultimate goal is to be able to combine information such that I can update inventory status, price, and other data on a web site using a bulk upload, as well as to be able to generate a PDF catalog from FM.  I'm open to using a different tool to accomplish this, if that's a better solution.  Please let me know if that's the case.

I think I need to use a join table somehow, and perhaps a portal to show the records, but I can't seem to make the connection after hours of reading documentation and trying things.  My skillz just aren't there yet.

Thanks in advance.

[comment]

1 hour ago, captainboom said:

Match the records using ITEM # and Product ID (they are the same thing,

Is either ITEM # or Product ID unique in one of these tables?

[captainboom]

ITEM # and Product ID are both unique to their respective tables, though many will match between the two tables.  I think there will be about 400 of them that match.

I want to match the tables to each other, based on that ITEM/Product ID, and combine the records from both tables such that they are combined under one of the ID's for each record.  One of the tables has more data than the other, so there will be some Product ID's that don't match those that exist in the table using ITEM # at this time.

[comment]

18 minutes ago, captainboom said:

ITEM # and Product ID are both unique to their respective tables,

If you are sure of that, then the problem is much simpler. However, you need to specify what to export in each one of the three possible cases:

1. Records from the first file that do not have a matching record in the second file;
2. Records from the second file that do not have a matching record in the first file;
3. Matched pairs.

And, of course, you'll have to perform at least two exports, to separate files (unless one of the first two cases cannot occur).

 

Edited May 2, 2016 by comment

[captainboom]

I hadn't thought about doing separate exports, so that helps.  I'm confused about how to iterate through the second table, comparing each record to the corresponding field in the first table.  I assume do a join table, but there is where I fall down.  I don't know how to display variations 1, 2, and 3 that you delineated above.

[comment]

I suspect you are making this more complicated than it really is. But I am still not sure what exactly you want to export.

The way I see it, you will have two tables in your FM file - let's call them Items and Products. You will define a relationship between these two tables as:

Items::ITEM # = Products::Product ID

Now, if you export from the Products table, and include fields from the Items table in the export field order, you will get two kind of rows in your exported file: rows that have data in all columns (these come from Products that have a matching record in the Items table), and rows that have data in only some columns (these come from "orphaned" Products). With this done, you need to switch to the Items table, find those Items that do not have a matching Product, and export them. Thus two exports cover all three cases I described above. (If you really wanted, you could use a third table to pre-combine the two exports, so that eventually you would end up with a single exported file.)

The part that I am missing is what do you want to do when you have a matching Item/Product pair with conflicting data. In your example, both tables have a Description field, and you may have to choose. Or perhaps you want to export both?

The other question here is whether you do this once, or is this a recurring thing. If the latter, will you be adding new records to each table, then export everything (old and new) - or something else?

 

Edited May 2, 2016 by comment

[captainboom]

Ok, I didn't realize I could just make the equality relationship, and then export the fields of interest.  That does indeed make this much more simple, so I'll try that.  I thought I would have to somehow display it in FM first, then export the displayed result.  I think that would be the third table option you mentioned, to give me a single export, and I don't know how to make that work.

To answer your questions, yes, I will export most of the fields from both tables.  Anything that doesn't come out cleanly should allow me to isolate it and manipulate further in Excel or TextWrangler.  I am not concerned about conflicting data in the description fields.  The Description fields actually contain slightly different data (I know, bad idea, but that's the way I got it from the vendor...), so I'll export them both for now, give them different field names, and eliminate as necessary from Excel.

I will likely get new versions of both of these files on a periodic basis, so I'll do it more than once.  Each table will get new records, supplied by my vendor.  I think it will be fast enough for now to use this method to get what I need and keep going.

I'm working with the supplier to get their database to spit out the data in a format that is more useful for me, and then I won't have to do this at all.  Thanks much for the help.  I'll try this after dinner and report back.

[captainboom]

Ok, the direct relationship and export is exactly what I wanted.  As you said, much easier than I was making it.  Thank you very much.

[comment]

Another option you might want to consider is to use a single table having fields for both types of files - except ITEM # and Product ID would be unified into a single field, let's call it CombinedID.

Here you would always import using the "update matching records" method: when importing from an ITEMS file, you would match ITEM # to CombinedID;  when importing from the other type, you would match Product ID to CombinedID. This way, any matching pairs would be automatically combined into a single record.

Note that this assumes any subsequent imports are supposed to overwrite existing records with the same ID.