Enforce relationship match in FileMaker

Question

I have two FileMaker tables that are linked using a relationship. The relationships matches based on the content of two fields:

TableA.Foo matches TableB.Foo
TableA.Bar matches TableB.Bar

Is there a way to enforce constraints on this relationship in FileMaker? When entering objects in TableA, I'd like to ensure that the values entered in fields Foo and Bar are such that there exists a corresponding record in TableB. Is this possible?

Bonus points if I could somehow autocomplete values of Bar based on the entries of TableB and the value already entered in Foo.

Answer 1

To answer your question as asked:

1. You can validate the Bar field (or any other field) by calculation:

   not IsEmpty ( TableB::Foo )
   

   Set the validation to validate Always. This will throw a validation error if you try to commit a record in TableA without having a related record in TableB.

2. To auto-complete based on values in another field, you must format the target field as Drop-down list with Auto-complete using a value list.

   Before defining the value list, set up a new relationship (using a new occurrence of TableB) as:

       TableA::Foo = TableB 2::Foo
   

   Then define the value list to use values from TableB 2::Bar, show only related values starting from TableA.

I suspect there may be better ways to accomplish whatever you're trying to accomplish here.