Nintex Form recalculate a variable


Badge +6

Hi All,

I have form variable that use a LOOKUP calculation that collects all the "amount" from the list by filtering an "ID number". +  I use a SUM calculation to sum them up and get the total, so far so good.

My issue is that the reviewer should be able to modify the "amount", and the form variable should re-calculate the SUM with the modified amount.

I need to prompt the user if the amount is over the "Limit" immediately so he cant save the item until the amount is decreased. That's why I cant use a workflow, but need to do this in the form.

Can somebody help how to trigger the recalculation when the amount is changed?

Thanks


11 replies

Userlevel 5
Badge +14

can you post how have you set up your formula?

you have make it dependent on a amount control on your form to recalculate once amount changes.

so your formula should look like: SUM(lookup values) + AmountControl

Badge +6

Hi Marian,

Thank you for looking into this.

Actually very close what you say, I tried your version as well, the problem is how to exclude from the lookup the AmountControl?

(The list already contains all the amount so its included in the sum(lookup) ).

So now its: SUM([Lookup values])

If I do SUM([Lookup values]) + AmountControl than the AmountControl will be duplicated. Can I exclude it from the lookup?

Userlevel 5
Badge +14

what about:    SUM([Lookup values]) + 0*AmountControl

however, I do not understand how AmountControl's value could be in lookup values already. if you change it on from, it's not written yet in list. so lookup can get at best the previous value. if so, then you will have to set up the formula so that it adds up value of AmountCountrol (named control) and subtract current value of list field (item property)

Badge +6

All amounts are already loaded, the reviewer only change where he wants to, so that why the lookup include every line included the one open by the reviewer.

Its nearly working as this: SUM([Lookup values]) + 0*AmountControl(itemproperty) + AmountControl(namedcontrol)

The reviewer changes something in line item 1 --> Working

The reviewer changes something in line item 2 --> Working, the balance change is reflecting the line 1 change

... so on for other lines, everything working its in balance

The reviewer goes back to Line Item 1 (or other line already changed) --> Not good, Its out of balance with the difference of the original changed amount.   

Userlevel 5
Badge +14
The reviewer goes back to Line Item 1 (or other line already changed) --> Not good, Its out of balance with the difference of the original changed amount.   

sorry, I do not get what you mean with that and what you exactly do. can you you explain it in more details and/or post some example?

btw, shouldn't you multiply by zero both AmountControl(itemproperty) and AmountControl(namedcontrol)?

Badge +6

Ok, so I try to explain it without confusing you with the details .

I have extracted part of the form and translated it happy.png

1. Reviewer checks the item, at start everything is in balance.

2. Reviewer change the reviewer amount with 1000 and save it.

3. Reviewer goes to another line and the balance change is reflected correctly.

4. Now the reviewer goes back to the first line he changed and the balance is not correct, its doubled the -1000.

 

The reviewer amount sum is calculated: SUM([Lookup values]) + 0*AmountControl(itemproperty) + AmountControl(namedcontrol) 

If I add 0*AmountControl(namedcontrol): the balance will be -164900 HUF instead of -2000 HUF above.

Userlevel 5
Badge +14

I still have doubts since I do not know what's exactly saved to the list resp. what figures are included in  SUM([Lookup values]).

I have a feeling, the SUM is sum of reviewers amounts over the list.

if so, then in step 2 it decreased your reviewer SUM by a 1k because of difference in form fields.

then in step 4 you included already decreased value of reviewer amount in  SUM([Lookup values]) + another difference of 1k still comes from difference of form controls, hence total difference of 2k

Badge +6

Yes your logic is correct, that why I have it duplicated when going back to the same line.

But this is the question, how can I avoid the duplication? 

Userlevel 5
Badge +14

I would say it should be

SUM([Lookup values]) - ReviewerAmount(itemproperty) + ReviewerAmount(namedControl)

Badge +6

OK I managed to create the correct calculation that shows the correct balance in all scenarios I mentioned above.

(SUM([Lookup values]) + 0*ReviewerAmount(itemproperty) + ReviewerAmount(namedControl) + VarOverspend)

- (SUM([Lookup]) + ReviewerAmount(itemproperty) )

Your suggestion on 0*ReviewerAmount was very helpfull, and its the answer of my original question so I mark it correct. THANK YOU!

Is there a way I can learn more on syntax's like this?

Userlevel 5
Badge +14

glad you came to the working solution.

IMHO, expression '0*ReviewerAmount(itemproperty)' is pointless in this context. value of itemproperty do not change during form instance. so it doesn't influence formula result neither value-wise nor recalculation-wise.

I've originally meant to mutliply by zero named control value.

next I guess 'SUM([Lookup values]' and 'SUM([Lookup]' is just the same, so your formula is effectivelly just

ReviewerAmount(namedControl) - ReviewerAmount(itemproperty + VarOverspend

Reply