Comparing Multiple Field Data

[harges]

I need to compare fields across two tables, find matching data, and change the value of a field in one of the tables. What's throwing me in FMP scripting is that I can't compare arrays or iterate through a list. Here's what I need to do. I'd love any wisdom as how to best approach it.

Look at the records below. Each has 9 fields. One key field and one field for each of four appliances, each with a corresponding field used to store whether the appliance is on or not. I want to copy the values ("on" or "off") in the "A" type fields in Table A records into any records with matching key fields in Table B.

I know how to check for the related records between the tables and loop through them. What I can't do is compare the contents of TABLE A F1 to TABLE B F1, F2, F3, and F4 to see if there is a match and then, if there is, to copy the data from the corresponding "A" field of of the matched field to the corresponding field in TABLE B. And then loop through those comparisons for all fields in Table B.

TABLE A RECORD 1

Key: 1234

F1: lamp

F1A: on

F2: radio

F2A: on

F3: television

F3A: off

F4: toaster

F4A: on

TABLE B RECORD 1

Key: 1234

F1: radio

F1A:

F2: television

F2A:

F3: toaster

F3A:

F4:

F4A:

[comment]

This is a good example of bad structure. While what you are asking for can be done, with a lot of if-then manipulations, it would be trivial if you had a table like:

#   KEY   OBJECT         STATUS

1   1234  lamp           on

2   1234  television     off

3   ...

If this is a one-time conversion, you can create such table by performing 4 imports from your Table A.

[harges]

So if I understand you correctly, you're saying:

Make a new table (Table C) with one record for each of the "Objects." So my number of records in Table C will be the number of records in Table A times the number of object fields in each Table A record.

But unless I do the same thing to my Table B records, I'll still have to do lots of if-thens to compare each "Object" field in a given Table B record to each record in Table C. This approach just cuts the number of if-then's in half, right?

[comment]

unless I do the same thing to my Table B records, I'll still have to do lots of if-thens

That's right - you should do the same thing on the other side. Then you can define a relationship matching on Key and Object in order to get the relevant status directly, with no calculations required.

It would help to know what are you actually doing, as it seems the "other side" is redundant (all the information is already in tables A and C).

[harges]

Ok. I think I'm startting to get it know. Here's the actual situation.

I have a single table-DB containing thousands of product records. Each product is identified by a unique key field and has fields for up to 14 colors that the product may comes in. There are also 14 fields used to indicate whether any of the product colors are new for this product for this season.

But the actual "new" or "not-new" information is not in the DB. It is in a separate spreadsheet.

Somehow, I need to take the "new" or "not-new" info from the SS and merge it with the product records in the DB. To make matters worse, the order that the colors are listed in the SS is different from that in the DB so I can't even compare "color 1" to "color 1." I have to look for matching key field + matching color name between the DB and the SS and, when I find a match, import the "new" or "not-new" status for that color from the SS to the DB.

[comment]

1. Is this a one-time thing, or do you need to update the colors "new" status periodically?

2. How is the data organized in the spreadsheet?

[harges]

It's really a one-time thing or a once-a-year thing. The data in the SS is pretty yucky. Right now it's not one record per row. A row will start a record but instead of running the colors across in cells on the same row, they run the color cells down in a column. However, I can script that into good order pretty easily and end up with a three column SS, one record per row, like, this:

StyleNumber | Color Name 1 | New Status 1 | Color Name 2 | New Status 2 | etc.

Edited July 11, 2010 by Guest

[comment]

No, actually the spreadsheet is a good start. If you can get it to a point where it's similar to the example I posted earlier (i.e each row has color, status AND StyleNumber) you can then import the status by updating matching records.

This is, of course, assuming you have split your existing table into two related ones: Products and ProductColors. You should do that regardless of the current issue. It's just basic data normalization, and it will help with almost any task you may need to undertake in the future.