One approach might be to replace your two email fields with a related table of email addresses. Then you are enforcing unique values on a single field.
You could set up some calculation fields and a self join relation ship that would match to all records in your table based on values in either field which you could then count in a validation rule to determine uniqueness, but that would appear to be more trouble than the first option
Hey Phil! Thank you for such a quick response.
If I were to create a related table of email addresses, would there be an easy way to re-link all of the email addresses to their respective contacts?
I have about 40,000 records that each have email addresses and I would want to make sure I didn't break the link.
You could write a script that moves the data from your two fields into new related records while looping through all your current records.
Or you could use Import Records twice to import both the primary key and email fields from your current records into matching fields in your new table. You'd import once from email1 and once from email 2. You'd then be able to use a portal to your email addresses so you could see both of them. Coincidentally, you could then support more than two email addresses if you wanted to.
Ok, so then just to confirm I understand what I'm doing correctly.
Create a new table for email addresses with a serial number unique to each email address.
Then, export the Contact ID# (original table) and email address and then re-import that into the newly created email table where I would let the Email ID auto enter as I imported?
Assuming that's correct, how do I then display both email addresses (or more) on the one record?
Not a bad idea to have a serial number for each email record, but it's not strictly needed for what we are discussing.
Define at least two more fields in your email table:
You should have a serial number defined in your original table. If not define one and use Replace field contents to load it with serial number values. Let's call it PersonID also.
Define a relationship in Manange | Database | Relationships between the two tables like this:
OriginalTable::PersonID = EmailTable::PersonID
Go to a layout for the new email table.
Select Import records and map the PersonID fields and one email address to the PersonID and EmailAddress fields in your new table.
Do this again, but map the second email address field instead of the first.
On a layout where you need to see your email addresses, add a portal to your new table. The PersonID value will be used by FileMaker to select the correct Email records to list your email addresses.
Thank you Phil! I'm going to start working on this right away.
You've been a HUGE help, this is something that's been a problem for me for months now. Will report back with an update!
After a couple of days I've got both of the databases merged. Thanks again Phil!
Now my next question is how to delete the portal rows associated with a Contact ID if I delete the Contact ID record?
There's a delete option for that you can set up for the relationship between the two. Open manage | Databases | relationships and double click the line between the two tables. You'll find a check box for this.
Do think this over carefully and do some testing before you try it with real data. Cascading deletes like this are great ways to maintain "referential integrity" in your database, but misused, they can blow away data you needed to keep.
Hey Phil, that worked as well! Perfect and Thank you again! That problem was a big one for me for a while!