Repeating field as matching field

[longjump]

I have a repeating field in Table A, which is also the matching field for a related field in Table B. Both fields need to be displayed in

one Table A layout in columns next to each other, so that any matches show up next to the appropriate repetition.

In many cases, not all of the repetitions for a given record have a match. It is vital that when there is no match for a given repetition, the related field must show either a blank or "no match" in the same row. Also the matches must show up in the correct order in the column.

The only way I've been able to pull this off so far is to use a lookup in a repeating field, with a "if no exact match" value specified in the lookup definition. If I try to use a multi-row portal with a regular related field, non-matches are ignored so that if, say, only repetitions #1 and #3 have a match, the portal puts the data in rows #1 and #2, resulting in columns that do not line up with each other.

Using a lookup is clunky, even for me. Is there a another way?

If your 1st thought is to tell me not to use a repeating field in the first place, please try to resist. My boss wants to keep the repeating field.

I learned FM in the pre-relational days, so this stuff is not intuitive for me, esp. portals; please be gentle.

Cheers!

FM 11 (the database file has .fp7 suffix).

Mac OS X 10.6.8

[comment]

If your 1st thought is to tell me not to use a repeating field in the first place, please try to resist.

Actually, my first thought was use a lookup. But you want a less clunky solution and still use the repeating field. Well, guess what is the real cause of the clunkiness here.

Still, I guess you could use a repeating calculation field, testing for something like =

IsEmpty ( FilterValues ( RepeatingMatchField ; List ( Related::MatchField ) ) )

(untested)

Note also that multiple matches cannot be distinguished from a single one.

[longjump]

Thanks, and a well nuanced answer (you got a zinger in w/o being a jerk about it, plus offering a solution based on my limitations). Kudos.

Unfortunately, either the solution doesn't work or, more likely, making it work is tantalizingly juuuust barely beyond my skills.

[longjump]

OK, alternate scenario - does this help? If not we can stay w/ the orig scenario:

I've converted the repeating field in Table A to a new portal, where the repeating values in the repeating field are now separate records in a new Table C. The key field that ties groups of records in Table C to individual records in Table A is a serial number from Table A.

But this puts me back to square one, since now I need matching columns from Table C and Table B to show up in the Table A layout [i guess? ].

I assume whatever this brings up has been posted about before, so pls feel free to redirect me if necessary.

[longjump]

In case it helps, here's a further description of what I'm trying to do:

Each record in Table A is a photograph, each with a unique serial number.

The repeating field in Table A is the names of the people in the photo. Each name must be in an individual field or repetition so that the name can be entered from a value list.

As I mentioned in the previous post, this data is now duplicated in a new Table C, as individual records related to Table A by the serial number. I don't know if this is useful, I just tried it to see what would happen.

Table B contains a record for each person who appears in the photo collection, with a text field containing a list of all possible names (A.K.A.'s), for that person. If a person has no A.K.A's, there is no record for him in Table B. Table B is related to Table A by the person's name. You never know which name or A.K.A. the operator will use during data entry, so the matching fields are: name field <--> field containing AKA's.

I need a layout showing a bunch of data about the photo (all of which is in Table A), plus a column of the names of the people in the photo. And I need a parallel column showing e/ person's A.K.A.'s next to his name. For people who have no A.K.A.'S there must be a blank or "no alternate names" next to their name.

I can get any two related things to show up together, but I don't know how to get all three together in one layout, unless I use a repeating field and a lookup.

The reason a lookup is problematic is that sometimes we might update the list of alternate names for a person, and then we laboriously have to edit every record with that person in it. NG!

[longjump]

Solved??? [assuming I can get boss to abandon his beloved repeating field, that is...]

I think I lucked onto it. In the Table A layout, just have a portal based on Table B, containing fields from both Table B and Table C.

Right?

[comment]

In the Table A Photos layout, just have a portal based on Table B Subjects, containing fields from both Table B Subjects and Table C People.

Right?

That certainly seems like the correct solution. I thought the problem was merely to indicate which subjects had a match in People and which didn't. Lining up each matching person next to the corresponding subject would be much more difficult using repeating fields - that is, other than by a lookup.

The reason a lookup is problematic is that sometimes we might update the list of alternate names for a person, and then we laboriously have to edit every record with that person in it. NG!

Hopefully this is theoretical by now, but why would that be necessary (assuming you were looking up the PersonID)?