dynamically calculated field

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

Problem:
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

Angel

  • 724 Points 500 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
I suggest using 2 fields for each of the q fields.

e.g.
[Q1Expense]
and
[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], 
  [TotalBaselineExpense]/4)
(Edited)
Photo of Chuck Grigsby

Chuck Grigsby

  • 1,978 Points 1k 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}

_fid_FIELDID


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.
(Edited)
Photo of Angel

Angel

  • 724 Points 500 badge 2x thumb
Thank you guys, I ended up using the approach by Mark
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
ya, stay native whenever possible.  No need here to hijack the webpage and try to reprogram Quick Base.
(Edited)