7 Replies Latest reply on Dec 7, 2012 2:34 PM by andrewtraub

    Conditional Value Lists

    andrewtraub

      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.

       

      Andrew

        • 1. Re: Conditional Value Lists
          andrewtraub

          I'd like some way to filter a value list like a portal is filtered if possible.

          • 2. Re: Conditional Value Lists
            LyndsayHowarth

            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.

             

            HTH

             

            - Lyndsay

            • 3. Re: Conditional Value Lists
              andrewtraub

              Thanks Lindsay,

               

              Can you show me what the full calculation would look like?  Would it return a single value or all the values?

               

              Andrew

              • 4. Re: Conditional Value Lists
                andrewtraub

                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:

                 

                Case(

                 

                Matter_Contact::Contact_Type = "Lawyer";

                 

                Matter_Contact::ContactID

                 

                )

                 

                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.

                • 5. Re: Conditional Value Lists
                  comment

                  andrewtraub wrote:

                   

                  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) =

                   

                  "Attorney"

                   

                  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.

                  • 6. Re: Conditional Value Lists
                    andrewtraub

                    Thanks.

                     

                    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

                    First Field->Matter_Lawyer::IsLawyer

                    Second Field->Contact::FullName

                    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,

                     

                    Andrew

                    • 7. Re: Conditional Value Lists
                      andrewtraub

                      Thanks, I think I got it.

                       

                      Andrew