I'd like some way to filter a value list like a portal is filtered if possible.
create calculation fields for each of the types (="Lawyer") in the Matter table and have a relationship to the Contacts table for each.
Then, you create a valuelist for each filtered by each relationship and use whereever.
Your calcs could also be in the contacts table with a self-relationship by type.
You could also have a relationship by type that uses a global field on one side where the value of the type is chosen and set up your relationship and valuelist to match. There can only ever be one active type though.
Can you show me what the full calculation would look like? Would it return a single value or all the values?
I guess I don't quite understand how this works.
Why would the calculation be in the Matter table?
I did create a calculated field called IsLawyer in the Matter table with this formula:
Matter_Contact::Contact_Type = "Lawyer";
I then duplicated the Matter_Contacts table (called Matter_Lawyer) and linked it to the Matter table with the MatterIDs in both tables matching and the Matter::IsLawyer matched up with the MatterLawyer::ContactID, but the value list still shows all contacts.
I need a value list that shows only the Attorneys that have already been added to a Matter as a Contact.
IUUC, you want a value list that shows only the Contacts that have already been added to a Matter in the role of "Attorney". That is very different from Contacts that have already been added to a Matter and their type - in the Contact_Type table - is "Lawyer".
If it's indeed the former you want, you could add an unstored calculation field to the Matters table (result is Text) =
then define a new relationship between Matters and Matter_Contacts, matching MatterID to MatterID AND the calculation field to Role.
Alternatively, you could define a new calculation field in the Matter_Contacts table =
Case ( Role = "Attorney" ; ContactID )
and use this as the basis of your value list.
I added a calculated field in the Matter_Contacts table called IsLawyer which your calculation.
I then modified the value list so it is as follows:
Use Values From Field
Include All Values
Show Only Values From The Second Field
But the drop-down is showing all the names added to the matter.
What am I missing?
Thanks, I think I got it.