Iterative test for blank or zero fiel

[enquirerfm]

I am trying to run a test on a field which if blank or less than 0 proceeds to copy the previous field into the one I am trying to 'Set'. However, it is not working. I have the following:

 

Set Field[Jan2013;Case (IsEmpty(Jan2013) or Jan2013 < 1;Dec2012;Jan2013)] so this should work to substitute the field if Jan2013 is empty. But what if Dec2012 is also empty? So I have the following underneath:

Set Field[Jan2013;Case (IsEmpty(Dec2012) or Dec2012 < 1;Nov2012)] etc for as many months as it takes.

Can someone suggest why this won't work? Many thanks.

[Ocean West]

Automatic message

This topic has been moved from "Articles, Tips, Techniques & Solutions" to "Calculation Engine (Define Fields)".

[doughemi]

If you have fields Nov2012, Dec2012, and Jan2013 all in one record, you have a serious problem with your data model.  Please give us more information about the process you are trying to model (in other words, tell us WHAT you are wanting to record, not HOW you are attempting to do it.

[enquirerfm]

Thanks. I have values which I have calculated for each month in the year - this is an average, say, of transaction and this value is placed into a field, say, Jan2013, Feb2013, March2013 etc. There is no problem with the calculation of these fields. However, where there have been no transactions during a particular month, say no transactions in January 2013, then the field Jan2013 is blank. However, I am using these monthly fields to make another calculation and I can't allow them to remain blank in order for this calculation to work properly. Therefore I need to fill the field with a value, say from the previous month, or if no value from that month either, from the month prior to this and so forth. When all else fails I can use the value from the next month. So assuming the critical months are April2013, May2013 and June 2013 this is what I tried to do - because the calculation above did not work but it also fails to place any values into the months when they are missing. So, this is what I have for a value to be calculated for April 2013 - I start by working forwards to May and then June and then consider March, Feb, Jan and finally back to July :

 

Set Field[April2013;Let(

[f1 =Jan2013;
f2=Feb2013;
f3=Mar2013;
f4=Apr2013;
f5=May2013;
f6=Jun2013;
f7=Jul2013];
Case (
f4>1; f4;
f5>1;f5;
f6>1; f6;
f3>1; f3;
f2>1; f2;
f7>1; f1;
"No possible value"
) )

[eos]

As doughemi suggested, your data model is serious flawed; each new month means a new summary, so you have to create a new field in your database and need to constantly adjust your calculation(s). That's exactly why child tables are used for such purposes - they give you an unlimited number of related entries/objects.

 

If you employ such a child table, where each record is defined by a year and a month, and use a properly defined and sorted self-join relationship, then for the duration of your database's lifetime this calculation is reduced to a simple look at the next (per sort order) non-empty related record. (Or use ExecuteSQL, if you have FM12 – which your profile doesn't tell.)

 

Just to make this clear: you could write a recursive script or formula to find the most recent non-empty field, regardless of the number of fields; but the effort necessary (e.g. define “previous month” in your field naming scheme) would be better spent in setting up said child table and some script(s) that create and populate those monthly summary records.

[enquirerfm]

However, this is only a one-off calculation. I am not doing this on an on-going basis. Although I described this for each month I am only set up a base calculation for a the period, for example, in 2004 and as such there is no need to re-calculate the months after this. Can you tell me why this formula above does not work? Thanks.