A bit of information of your context and purpose would help us here. It would also help to know your FM version. Are you using FMP Advanced?
You could solve with a looping script, a recursive custom function, a repeating calculation field OR with an UNSTORED calculation field that calls itself.
I'll prospect you the calculation for the last one:
$i = $i + 1;
d = Date ( Month ( StartDate ) + $i - 1 ; 1 ; Year ( StartDate ) )
d ≤ EndDate ; List ( Left ( Proper ( MonthName ( d ) ) ; 3 ) ; ThisCalculationField ) ;
Let ( $i = "" ; "" )
To explain further....
I am using FM Pro advanced version 11
I have the following fields.
- Campaign start date (date field)
- Campaign end date (date field)
- Campaign duration (calculation field = end - start)
- Campaign Revenue (number field)
- Jan Revenue (number field that inserts an auto calc)
- Feb Revenue (number field that inserts an auto calc)
- March Revenue (number field that inserts an auto calc)....continue series i.e. a revenue field for each month of the year
I am wanting the users to be able to input the start date, the finish date and campaign revenue. From these inputs I want the revenue by month to calculate automatically. e.g. if the customer inputs the following data
Campaign start date: 21 feb 11
Campaign end date: 23 Jun 11
Campaign revenue: $10,000
Campaign duration (calculation field) = 122 days
The results should populate as follows:
Jan revenue (number field): $0
Feb revenue (number field): $655.74 or (10,000/122)*8 days
Mar revenue (number field): $2540.98 or (10,000/122)*31 days
Apr revenue (number field): $2459.00 or (10,000/122)*30 days
May revenue (number field): $2540.98 or (10,000/122)*31 days
Jun revenue (number field): $1885.25 or (10,000/122)*23 days
Jul revenue (number field): $0
I hope this makes more sense.
rabaudi said, "with an UNSTORED calculation field that calls itself."
The calculation you presented is dangerous and undocumented behavior.
As quoted from Comment:
"For anyone reading this thread, you included, I'd recommend against forcing recursion by referencing a calculation field to itself. Although it might seem to work in some circumstances, it's not possible to predict how it will behave under other conditions. Unlike a custom function, there is no built-in protection against stack overflow and you might easily end up with the application crashing and your file being corrupted."
I myself have had issues with it as well.
"I myself have had issues with it as well."
It will be nice if you could make an example ( with recursion's number < 200 to be safe )