• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

Multiple operations within a form

I am creating a form with 13 fields.  2 of the fields are constants (field A and B), 4 of the fields need user input (fields C, D, E, J), and the remaining fields (fields F, G, H, I, K, L, and M )are formulas pulling the data from the 2 constant and 4 user inputted fields. I want to make it so the user enters the data for fields C, D, E, J, and by saving the form, the rest of the fields automaticlaly calculate.  To elaborate on the formula fields:

F=A * E
G=F * B
H=D + F
I=H + G
M=C * L

How do I do this?
1 Solution
If you want to make the fields auto calculate on saving ( or refresh ) then you have a couple of options..

1) write so lotus script code that does the calculations and store it in QuerySave Event of the form..

2 ) Use calculated values for the the other fields

Here is 2)

You will also obviusly need to setup "input validation" formulas for the fields C,D,E and F since the other fields use these values to calculate themselves...

so for C,D,E,F use the "INPUT VALIDATION" formulas like

@If(C="";@Failure("You must enter a value for C");@Success);

Now for your calculated fields make them "computed" with a value of :

Field F

@If(A!="" & E!="";A*E;0)

Field G

@If(F!="" & B!="";F*B;0)

Field H

@If(D!="" & F!="";D+F;0)

Field I

@If(H!="" & G!="";H+G;0)

Field K

@If(H!="" & L!="";1-(H/L);0)

Field L

@If(L!="" & J!="" ;I/(1-J);0)

Field M

@If(C!="" & L!="" ;C*L;0)
I have noticed that some of your formulas may cause an error such as I/1-J is infinite if J =1 so your may want to change the formula to

@If(L!="" & J!="" & J!=1 ;I/(1-J);0)

Also, notes computes fields top to bottom left to right , so you will need to store the fields that are dependent on other fields below those fields i.e on your form the order of your fields should be

F , G , H , I , L , K , M

This is because K uses the value of L so it needs to be calculated first

Hope this helps !!
kevshAuthor Commented:
Great, though when I save the first time, I get an error within field k, yet when i press save again, than field K correctly calculates.  I have the order of fields like you described above.  Can you help asto why I would need to press save twice for all of my fields to calc?


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now