8 Replies Latest reply on Feb 4, 2013 11:12 AM by ericjlawson

    Swap between value lists based on another table value

    ericjlawson

      Hi,

       

      I have a Diary Note layout linked to a parent Customer table. Customers can have Invoices and Quotes, both of which are stored in separate tables.

       

      When creating a diary note (navigated to from the Customer layout), I present a field on the layout called NoteType. This has a value list (hard coded) with four values. They are

       

      • Invoice
      • Quote
      • Enquiry
      • Other

       

      Currently there is a field in the DiaryNote table called RelatedReference. If its a diary note about an invoice, the user wants to store the relevant Invoice code. If its about a quote, then the user wants to save the relevant Quote code. Or they can choose Enquiry or Other, neither of which require a Reference code value.

       

      So, it was easy establishing the value list to get all invoices (and the Customer name from customer table as the 2nd field). This is what I did as the design evolved, when it was just Invoices as the NoteType.

       

      Now, if the user chooses Quote as the NoteType, I want the data presented in the RelatedReference drop down value list to come from the quote table. If they choose Enquiry or Other I want to disable the RelatedReference field.

       

      So the inspector easily allows me to choose on or the other Value List (I have named them CustInvList & CustQuoteList). However, I figure I must be able to setup a trigger on the NoteType field, so that when it changes I can fire a script that can change the Value List available via the RelatedReference field. Or if its Enquiry or Other, disable it.

       

      I am fairly new to Filemaker but am reasonably experienced at programming and database design. So basically, I hoping I just need to know how to target the "Values From" attribute of the data element of the field. I would prefer not to have 2 separate field objects (1 per value list) and toggle them.

       

      Thanks in advance

      Eric

        • 1. Re: Swap between value lists based on another table value
          ariley

          In FileMaker the value lists are somewhat hard-coded. You can generate the values dynamically but only from one table. 

           

          What you could do is take the user to different layouts with the trigger and show them the appropriate value list based on their previous choice.

           

          Hope this helps. 

           

          Best regards,

           

          agnes b. riley

          filemaker and web development

          zeroblue

           

          TWO-TIME MAD DOG AWARD WINNER

          FileMaker Certified in 10 and 11

          Member, FileMaker Business Alliance

          T: 877 917-9079 . C: 917-660-7221

          1 of 1 people found this helpful
          • 2. Re: Swap between value lists based on another table value
            ericjlawson

            Hi Agnes,

             

            I feared this might be the case. Thanks for the help as I spent many hours reading a manual last night to no avail. I will develop a workaround as you suggest.

             

            Is it possible to 'disable' a field on a layout with a trigger as I suggested for the other two note types I mentioned (ie. Enquiry and Other)?

             

            Thanks

            Eric

            • 3. Re: Swap between value lists based on another table value
              Mike_Mitchell

              You could do this by having a calculation field (set for global storage) like this:

               

              Case ( NoteType = "Invoice" ; List ( Invoices::{field} ) ; NoteType = "Quote" ; List ( Quotes::{field} ) ; "" )

               

              and base your value list on that. Set up a Cartesian join from your current table to each of Invoices and Quotes to get all records in the related table.

               

              HTH

               

              Mike

              1 of 1 people found this helpful
              • 4. Re: Swap between value lists based on another table value
                ericjlawson

                Hi Mike,

                 

                I assume I can access multiple fields in the list

                 

                ie. - List ( Invoices::{field-a, field-b} ) OR

                      List ( Invoices::{field-a}, Invoices::{field-b} )

                 

                I suppose if thats not possible, I can create a new calculation field that concats the two fields I want in the value list.

                 

                I know what a Cartesian join is; I assume I need to do that with the Execute SQL function. I am a bit worried that CJ could produce quite a big dataset and I am guessing global storage is going to use RAM for each active session.

                 

                I havent dabbled with global storage yet so I will try this in version 2 of the application.

                 

                Thanks for the suggestion, clearly there are plenty of inventive methods for achieving tasks in FM.

                 

                Cheers

                Eric

                • 5. Re: Swap between value lists based on another table value
                  Mike_Mitchell

                  In this case, you'll need to contat the fields together.  (Edit: Or, you can make two calculation fields, one for each part you want to include in the value list, and then use those two.)


                  And - my mistake - you'll need to do it as an auto-enter calculation instead of a global calc. Global calcs can't be indexed so they can't be used in a value list.

                   

                  No, you don't need to use ExecuteSQL (although you could, and that's another way to do this sort of thing). A Cartesian join looks like this:

                   

                  join.png

                   

                  Basically means "all records in table A match all records in table B". What you'd do is make a join to Invoices and to Quotes and then tell the calculation to pick the one to use based on the selection in the field.

                   

                  HTH

                   

                  Mike

                  • 6. Re: Swap between value lists based on another table value
                    ariley

                    You can control field entry based on a calculation or with a triggered script that checks for a condition and commits the record if that condition is not met. 

                     

                    Best regards,

                     

                    agnes b. riley

                    filemaker and web development

                    zeroblue

                     

                    TWO-TIME MAD DOG AWARD WINNER

                    FileMaker Certified in 10 and 11

                    Member, FileMaker Business Alliance

                    T: 877 917-9079 . C: 917-660-7221

                    • 7. Re: Swap between value lists based on another table value
                      ericjlawson

                      Thanks, getting on very well now. I appreciate your help.

                      • 8. Re: Swap between value lists based on another table value
                        ericjlawson

                        I am still getting used to the main access method being controlled, rather than coding native SQL. Makes more sense now. Thanks for your help Mike.