Load lookup rules instead of calculating formula

asked on May 4, 2017

Good afternoon,

Let's say that I have 3 boxes; boxA, boxB, and boxC. In a formula I have boxB=boxA+1 and boxC=boxB+1.

If boxA = 1, then boxB=2 and boxC=3.

If I change boxB to = 3, then boxC=4.

When I submit the form, in my database all is correct. boxA=1, boxB=3, boxC=4.

When I load the form, the boxes show boxA=1, boxB=2, boxC=3; which is not the same as in the database, even though I'm loading the boxes via lookup rule.

Took me a while to figure out that the formula predominates the lookup rule. I'm looking for a way to overcome this so that the boxes retain the database/table values, and only changes when I change I box?

In other words, don't trigger the formula, unless I change one of the boxes.

Any ideas or suggestions?

0 0

replied on May 5, 2017

Sorry, I didn't get what is the issue actually. "When I load the form, the boxes show boxA=1, boxB=2, boxC=3; which is not the same as in the database, even though I'm loading the boxes via lookup rule.", do you mean when load the form in the next step? How do you configure the lookup rules for the three fields? The general rule for field's value is :Carried over value (variable's current value when there's an existing submission of the variable) > Lookup value > Calculated value > Default value.

0 0

replied on May 5, 2017

Xiuhong, thanks for the response.

So, let's say for example that originally boxA = 1, and boxB has a formula of =boxA+1.

Now when the form loads. boxA=1, and boxB=2

In my database I have a table with two columns. colA and colB.

Let's say that colA=1 and colB=2

Then I decide to plug in Look Up rules for both boxes. I tell it to:

fill boxA with data source value colA
fill boxB with data source value colB

Then I go to the form and sure enough colA=1 and colB=2.

Then I go into the data base table and change colB=7

Then I go to the form and colA=1, but colB=2. Then I realize that the formula plugged in is overriding the lookup rule; since I have colB to use formula =colA+1

I hope I made more sense with the two examples. Nevertheless, , since I know I might be giving it a long shot I thought in a different approach. Simply using identical boxes, one hidden with the formulas and another visible with the lookup rules. That way, the person viewing the form can see the data as it is in the database, and if they change anything in one of the fields, then it triggers the formula in the back (hidden fields) and then it replicates to the visible fields.

I just didn't want to mess around with duplicate hidden fields, but that's where I stand at this point. If there is a better way please share any ideas or suggestions.

Thanks again,

Raul Gonzalez

0 0

replied on May 7, 2017

If you have both formula and lookup rule configured for a field, the value returned by lookup rule should have high priority. But as you has lookup rule on the reference field used in the formula as well(in you case is colA), then if the lookup result for colA comes after lookup result for colB, then the formula colB will be triggered after the lookup result for colB has populated. Do you use separate lookup rules to fill colA and colB? If so, you can try change the order(the number appear in front of the rules) to make sure the lookup rule for colA has smaller number so that that lookup rule will be executed first.

0 0

replied on May 8, 2017

Thanks Xiuhong,

Although it might have not been the ideal solution, I ended up creating a duplicated set of field for each field. So for simplicity purpose I ended up with:

ColA with no formula (HIDDEN)

ColA_2 loaded from lookup rule (with the value of 1 let's pretend)

ColB with formula =ColA+1 (HIDDEN)

ColB_2 loaded from lookup rule (with value of 7 let's pretend)

When ColB_2 changed, then ColB = ColB_2 (using jQuery)

This way people always see ColB_2 = 7 or whaterver new number it has from the lookup rule, otherwise ColB would have always shown ColB=2

0 0

Question

Question

Load lookup rules instead of calculating formula

Replies

Sign in to reply to this post.