Validate for sum within roster

Dear Survey Solutions Team,

I have the following structure:

Level 1 Parcelroster:
Q1: Area of parcel (Numeric)
Q2: Unit of area (Single select)
VariableA: Standarized Area of Parcel X (Q1*LookUpTable[(int)Q2)].factor)

Level 2 Plotroster
Q3: Area of plot (Numeric)
Q4: Unit of area (Single select)
Variable B: Standarized Area of Plot Y on Parcel X 

For Q4 I would like to use two validation conditions:

  1. Variable A>= VariableB. This however works only if I use the expression to create the variables as macros in the validation condition. However, as it works this way I am fine for now.

  2. VariableA>=Sum of all VariableB’s.
    Unfortunately I do not know how to get it working. Using variable or a macro with the expression: plotroster.Sum(x=>x.area_plots1) within Q4 leads to correct validations only sometimes. That means once I might change my selection in Q4 (e.g. from Hectare to Acre) it suddenly flags error even though everything is correct. Once I change my numeric answer in Q3 so it actually should flag an error, the error disappears. Other ways, such as boolean variables or other Linq expressions did not help.

However, if I use the "inverse"of the validation condition as an enabling condition in a subsequent question (VariableA<Sum of all VariableB’s) everything works just fine. I could use a static text with red font to indicate a problem to the enumerator but of course I would be more happy to use the Survey Solutions “Red Error Flag”. Also presenting the value of the variables in a static text show that the variables calculate the correct values but it is not validated correctly in Q4. I assume it has to do with that Q4 is part of the calculation?

I would be happy to receive some help on that matter. If you need more information I am happy to provide it to you.

Best,
Peter

Dear Peter,

the following expression can be used outside a roster to find a sum of area of all crops of a particular kind (e.g. cereals with codes 101-199) inside a roster:

S=ROSTER.Where(x=>x.cropcode>=101 && x.cropcode<=199).Sum(p=>p.area)

If you are using a nested data structure, you must correspondingly use nesting in the expression above, or generate intermediate variables.

What do you mean by "Other ways, such as boolean variables or other Linq expressions did not help. "??

Best, Sergiy Radyakin

Dear Sergiy,

thanks a lot for your answer.

For now I just need to validate the sum of areas of plots must NOT be larger than the parcel on which the plot is situated. Therefore, my simple expression:

(Q1*LookUpTable[(int)Q2)].factor)>= plotroster.Sum(x=>x.area_plots1)

does the trick and always computes the correct numbers. It also returns the correct validation IF i use it as an enabling condition in a SUBSEQUENT QUESTION/STATIC TEXT after Q4.

However, I would like to have a validation within Q4. Therefore, enumerators will be shown an error if the sum of plots is larger than the parcel itself. For now if they click the Unit of Area (Q4), sometimes the correct validation is returned, sometimes not. It appears to pretty random for the moment when it shows an error and when not. So I thought there might be a better validation condition which I just can’t think of at the moment :).

With other ways I tried, I meant for example:

Boolean: Variable outside the plotroster (within parcelroster) with plotroster.Sum(x=>x.area_plots1)<=(Q1*AreaConv[(int)Q2].factor) returns true/false. Then I just used this variable as an validation in VarX==true. Still does not work consistently.

I created a minimal example: minimal_example_sumplot
URL: https://solutions.worldbank.org/questionnaire/details/f6aef49679e24a9db2ec85984ac87d41/chapter/37e3d6dd8eca4ce08dad9de7f7750fa5/group/37e3d6dd8eca4ce08dad9de7f7750fa5

Thanks again for your help and best,
Peter

Peter, macros are substituted in at the compilation stage. So from the expressions you specified here in your message, your actual validation expression is in fact:

Q1LookUpTable[(int)Q2)].factor>=plotroster.Sum(x=>x.Q3LookUpTable[(int)Q4)].factor)

which means that when the validation is running the current value of Q4 is applied to all rows. Instead it should be: x.Q4. (it still compiles, since Q4 is in the current scope and is accessible, but you need Q4 of that other plot denoted by x).

This can explain why you were getting correct behavior sometimes and strange in other situations.

In this situation the variables have the advantage over macros of less computations. Once you enter the area and unit of the plot, Survey Solutions will calculate normalized area (say, in square meters) and re-use it in many expressions, e.g. for sums computations. With macros it has to re-evaluate the same over and over again.

Best, Sergiy Radyakin

Dear Sergiy,

I just found a solution to my problem. Very sorry for taking your time.

For all those for it might be of any use:

Following my example I do

Validation in Q4: $area_parcel>=$sumarea_plots2

$area_parcel= Q1LookUpTable[(int)Q2)].factor
$sumarea_plots2=plotroster.Sum(x=>x.$area_plot)
with
$area_plot=Q3
LookUpTable[(int)Q4)].factor

Therefore, what made the difference was not using variables but summing up a macro ($area_plot). Now the software returns always the correct validation. This was not the case when I used to sum up variables.

Sorry again and best regards,
Peter

Peter,

the function Sum ignores the NULL values in the values to add. So 2+3+null+1 is simply 6.
The issue may be related to the usage of the lookup tables (looking up a missing of Q4 AFAIK should raise an exception).

Peter and other users reading this topic may refer to the example questionnaire called “Public example User questions and common patterns” which has a section “Burgers” which demonstrates some common operations on what can be done in terms of checking and reporting in the rosters (totaling all items, some items, verifying the total is equal to declared value, verifying all items are entered,etc).

Best, Sergiy Radyakin

Dear Sergiy,

thanks a lot for following up on my “solution”.

I thought I tried with the tester all possible answers and it always worked right with my last proposal. But you are right, I must not forget the x.Q4, and yes this explains why sometimes it got correct and sometimes strange.

Thanks a lot for your support!

However, I would like to follow up on your solution:

Q1*LookUpTable[(int)Q2)].factor>=VARIABLEX

VARIABLEX: plotroster.Sum(x=>x.Q3*LookUpTable[(int)x.Q4)].factor)

With this as an variable, it returns in any case an error if I insert the area in my first plot and only validates correctly after finishing all my plots. I guess this relates to the fact that until not all plots are filled out the value is still “missing”.

I circumvent the problem by using the expression of the VARIABLEX again as an validation condition within Q4. But I need to add that it will only be checked at the end/last plot:

plotroster.All(y=>IsAnswered(y.Q3)) ?Q1AreaConv[(int)Q2].factor>=plotroster.Sum(x=>x.Q3AreaConv[(int)x.Q4].factor) : true

It works fine, but given that it worked “fine” before I wanted it to post here in the community and I am open for suggestions/warnings.

Thanks again and best,
Peter

gooday sergy, my question is way simpler , i need to calculate total members in roster

Use ROSTERNAME.Count() or refer to the trigger question if it is conveniently numeric.