dynamically calculated field

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Struggling with forms and Dynamic Form Rules.

I have one field called as 'TotalBaselineExpense'

I have 4 fields related to this field, they are namely: Q1Expense, Q2Expense, Q3Expense, Q4Expense (for simplicity lets call them q1,q2,q3,q4).

On the form, as soon as a value is entered in TotalBaselineExpense, the value of q1=totalExpense/4. Similarly, q2,q3,and q4 should also become totalBaselineExpense/4.

Also, if the value of TotalBaselineExpense changes, the value of q1,q2,q3,q4 need to change accordingly.

In addition to this, I want the user to be able to edit q1,q2,q3,q4. and their total may not be equal to totalBaselineExpense. (hence, I cannot make them a formula fields)

I am struggling to achieve this.. Any help is appreciated??
Photo of Angel


  • 784 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
I suggest using 2 fields for each of the q fields.

[Q1Expense] override.

Set the override NOT to treat blank as zero

Then the formula for 
[Q1Expense] will be

IF(not IsNull([Q1Expense Override]),[Q1Expense Override], 
Photo of Chuck Grigsby

Chuck Grigsby

  • 2,100 Points 2k badge 2x thumb
If you have IOL and can use code then you can just use text fields for Q1-4, do 
if(_fid_2.value == ""){_fid_2.value = _fid_1.value / 4}


Basically if nothing is in the field then it will do your math. But then they can type into it and change it like a normal text field and the next time its in edit mode itll see that a value is there and not perform the math again and leave what value is there.
Photo of Angel


  • 784 Points 500 badge 2x thumb
Thank you guys, I ended up using the approach by Mark
ya, stay native whenever possible.  No need here to hijack the webpage and try to reprogram Quick Base.