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.

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.

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