8 Replies Latest reply on May 5, 2017 6:37 AM by philmodjunk

    Using Value list or case statement

    bookalaka

      Hi,

       

      I'm stuck on a calculation.. I need to have multiple codes having this restriction. What is the best way to do multiple cases? I tried using "or" but that didn't work..

       

       

      If (CODE = "ADM" and YTD_IGS % ≥ "1.1"; "1.1" ; YTD_IGS %)

       

      CODE = "BCD"

      CODE = "JGR"

      CODE = "LSN"

      CODE = "ACC"

       

      Thanks in Advance for the help.

        • 1. Re: Using Value list or case statement
          Jaymo

          Case(

           

          CODE = "ADM" and YTD_IGS % ≥ 1.1; 1.1;

           

          CODE = "BCD" and YTD_IGS % ≥ 2.1; 2.1;

           

          CODE = "JGR" and YTD_IGS % ≥ 3.1; 3.1;

           

          YTD_IGS %

           

          )

          • 2. Re: Using Value list or case statement
            philmodjunk

            If (PatternCount ( "BCD JGR LSN ACC ADM" ; CODE ) and YTD_IGS % ≥ "1.1"; "1.1" ; YTD_IGS %)

            • 3. Re: Using Value list or case statement
              ch0c0halic

              I suggest using Case() instead of IF() and PatternCount() to test for any of the values listed. BTW, special characters (for example: %) and spaces in field, relationship, or file names is never a good idea. I suggest changing "YTD_IGS %" to "YTD_IGS_pct". Worst case it can make a field unusable in a Calculation.

               

               

              Case ( PatternCount ( "ADM, BCD, JGR, LSN, ACC" ; CODE ) and YTD_IGS % ≥ "1.1"; "1.1" ; YTD_IGS %)

              1 of 1 people found this helpful
              • 4. Re: Using Value list or case statement
                bookalaka

                Tried this receive error "There are few too parameters in the function" The ) gets highlighted?

                • 5. Re: Using Value list or case statement
                  philmodjunk

                  Look again. My original post left out a parameter which, moments later, I added in by editing. If you are looking at email, it won't show the corrected expressionl

                   

                  I suggest using Case() instead of IF()

                   

                  I will gently disagree. I won't complain if a future version of FileMaker deprecates IF with conversion code updating expressions to use case instead, but as long as we have both functions, I use IF when there is clearly only two possible result values and case when there is more. To me, that adds just a tiny bit of useful "self documentation" that I'd lose if I used Case in place of IF.

                   

                  Should the need to add more results arise in the future, it's a matter of seconds to replace that one word "If" with the word "case" before editing to add more Boolean and Result expressions.

                  • 6. Re: Using Value list or case statement
                    bookalaka

                    Case ( PatternCount ( "ADM, BCD, JGR, LSN, ACC" ; CODE ) and YTD_IGS % ≥ "1.1"; "1.1" ; YTD_IGS %)

                     

                     

                     

                    Must have typed incorrectly the first time. THIS IS A WINNER!! Thanks!!

                    • 7. Re: Using Value list or case statement
                      bookalaka

                      Hello Again,

                       

                      Needing to modify part of the calculation based on additional conditions. Without going into a found set of records how can I get the total of a field in each record?

                       

                      If ( PatternCount ( "BKS,TZA"; CODE ); YTD_CS_Sold + YTD_CS_Sold)

                       

                      I need to get the Sum of Cases Sold from  "BKS" YTD_CS_Sold and TZA YTD_CS_Sold

                       

                      The way I have now I only its adding to itself

                       

                      example BKS Sold 30 and TZA sold 20 my new field gives me 50

                       

                      Thank You

                      • 8. Re: Using Value list or case statement
                        philmodjunk

                        Looks like you could set up a relationship that matches to the records you want to sum. Then sum ( relatedTable::field) can sum across the set of related records.

                         

                        ExecuteSQL could also be used.