9 Replies Latest reply on Dec 14, 2012 10:34 AM by philmodjunk

    If/Case date problem

    C.AllenCrawford

      Title

      If/Case date problem

      Post

           Old dog trying to learn new software. This is too simple but can't seem to figure out why it is not working. I am simply trying to post a field with existing data if it meets 3 criteria but I can't even get one test to work.

           If(Date<1/1/2013;Amount;0)

           When I enter a date 12/12/2012 and amount, the result in the new field gives me 0 and it should give me the amount? (The field does require a 4-digit date but it should convert properly anyway)

           All I am trying to do is post a field for 2012Pledges on an ongoing basis as money is received.

           If(Date>12/31/2011 and Date<1/1/2013 and Type="Pledge";Amount;0)

           I am expecting that the field 2012Pledges would be updated with the posted amount.

           I am probably going to have to learn how to set a field etc but if I can't even perform a simple IF test this is going to be harder than I thought. Any help getting me started would be appreciated.

      FMScreenShot.JPG

        • 1. Re: If/Case date problem
          schamblee

               If(date<"01/01/2013";amount;0) would be your calculation. 

               Make sure the date field is date type.

          • 2. Re: If/Case date problem
            Abhaya

                 Yes the condition should be like this

                 If(date<"01/01/2013";amount;0) and the field must be Dtae type field.

                 you may also use the GateAsDate() fucntion to converrt both to date if they are not.

            • 3. Re: If/Case date problem
              raybaudi

                   First calc:

                   Case(
                   date < Date ( 1 ; 1 ; 2013 ) ; amount
                   )

                   Second calc:

              Case(
              Date> Date ( 12 ; 31 ; 2011) and Date < Date ( 1 ; 1 ; 2013 ) and Type="Pledge" ; Amount
                   )

              • 4. Re: If/Case date problem
                philmodjunk

                     FYI:

                     In the expression: if(Date<1/1/2013;Amount;0)

                     FileMaker interprets 1/1/2013 as 1 divided by 1 divided by 2013. That evaluates as a decimal less than 1 so it will always be an amount less than any date in your date field short of leaving the field empty.

                     That's why you have to use either the date function as raybaudi posted or GetAsDate ( "1/1/2013" ) to get FileMaker to interpret that data as a date.

                • 5. Re: If/Case date problem
                  C.AllenCrawford

                       To PhilModJunk..appreciated the explanation of why it didn't work. Just not sure if you make a field type Date why it doesn't treat it like one?

                       The recommended  If(date<"01/01/2013";amount;0) did not work (see screenshot)and you can see in the left box that the Type is Date. Is there another setting in Type Date that is causing the fail?

                  I did try the Case forwarded by raybaudi and the 2nd calc worked. Is the 1st calc necessary and if so, how do you nest them to be accepted because it didn't like it when I put both expressions in?

                  I know this is elementary to you guys but usually if I can get the hang of how the software is thinking it makes it easier for me to learn.

                  Thanks

                        

                        

                        

                  • 6. Re: If/Case date problem
                    raybaudi

                         The first cal was for your first example.

                         You'll need only the second one.

                    • 7. Re: If/Case date problem
                      philmodjunk

                           There is nothing wrong with the field of type date. Dates are stored as a number counting the number of days elapsed between 12/31/0000 and the date entered into that field. "01/01/2013" is a text string so you are comparing an integer number stored in that date field to quoted text.

                           That's why I used GetAsDate ( "01/01/2013" ) in my example. That function takes the quoted text and produces the needed intenger value so that it can be treated as a date.   Date ( 1 ; 1 ; 2013 ) does the same.

                            

                      • 8. Re: If/Case date problem
                        C.AllenCrawford

                             Ok that makes sense. Now to actually GetAsDate....it is not an option in the Specify Calculation View menu of functions. I guess this must be learned elsewhere. This syntax didn't work.

                             GetAsDate ( "01/01/2013" )

                             If(date<"01/01/2013";amount;0)

                              

                        • 9. Re: If/Case date problem
                          philmodjunk

                               It is indeed available in the list of functions in the Specify Calculation Dialog. You should look again.

                               Here's the syntax that you should be using:

                               If ( date < GetAsDate ( "01/01/2013" ) ; amount ; 0 )