Discussions

Expand all | Collapse all

dynamically calculated field

  • 1.  dynamically calculated field

    Posted 07-19-2017 21:10
    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??


  • 2.  RE: dynamically calculated field

    Posted 07-19-2017 21:26
    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)


  • 3.  RE: dynamically calculated field

    Posted 07-20-2017 12:35
    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.


  • 4.  RE: dynamically calculated field

    Posted 07-20-2017 20:08
    Thank you guys, I ended up using the approach by Mark


  • 5.  RE: dynamically calculated field

    Posted 07-20-2017 20:21
    ya, stay native whenever possible.  No need here to hijack the webpage and try to reprogram Quick Base.