Avoid duplicates...

[jd9027]

This question is based on a topic explaining Many2Many relationships in FM7 described in the book "Special edition using FMPro 7". Unfortunately the book does not explain a problem that I am having. Could someone help?

As told in the book,

I created a layout as shown in the picture based on the table occurence (TO) "Shift" and then inserted a portal based on the fields in "ShiftAssignment". Then Formatted the first field of the portal to display a value list generated from theTO "PatrolOfficer".

Everything works fine except that I am able to enter duplicate values into the portal. How can I avoid this?

Is it possible to populate the value list dynamically based on the selection the user has already made that the user cannot make 2 duplicate records?

Is there a function in filemaker that can return the total number of records from a table. I was wondering if I can then somehow make FM to iterate through each record of for eg. "ShiftAssignment TO" to see if the concatenated value of "ShiftID & PatrolOfficerID" is a unique value for the particular table.

[IdealData]

You need to set up the VALIDATION on the fields in the related table.

This will also ensure that your concatenated entries are also unique.

To count the records in a table see the Get (TotalRecordCount) function. See the help system for more details.

[Søren Dyhr]

Everything works fine except that I am able to enter duplicate values into the portal. How can I avoid this?

I would probaly not validate, but instead use a principle like the attached, and if I could get rid of the join table as well would do it ...but if you need to build reports on the join records should they stay as in the template.

--sd

pling.zip