1 Reply Latest reply on Apr 14, 2011 10:05 AM by philmodjunk

    unique records in a join table



      unique records in a join table


      I'm working on a solution that has 3 tables: Volunteers, Oportunities and a join table. There can be only one Volunteer-Oportunity record in the join table.

      I have a portal on the Volunteer form that allows me to select Opportunities for the Volunteer record I'm currently working on and add a record to the join table. This works fine.

      What I'm trying to do is display a custom dialog box that gives the user a clearer message that a duplicate entry is being made, only gives them the option to cancel the entry and removes the duplicate entry from the join table.

      I've created a calculation field in the join table that has both foriign  keys and checked the Unique box. When I enter a duplicate  Volunteer-Opportunity record I get the standard Filemaker dialog box  that alerts me to the fact that I'm entering a duplicate entry.

      Thanks for any help.

        • 1. Re: unique records in a join table

          You can add your own custom validation error message to use in place of the standard message and you can disallow letting the user override the warning which limits the validation error dialog to returning to the field to correct the error or reverting the record which gets you pretty close.

          If you want total control of this yourself, add a self join to Manage database Relationships that matches on your calculation field:

          JoinTable::CalcField = JoinTable2::CalcField.

          Then you can use the OnRecordCommit script trigger or possibly an OnObjectExit trigger to check JoinTable2 for related records. If there's more than one you have a duplicate.