7 Replies Latest reply on Nov 20, 2013 4:06 PM by philmodjunk

    Can anyone fix this case?

    ngarnier

      Title

      Can anyone fix this case?

      Post

           whatever I'm doing wrong, it's not working.
            
           field A should = 0,1,2,3,4,or 0 again if drop down choices for field B are either result1, 2,3,4,5, or 6 respectively.  I can't figure out how to return a result from the drop down without copying in the actual value.
            
           Field A=
            
           Case ( Field B = "result1" ; "0" ;
                     Field B= "result2" ; "1" ;
                     Field B = "result3" ; "2" ;
                     Field B = "result4" ; "3" ;
                     Field B = "result5"; "4" ; 
                     Field B = "result6"; "0" ;)
            
            
           Note:
           result1 etc should be the command to return the first value from the list, not the copied value

        • 1. Re: Can anyone fix this case?
          philmodjunk
               

                    result1 etc should be the command to return the first value from the list, not the copied value

               And what list is that?

               And example with actual values from your value list in Field A with example results in Field B could be very helpful.

               You might not even want to use a calculation for Field B as there are other options that can be a better approach in many cases.

          • 2. Re: Can anyone fix this case?
            ngarnier

                 Let's say Field B = cereal, Field A = # of cereal boxes per unit, and I have a 3rd field to add up the sum of the number of boxes.  (I can't just do the SUM function on the row of field B's because it has more numbers in the drop down choices than just the # of boxes so the sum is off.

                 When I choose from the drop down menu in Field B, the corresponding choice should appear in field A

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                                Field B                     drop down choices                                           Field A = triggered by field B                     drop down choices                                           Field C = sum of all field As
                                Kellogs cereal                     frosted flakes 474                                           # of boxes Kellogs                     2                                           should = 4 when fruit loops and apple jacks are chosen
                                                      fruit loops 221                                                                 3                                            
                                                      capn crunch 392                                                                 4                                            
                                                                                                                                                                     
                                General Mills                     apple jacks 7724                                           # of boxes General Mills                     1                                            
                                                      oat bran 266                                                                 5                                            
                                                      cherrios 2829                                                                 7                                            

                 *These are not accurate brands to the company names for anyone who might be nitpicky

            • 3. Re: Can anyone fix this case?
              philmodjunk
                   Assuming that your value list for Field B is:

                   frosted flakes 474
                   fruit loops 221
                   capn crunch 392
                   apple jacks 7724
                   oat bran 266
                   cherrios 2829
                    

                   Then this case function will work for Field A:

                   Case ( Field B = "frosted flakes 474" ; 2 ;
                             Field B= "fruit loops 221" ; 3 ;
                             Field B = "capn crunch 392" ; 4 ;
                             Field B = "apple jacks 7724" ; 1 ;
                             Field B = "oat bran 266"; 5 ; 
                             Field B = "cherrios 2829"; 7
                           ) // case
                   But this does not look like a situation where such a calculation field is the best method to use. If it doesn't, make sure that Field B is of type text and that the text shown here is what the value list is actually entering into the field--it could enter an ID number.
                    
                   If you set up a related table with this same data (each row below is one record)
                   Field B                  | Field A
                   frosted flakes 474 | 2
                         fruit loops 221      | 3
                        capn crunch 392   | 4
                        apple jacks 7724   | 1
                        oat bran 266         | 5
                        cherrios 2829       | 7

                        Then you could set up a relationship matching Field B of your original table to Field B of this related table and then you can use auto-enter field options to look up (copy) this data into Field A of your original table. This is how prices are looked up into Invoice Data records in the Invoices starter solution that comes with FileMaker 12. This method allows a user (instead of you, the developer) to change what values are looked up with each choice in your value list.

                        Field C should be a summary field in either case and your example seems made to order for a conditional value list where the first field is used to select either "Kellogs" or "General Mills" and then the second value list update to only list the brands for that specified company.

                    

              • 4. Re: Can anyone fix this case?
                ngarnier

                     Putting the case together like this worked.  Thanks so much.  

                     Case ( Field B = "frosted flakes 474" ; 2 ;
                               Field B= "fruit loops 221" ; 3 ;
                               Field B = "capn crunch 392" ; 4 ;
                               Field B = "apple jacks 7724" ; 1 ;
                               Field B = "oat bran 266"; 5 ; 
                               Field B = "cherrios 2829"; 7
                             ) // case

                      

                • 5. Re: Can anyone fix this case?
                  philmodjunk

                       It works, but may not be the best option for this type of data manipulation.

                  • 6. Re: Can anyone fix this case?
                    ngarnier

                         I'm really new to filemaker and really bad at it so far, so anything that does basically what I need is a miracle, but yes I'm sure the entire database I've created is full of awful workarounds for things that can be simpler and more efficient. frown

                          

                    • 7. Re: Can anyone fix this case?
                      philmodjunk

                           Fair enough and we're here to help you improve on that.

                           Please keep in mind that your invoices starter solution provides a working example of the alternative approach that I described so you can open it up and examine how it works..