1 2 Previous Next 25 Replies Latest reply on Jul 30, 2012 12:59 PM by Nighthawk

    Anyone up for some FileMaker 6 stuff..?

    Nighthawk

      hi everybody...

       

      i just need some help on my thinking... which i think i got the right idea.... however results returned by the if statement is incorrect... can anybody shed some light...?

      so here it comes... tried with an embedded case statement... for example when entering a 4 i will get a result "now it is time"... and it should actually say... not calculating that...

      any help is much appreciated...

      regards from germany...

       

       

      If(IsEmpty(inv age) or inv age < "10",

       

      "not calculating that",

       

      If(inv age > "10" and inv age < "20", "just issued",

       

       

      If(inv age > "21" and inv age < "30", "coming up",

       

       

      If(inv age > "31" and inv age < "45", "now it is time",

       

       

      If(inv age > "46" and inv age < "60", "action required",

       

       

      ""

       

       

      )))))

        • 1. Re: Anyone up for some FileMaker 6 stuff..?
          TomHays

          The situation you are encountering is common to FM6, FM7-11, and FM12.

           

          You are comparing text to numbers.

           

          When comparing text, "10" < "2"  in the same way that "ba" < "c". The text is compared by how it would sort alphabetically.

           

          You need to use

           

          age > 10

           

          and similar for all of these numeric comparisions.

          Leave off the quotes around the numbers.

           

          -Tom

          1 of 1 people found this helpful
          • 2. Re: Anyone up for some FileMaker 6 stuff..?
            Nighthawk

            hi tom...

             

            thx for answering my question... problem though with the solution without the quotes... i get... an operator is expected here...

            which means it does not work without the quotes... i know it shouldn't be that difficult at all...

             

            any thoughts ?

             

            jürgen

            • 3. Re: Anyone up for some FileMaker 6 stuff..?
              wimdecorte

              also don't forget about the getAsNumber function.  You can use that in your formulae when you are not sure if the values are stored in text fields.  It lets you cast whatever source data you have into the proper data type for whatever comparison you want to do on it.

              • 4. Re: Anyone up for some FileMaker 6 stuff..?
                Nighthawk

                hi wim...

                 

                thx for your reply to my question... haven't worked with the getAsNumber function... so i will have to get my head around it... thx for pointing it out...

                any further help is much appreciated...

                 

                jürgen

                • 5. Re: Anyone up for some FileMaker 6 stuff..?
                  TomHays

                  This works.

                   

                  Case(

                  IsEmpty(inv age) or inv age < 10,"not calculating that",

                  inv age > 10 and inv age < 20, "just issued",

                  inv age > 21 and inv age < 30, "coming up",

                  inv age > 31 and inv age < 45, "now it is time",

                  inv age > 46 and inv age < 60, "action required",

                  ""

                  )

                   

                   

                  -Tom

                  • 6. Re: Anyone up for some FileMaker 6 stuff..?
                    Nighthawk

                    hi tom...

                     

                    hate to burst your bubble... same problem... "not calculating that" is coming up highlighted... and the message is the same... an operator is expected here...

                    so sorry... not working for me...

                     

                    jürgen

                    • 7. Re: Anyone up for some FileMaker 6 stuff..?
                      TomHays

                      No worries. My bubble is fine here. I copied and pasted the calculation directly from a working FM5.5 database calculation.

                       

                      I recommend that you remove extra spaces and line breaks. There might have been (invisible) artifacts from copying it from the web page that are making FileMaker unhappy.

                       

                      -Tom

                      • 8. Re: Anyone up for some FileMaker 6 stuff..?
                        keywords

                        Hi Jurgen.

                         

                        Tom is on the right track, but I think you are missing the point. Your calc is basing its outcome on the "inv age" field, and it's THIS field which should be a number to deliver the result you want.  If FileMaker is insisting on quaotes around your numbers in the calc, that's a pretty clear indication that the "inv age" field is text.  If you changed that field to number and remove the quotes around the numbers in your calc (or use the version Tom has put up) you will get the result you want. If there is some significant reason why the "inv age" field must be text, then using getAsNumber() should also deliver the right result; this function is telling FileMaker "even though the value in the field is text I want you to treat it as if it were a number for this calulation".

                         

                        Cheers!

                        • 9. Re: Anyone up for some FileMaker 6 stuff..?
                          LyndsayHowarth

                          Did FM 6 even have isempty()?

                           

                          Sent from my iPad

                          Lyndsay Howarth

                          11th Hour Group Pty Ltd

                          • 10. Re: Anyone up for some FileMaker 6 stuff..?
                            keywords

                            It's been there since at least FM3.

                            • 11. Re: Anyone up for some FileMaker 6 stuff..?
                              LyndsayHowarth

                              LOL... I was stuck on my iPad so I couldn't check.

                              I've used every version of FM and sometimes blur on which version introduced which functions...

                              - Lyndsay

                              • 12. Re: Anyone up for some FileMaker 6 stuff..?
                                erolst

                                Hi Jürgen,

                                 

                                this really should work:

                                 

                                Case

                                (

                                IsEmpty ( inv age) or inv age < 10 ; "not calculating that" ;

                                inv age < 20 ; "just issued" ;

                                inv age < 30 ; "coming up" ;

                                inv age < 45 ; "now it is time" ;

                                inv age < 60 ; "action required"

                                )

                                 

                                If "An operator is needed…" comes up, make sure you use the correct delimiter.

                                Before FM7, this could be either a comma or a semicolon, depending on your system

                                (it definitely was a semicolon on German systems).

                                 

                                Regards from the deep south

                                • 13. Re: Anyone up for some FileMaker 6 stuff..?
                                  Lemmtech

                                  Here is how I do this for aging invoices only depens on one field and using the current date. you can put in your text of course:

                                   

                                  Case(

                                  Get(CurrentDate) - Invoice_Date ≤  10;"10 Days or Less";

                                  Get(CurrentDate) - Invoice_Date ≤  20 and Get(CurrentDate) - Invoice_Date > 10;"10 to 20 days old";

                                  Get(CurrentDate) - Invoice_Date ≤ 30 and Get(CurrentDate) - Invoice_Date > 20;"20 to 30 days old";

                                  Get(CurrentDate) - Invoice_Date ≤ 60 and Get(CurrentDate) - Invoice_Date > 30; "30 to 60 days old";

                                  Get(CurrentDate) - Invoice_Date ≤ 90 and Get(CurrentDate) - Invoice_Date > 60; "60 to 90 days old"; Get(CurrentDate) - Invoice_Date >  90; "More than 90 days old.")

                                   

                                  Just make sure it's an unstored calc.

                                  1 of 1 people found this helpful
                                  • 14. Re: Anyone up for some FileMaker 6 stuff..?
                                    Malcolm

                                    You don't need AND statements. In each instance, the second part of the AND has already been tested and proved by the prior steps. You can reduce the number of calculation by half:

                                     

                                    Case(

                                    Get(CurrentDate) - Invoice_Date ≤ 10; "10 Days or Less";

                                    Get(CurrentDate) - Invoice_Date ≤ 20; "10 to 20 days old";

                                    Get(CurrentDate) - Invoice_Date ≤ 30 ;"20 to 30 days old";

                                    Get(CurrentDate) - Invoice_Date ≤ 60 ;"30 to 60 days old";

                                    Get(CurrentDate) - Invoice_Date ≤ 90 ;"60 to 90 days old";

                                    "More than 90 days old."

                                    )

                                     

                                    You could reduce the overhead even more by using CHOOSE.

                                     

                                    if (

                                         Get(CurrentDate) - Invoice_Date ≤ 90 ;

                                         choose (

                                              DIV( Get(CurrentDate) - Invoice_Date ; 10 )

                                              "10 Days or Less";

                                              "10 Days or Less";

                                              "10 to 20 days old";

                                              "20 to 30 days old";

                                              "30 to 60 days old";

                                              "30 to 60 days old";

                                              "30 to 60 days old";

                                              "60 to 90 days old";

                                              "60 to 90 days old";

                                              "60 to 90 days old";

                                         ) ;

                                         "More than 90 days old."

                                    )

                                     

                                    Malcolm

                                    1 2 Previous Next