6 Replies Latest reply on Apr 28, 2009 4:46 PM by philmodjunk

    Newbie Help with if function

    DaveParry

      Title

      Newbie Help with if function

      Post

      I have a table that has a field called "from" and another field called "value" which has a monetory value, I want to have a total value for (say) leaflets after a specific date. These are to be displayed on another layout which I have associated with the table. 

      I have created a calculation field (leaflet) with "If (From="leaflet" and Date >4/4/2009; Value ; 0 )" and a summary field (sum leaflet) with "total of leaflet".

      It dosent calculate correctly and seems to sum all the leaflets regardless of date.

      Its probably something stupidly simple but I cannot see what.

       

        • 1. Re: Newbie Help with if function
          philmodjunk
            

          You're really close. FMP sees your date as a division expression (month divided by date divided by year)

           

          Enclose your literal date in a getasdate() function and it should work.

           

          If (From="leaflet" and Date > getasdate("4/4/2009"); Value ; 0 )

          • 2. Re: Newbie Help with if function
            claddam
              

            Hi Dave,

             

            Where are you getting the date from? Do you have a field called date? Or are you trying to get it from the system's date? 

             

            Nevermind, just saw Phil's post :) 

            • 3. Re: Newbie Help with if function
              DaveParry
                 Thanks Phil (and Claddam) worked a treat. and superfast reply 4 mins.
              • 4. Re: Newbie Help with if function
                DaveParry
                  

                Just to complicate things, is there a way I can modify the getsdate function so as to reference a value in say another field?

                I will have a few of these functions which would need changing each year, having to change just one field would make life a lot easier. 

                • 5. Re: Newbie Help with if function
                  davidhead
                    

                  Dave Parry wrote:

                  Just to complicate things, is there a way I can modify the getsdate function so as to reference a value in say another field?

                  I will have a few of these functions which would need changing each year, having to change just one field would make life a lot easier. 


                  My take on the last calculation is that you should really use the functions provided by FileMaker. It is better written as:
                     If ( From="leaflet" and Date > Date ( 4; 4; 2009) ; Value ; 0 )
                  This will avoid any internationalisation issues with date formats (dd/mm/yyyy versus mm/dd/yyyy).
                  For your new question, you may find it useful to create a date field that has global storage. That way you can put a reference date in that field rather than hard-coding the date into the calculation (as you have suggested above I think).
                  So the new calculation would be (where ReferenceDate is a global date field):
                     If ( From = "leaflet" and Date > ReferenceDate ; Value ; 0 )

                   


                  • 6. Re: Newbie Help with if function
                    philmodjunk
                      

                    if your field is a date field just insert the name of the field into your if statement.

                     

                    If (From="leaflet" and Date > tablename::datefieldname; Value ; 0 )

                     

                    Just so you know, date fields are really number fields with special formatting. You can use them in expression just like the field contains an integer.

                     

                    Put getasdate("1/1/2009") + 1 into a date field and you get 1/2/2009 (a date one day later.)