5 Replies Latest reply on Jun 7, 2012 6:35 AM by johnhorner

    Help with a Case calculation



      Help with a Case calculation


      Hi Im wondering if some one could help me... I have a field calculation with the following:

      Case ( _fkNoFactura ≤ 236  ; Producto ProPlan::Precio sugerido publico ; _fkNoFactura > 236 ; Producto ProPlan::Precio sugerido publico 1jun2012 )

      But what I really want is that the test part may be than when the number of _fkNoFactura is between 0 and 236 then the result would be Producto ProPlan::Precio sugerido pubilco, but I just don´t know how I should write that... I´ve tried 0<_fkNoFactura ≤ 236 but it doesnt work, also I´ve tried _fkNoFactura ≤ 236 & 0 >_fkNoFactura and it doesnt work.

      By the way if also some one can explain me how can I put into a Case test to look up between one date and aonther, I will appreciate so much



        • 1. Re: Help with a Case calculation

          if i understand your question correctly you are very close...

          try: 0 < _fkNoFactura and _fkNoFactura ≤ 236

          the "&" symbol is used to assemble text strings (e.g. "A" & "B" & "  " & "C" results in "AB  C")

          you also had the "<" operator going in the wrong direction in your statement:" _fkNoFactura ≤ 236 & 0 >_fkNoFactura" ("0 > _fkNoFactura" it is only true when _fkNoFactura is negative)

          hope that helps!

          • 2. Re: Help with a Case calculation


            Home > Reference > Functions reference > Logical functions > Case

            Case evaluates each test expression in order, and when a True expression is found, returns the value specified in result for that expression.
            You can include a default result at the end of the parameter list. If none of the expressions evaluate to True, the Case function returns the value specified for defaultResult. If no default result is supplied, Case returns an empty result.

            Case(Score >= 90;“Excellent”;Score > 50;“Satisfactory”;“Needs Improvement”) displays Excellent when the score is 90 or above, Satisfactory when the score is between 50 and 90, and Needs Improvement for any other score.
            Case(Shipment Method=”Ground”;2;Shipment Method=”Air”;10) returns 10, when the Shipment Method field contains Air.
            • 3. Re: Help with a Case calculation


              I also agree davidanders statement.

              you should first write the case in proper format.

              Default statement should be there.

              For your query it is better to write like this

              (_fkNoFactura>0) and (_fkNoFactura<=236)


              I  hope you will get your answer.



              • 4. Re: Help with a Case calculation

                And you asked about searching a date range. If you need to explicitly specify a date in the calculation, care must be taken with the syntax:

                DateField > Date ( 1 ; 1 ; 2012 ) and DateField < Date ( 1 ; 31 ; 2012 )

                Will be true for any date in DateField in the month of January, 2012

                GetasDate ( "1/1/2012" )

                Will also work.

                These two expressions, however, will NOT work:

                DateField > 1/1/2012

                DateField > "1/1/2012"

                FileMaker won't recognize either of these as dates when evaulating the expression. The first is interpreted as division (1 divided by 1 divided by 2012). The second is text.

                • 5. Re: Help with a Case calculation

                  i don't entirely agree atauf...

                  imho, there is not one "proper" format for writing case statements.  in this situation, parentheses are not necessary and do not affect the proper evaluation of the case statement.  it may make it easier for the developer to read, which may be reason enough to use them, but perhaps a purist could argue that extraneous characters in a calculation only serve to make it less efficient... i don't actually know enough about computer science to know whether this is the case or not but it seems like a reasonable proposition.  similarly, the statement can be written as:

                  case(0<_fkNoFactura and _fkNoFactura≤236;Producto ProPlan::Precio sugerido publico)


                  case (

                       0 < _fkNoFactura and _fkNoFactura ≤ 236 ;

                            Producto ProPlan::Precio sugerido publico



                  case (

                       0 < _fkNoFactura


                       _fkNoFactura ≤ 236 ;

                            Producto ProPlan::Precio sugerido publico


                  it makes no difference.  i have seen all three of these conventions and no doubt there are many others.

                  additionally, while i agree that there are many (most?) situations where you would want to use a default case, it is not necessary or even desirable in every situation.  even the filemaker reference that david posted shows a case statement with no default case (the shipping example).