Duplicate ID number

[Robbydobbs]

If I go to add a record with a unique ID number and there is already a record in the database with this ID number, is there a way to have the existing record popup on the screen with all its information?

[Chuck]

How many duplicate ID records could there be? I would attack this in one of two ways depending on whether there can be only two or if there could be more.

Chuck

[Robbydobbs]

There would only be 1 duplicate record.

[Chuck]

Create a self-join relationship from your file to itself with the ID field as the match field on both sides of the relationship. Place fields through that relationship on the layout. After the ID field is filled in, if there is another record with the same ID, the information from that other record will appear in the related fields.

Since when there are two records with the same ID the record will be related to two records (itself and the other record), you want the first record to show it's information. Assuming you have a creation date field for each record, set the relationship to sort by that field in descending order. When related fields are not in a portal, the first related record that would appear in the portal is the one that is shown.

Once a record has been filled in, if it is the only record with that ID, the related fields will show information from the same record. You can get around this by showing calculations instead of related information. For instance, say that you want to show the first name field, but only have it display information if there is a different record with the same ID. Rather than showing the related name field, create a calculation that brings in the information if there are two related records but is blank if there is only one.

Case(