9 Replies Latest reply on Nov 7, 2010 6:42 PM by MarkMcCoy

    Allowing Unique Values Only

    MarkMcCoy

      Title

      Allowing Unique Values Only

      Post

      I have a contact database that has two separate fields for email addresses.

      • Email Primary
      • Email Secondary

      I have already set the database to only allow unique values in the email primary or in the email secondary fields but have been unable to find a way to link the two together and ONLY allow a unique email address to appear in either one of the fields.

      For Example, it's setup to avoid this situation

      Contact #1

      Primary: mark@test.com

      Contact #2

      Primary: mark@test.com

      What it WILL let me do and what I want to avoid is

      Contact #1

      Primary: mark@test.com

      Contact #2

      Primary: info@test.com

      Secondary: mark@test.com

      Thank you for looking!

        • 1. Re: Allowing Unique Values Only
          philmodjunk

          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

          • 2. Re: Allowing Unique Values Only
            MarkMcCoy

            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.

            Thanks!

            • 3. Re: Allowing Unique Values Only
              philmodjunk

              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.

              • 4. Re: Allowing Unique Values Only
                MarkMcCoy

                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? 

                • 5. Re: Allowing Unique Values Only
                  philmodjunk

                  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:

                  PersonID (number)
                  EmailAddress

                  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.

                  • 6. Re: Allowing Unique Values Only
                    MarkMcCoy

                    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!

                    • 7. Re: Allowing Unique Values Only
                      MarkMcCoy

                      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?

                      • 8. Re: Allowing Unique Values Only
                        philmodjunk

                        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.

                        • 9. Re: Allowing Unique Values Only
                          MarkMcCoy

                          Hey Phil, that worked as well! Perfect and Thank you again! That problem was a big one for me for a while!