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
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.
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?
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>
To obtain the last day of the next month, the calculation is Date(Month(Date)+2;0;Year(Date))
oops ... ps where the Date field enclosed in brackets is your Invoice::Date
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?
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!
Thanks everyone for all your help, I am truly grateful.