Related Record Validation

[Scott Hall]

OK, I have another question. Is there a way to validate the data in a field as unique, but based on a set of related records in a table?

For example, in my Contacts database, I have a separate related table for "Addresses", so that I can store multiple addresses for each contact. The records in the Addresses table are related to the Contact record by a "Client ID" number. Now, for each address record in the Addresses table, I also have a "Type" field to label each address (e.g. Home, Business, etc.). What I am trying to do is make sure that the values in that "Type" field are unique to that particular Client ID. So, Client "A" might have a "Home" and a "Business" address and Client "B" could also have a "Home" address. I would like the behavior to be such that if you tried to enter a new address for Client "A", it would not allow you to use a value that you have already used for that client (in this case, Home or Business). If I turn on unique validation for the field, it applies to all records in the table. So, in this example, only one client could have a "Home" address type.

Any ideas? Thanks in advance.

Scott

Edited July 19, 2006 by Guest

[Søren Dyhr]

The table from which you pluck Addresses, could be driven by other and more sophisticated relationtypes than cartesian product type.

You can based on what's already in the recieving portal make a valuelist act as a relational primary key for the plucking portal, to strain the showed records.

It's almost accomplished by the relational graph only, by duping TO's accordingly, just except an single unstored calc'field that generates the multilinekey for the straining is required beyond the graphing.

But it doesn't make much sense if I made you a template, if you havn't embraced the relational approaches fully with filemaker 7+ yet. So poke into the Migration Foundations Whitepaper and read on!

http://www.filemaker.com/downloads/pdf/techbrief_fm8_migrtn_found.pdf

--sd