Matching between Portals on the same layout

[Gene L]

Gurus at Large,

I am working with invoices containing serialized products. I have two tables of data that should be identical, but unfortunately they're not. So I need to compare the two tables and and match up the records.

Both tables have several levels of granularity; ex. invoice year, invoice month, invoice day, invoice number, part number, serial number. The problem is that in one table, a serial number for a part might have a leading "S", while in the other table the "S" has been lopped off. This is just one example of multiple permutations that defeat using serial number as a match field.

I want to build a user interface that will allow a user to see both tables in side by side portals on the same layout, and to drill down as granular as they can go, to easily see where the broken matches are and, for the example above, add an "S" where it was lopped off to repair the matching serial number.

To start with, I've built a layout with two portals, which are both driven by relationships from global drill-down variables; gYr, gMo, gInv, gPN. It works beautifully for the portal on the left, but I can't figure out how to activate and populate the portal on the right. I think I'm supposed to use the Go to Object or Go to Field command to get to the second portal, but I can't get it to work.

Assuming someone can advise me on how to access multiple portals on the same layout, how then would I enable a user to pick a portal row from the table on the left and match it to a portal row from the table on the right? One possible solution would be to have a button that copies the serial number from the table on the right and overwrites the serial number in the table on the left. But I still have the problem of identifying which portal row to pull the SN from and which portal row (in the other portal) to place it in.

Thanks in advance!

Gene

[dazlunn]

Hi Gene,

I don't have a direct answer, but why are you using 2 sets of 'almost' identical data?? Surely that is defeating the objective of having centralised data?

Ok there could be times when this is necessary, I suppose (although non spring to mind)... sounds like you are using one table as a master and the other as permutations of the master data, but why can't you build this into the same table?

You are going to run into some serious issues by trying to use custom serialisation, it's not a sound way to build a solution, you should try to consolidate the table into a single table and then use table occurrences which will allow you to use proper serial id's..[in the relational graph]

I may be off the mark with my understanding of what you are trying to achieve, but it appears that you could be making a stick to beat yourself with here..

Edited March 4, 2007 by Guest

[Fenton]

I've had to do something like this. In my case it was because 2 departments of a company collected people's information, and only one of them used FileMaker. But it was very much a "fix and centralize the data," not a normal part of operation.

I just put a portal of the other file on the layout of the first, sort of a list/form layout. Then I turned on drag-and-drop, and "select entire contents" in the Field Behavior. So I could grab the real ID and drag it to the portal record, tying it to the other. I would later automate processing the records, getting data from the one, putting it in the other (if it was more recent).

The portal was filtered so that it showed only "similar" records, based on something like the first part of the address; not showing records which had already been matched (most automatically).

It is hard to imagine how invoices would get themselves in such a mess however; two different departments creating invoices for the same things? Doesn't make much sense.

[Gene L]

Hi Dazlunn,

Thanks for your response. I tried to keep my post as brief and to the point as possible, but I guess I neglected to mention this important point... I am submitting data to another company, they are then processing the data and returning it to me. When it comes back, I need to match it up with the original data that I submitted, to determine how accurately the data was processed. Unfortunately, I am locked into the other company's required submission format, which, believe it or not, lacks a key field for later matching. Also, the serial numbers are not mine, but provided by the other company. So there you see my predicament! And onward must I go with this silly matching project..

Regards,

Gene

[Gene L]

Hi Fenton,

Thanks for your response and suggestions... I like the drag and drop idea and will try it...

Regards,

Gene