I have a table called "Matter" and a table called "Contacts". Each Matter can have many Contacts and each Contact can be in many Matters. I have a table called Matter_Contacts which links them and has an extra field called "Type" which specifies that Contact's role in the Matter.
So, for example, I can currently add several Contacts to a Matter and specify their role, like "Client" or "Attorney" or "Witness" or "Claims Handler" in that Matter.
I need to track other information for each role type, like for example, which Attorney represents which Client(s), specific information about a Claims Handler (like claim number).
Currently, I'm working on a Service portal. I need a value list that shows only the Attorneys that have already been added to a Matter as a Contact. So far, I've only been able to get the value list to be limited to the Contacts in the Matter, but not the Attorneys within the Contacts. In SQL, it would be something like adding "WHERE Contact_Type='Attorney'" but I can't figure out how to do this in FileMaker.
I know that the "Include only related values starting from:" needs to be selected and the correct table selected (I currently have Matter_Attorney selected, which is a duplicate of the Matter_Contact table). I'm not sure how to limit the Matter_Attorney table to only those that have a type of Attorney. Is that some sort of calculated field?
Any help would be greatly appreciated.