9 Replies Latest reply on Jul 8, 2014 7:09 PM by underthepump27

    If statements



      If statements


           Hi there, if someone could help me it would be greatly appreciated. I have had a look through the knowledge database but to no avail.

           I am trying to get a numerical value from a value in a field.

           Unfortunately I get a ;one' if the field is occupied, and 'zero' if the field is empty.


           The if statement is as follows-:

           If (Terms; 'Nett 7 days'; 7)


           If (Terms; 'net 14 days'; 14)


           If (Terms; 'prepaid'; 0)


           If (Terms; 'C.O.D'; 0)


           If (Terms; 'Nett 30 days'; 30)

           The calculation result is set to number and the number of repetitions is set to 5

           Any help would be greatly appreciated.


        • 1. Re: If statements

               I'm guessing you're defining a calculation field ? If so:

               Case(Terms = "Nett 7 days" ; 7;
               Terms = "Nett 14 days" ; 14 ;
               Terms = "Nett 30 days" ; 30 ; 0 )

               which is basically saying If Terms = 7, 14 or 30 set the calc field to the correct number, otherwise set it to 0

          • 2. Re: If statements

                 If we now try and add this variable to an invoice date, what would be the best way.

                 the result of the 'case' I am calling 'settle days'

                 would the 'due date now be calculated as 


                 Date(Month(invoice date) ; Day(invoice date) + settle days ; Year(invoice date)

                 where settle days is a number? Also would 'settle days' need to be in the same table as 'invoice date'

                 the current return is 00/00/0000


                 thanks for any help.

            • 3. Re: If statements

                   Make sure that Invoice date is a field of type date. Your expression should work if Invoice Date is not a text field and is the correct field to refer to here, but it can be simplified to be:

                   Invoice date + settle days

                   Make sure that the result type for settle days is "number".

                   Also would 'settle days' need to be in the same table as 'invoice date'

                   If either settle days or invoice date are not defined in the same table as this calculation, your syntax would be slightly different as you would need to use TableOccurrenceName::FieldName notation in place of the Field Name notation you show here. If the fields are not all in the same table, the relationship between the tables and the "context" you select at the top of the Specify Calculation dialog can affect what result is returned by the calculation.

                   If "Table Occurrence" is an unfamiliar term: Tutorial: What are Table Occurrences?

              • 4. Re: If statements

                     Thanks for the help so far, I am very grateful for it.


                     I have one last query; I now need the due date to consider one option.

                     If the "settle days" = 60, the due date is to be last day day of the following month 

                     i.e. if the invoice date is 20th March 2003 and the settle days has returned '60' , the due date will be 30th April 2003


                     due date = invoice date + settle days

                     Thanks in advance, this forum is priceless>

                • 5. Re: If statements

                       Hi Michael

                       To obtain the last day of the next month, the calculation is Date(Month(Date)+2;0;Year(Date))

                  • 6. Re: If statements

                         oops ... ps where the Date field enclosed in brackets is your Invoice::Date

                    • 7. Re: If statements

                           Thanks for that. so bringing all this together, would I now use an 'if' statement to get the result for the due date?

                           Such as -:


                           Due Date =

                           Invoice Date + Settle Days


                           If(settle days; 60 ; Date(Month(invoice date)+2 ; 0 ; Year(invoice date)))

                           The reason for this is that for all trading terms, the due date is straightforward, the odd case out is when the trading terms end on the last day of the following month, which I call 60 in settle days.

                           Or would I be better of with the following-:

                           If settle days does not equal 60, due date = invoice date + settle days


                           if settle days = 60 then due date = date(month(invoice date)+2 ; 0 ; Year(invoice date)))


                           How would this be written as an argument?

                           Thanks Again.

                      • 8. Re: If statements


                             So your DueDate field would be a date field, with the Calculation:
                             Case(SettleDays  ≠ 60 ; Invoice::Date + SettleDays ;
                             SettleDays = 60 ; Date(Month(Invoice::Date)+2 ; 0 ; Year(Invoice::Date)) )
                             note that this does not have a default value ... obviously!
                        • 9. Re: If statements

                               Thanks everyone for all your help, I am truly grateful.