Relate to *other* records with same value?

[ESpringer]

Just discovered the forums. Sorry if this has been covered before...

Suppose I'm troubleshooting duplicate records or consistency across similar records, and an internal relation tracks sameness in one field (say, on an id number or phone number); I would like a layout to show me some fields from the other record(s) with this value. (Or imagine text calculation that would stay blank or report "This record seems to share a phone number with " & samephone::name, used in layout as a button to go to that record.)

As it is, any portal or relational field shows the "first" related record (by entry order or fixed relational sort order), so that half the time I'm looking at exactly the data already visible in the primary record display. So, I can't see the duplicate unless I set up a portal with multiple rows and scan visually for which one is different, and I can't just set up a button to "go to the other one" (or perhaps that's doable with a script, haven't bothered to try yet).

Is there any elegant solution? Thanks...

[Fenton]

If you don't want to see the current record in the portal, you have to remove its ID from the right-hand key. Since you're using a simple self-relationship right now, you can't manipulate it in any way.

So what you have to do is recreate the keys of the portal's relationship in a calculation field.

First create a value list. Use values from a field, related values only, choose the portal's relationship; then choose the right-hand key field.

Then create a calculation field with those values, using the ValueListItems function. Its Storage must be "Do not store."

Now you've got the same key values as the relationship in a field. A lot of work. But now you can manipulate them.

Substitute nothing ("") for the current record's key, adding a carriage return to both so you don't accidentally wipe out a partial match; which a text function can do otherwise.

Here is the URL of an example file (not mine):

<http://www.filemakerpros.com/REMROW.zip>

[Fenton]

Oops. You don't remove it from the right-side key. You can't manipulate the right-side key. But you do something which has the same effect.

You get the values of the right-side key into the left-side key, then remove the current ID, then relate the result back to the right-side key, where it will match its own values (which now doesn't include the current ID).

[ESpringer]

[color:"green"]Thanks! I was able to set up such a portal within a few minutes based on the example file.

(I just checked back for replies today. Very impressed that you had actually answered so promptly.)

-E Springer, Connecticut