3 Replies Latest reply on Jul 25, 2014 10:43 AM by philmodjunk

    Calculation Help



      Calculation Help


           I am limited in my knowledge of calculations, not even sure the result I'm after can be done.

           I want a field that will switch value lists based on what is entered on another field.

           For example, I have a field with drop down list named Customer. If the customer picked is ABC, then I want 

           another field to pick a value list based on the customer's name, ABC. If customer name is DEF, then that field would switch to a different value list when clicked on. There would be 3 different value lists for 3 different customers.

           I tried this but FM says it can't find a field named "Shurtape", Shurtape is the customer.

           If ( Customer =  Shurtape, getvaluelist; Shurtape ITR ; )

           Any & all help would be appreciated.



        • 1. Re: Calculation Help

               The specific error message that you got is because you did not put Shurtape in quotes. Thus FileMaker is looking for a field named Shurtape to evalaute in order to access the data stored in it instead of comparing the literal text "shurtape" to the value of your customer field.

               But your approach won't work anyway. You can't select different value lists in the manner shown.

               But you can set up a table where each record supplies one or more values to a value list and use what is known as a conditional value list to control which subset of records supply values to the value list.

               The following list of links can be intimidating, but you really only need focus on the first and third links. The second is pretty redundant and the rest expand on the basic concept:

               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

               The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: Calculation Help


                 Thanks for the info. That's a lot to digest. It's tough for me to keep focused on FM & the work I get paid for. As soon as I get deep into FM & grasping how to do something I get handed work to do. But that's life & I'm grateful to have a job.

                 I have reviewed the 1st & 3rd articles, I found the 3rd more helpful. 4th & 5th are above my skill level. I consider my skill level as between novice-amatuer. 

                 I'm still a bit in the dark about whether I should have a separate value list for each customer or just one list with all of the items that would show only the items pertinent to each customer. My thinking is to have separate lists for each customer. 

                 I understand the table-relationship part. This will help with some other ideas I have for this form.

                 Thanks very much!


            • 3. Re: Calculation Help

                   You need one value list that filters down to specific sets of values for each customer. You can't dynamically create value lists for each customer anyway.

                   You shouldn't need the 4th and 5th links at this point in the process, if ever. They are there for those interested in taking a conditional value list to the next level or for those that have specific conditional value list needs.