Discussions

Expand all | Collapse all

Formula fields not calculating

  • 1.  Formula fields not calculating

    Posted 01-28-2020 08:10
    Hey,

    I've working on a formula rich text field which displays some warning text depending on if certain fields are null.

    when i have this field in a form, all my other calculations seem to fail (including this one) unless all conditions are true. Rather each calculated field without a True value is left with a ??? value.

    Anyone know how to fix? QB support doesnt seem to know how.

    ------------------------------
    GMacri
    ------------------------------


  • 2.  RE: Formula fields not calculating

    Posted 01-28-2020 08:20

    Hello GMacri,

    It is hard to tell with the information provided, it may be easier to help you troubleshoot if we had more a description of what the formulas are doing and perhaps a copy of the formula, if possible, and the resulting error/values.  However, if they are numeric calculations, a common problem would be that the check box "Treat blank values as "0" in calculations" in the field properties not being checked would cause calculations to fail.  Beyond that, it is hard to say without more information.



    ------------------------------
    Brandon Drake
    JHI Group
    Monroeville OH
    ------------------------------



  • 3.  RE: Formula fields not calculating

    Posted 01-28-2020 08:28

    var text Warnings = List("<br>",

    If([Data Element 1]<>"","","Data Element 1"),
    If([Data Element 2]<>"","","Data Element 2")

    If($Warnings <>"", "<span style=\"color:red;font-size:110%; font-weight:bold\">Missing Information:<br>" & $Warnings)

    This is the larger formula, I am not using any checkbox fields for support, only binary numeric fields (aka only values are 0 or 1)

    ------------------------------
    GMacri
    ------------------------------



  • 4.  RE: Formula fields not calculating

    Posted 01-28-2020 08:49
    Hi GMacri, 

    Is this the exact formula that you are using in the Rich text field?  If it is, it seems as though you are missing some syntax.  I think you would need to add the two highlighted characters to show the end of the List and to end the variable declaration.  This should clear up any errors in the formula, so long as your Data Element fields are of a type that can be compared to "blank" and does not require the isNull() function to be used.

    var text Warnings = List("<br>",

    If([Data Element 1]<>"","","Data Element 1"),
    If([Data Element 2]<>"","","Data Element 2"));

    If($Warnings <>"", "<span style=\"color:red;font-size:110%; font-weight:bold\">Missing Information:<br>" & $Warnings)

    If there are not any errors in the function that you are using, what is the result of this field when used in the live application? This formula seems to work as expected in my test.

    ------------------------------
    Brandon Drake
    JHI Group
    Monroeville OH
    ------------------------------



  • 5.  RE: Formula fields not calculating

    Posted 01-28-2020 08:54
    I was missing some syntax in the example but not in the app. This formula works in different apps, just not this one. I suspected IOL might of been the issue but i've since removed those fields from my forms and its still shooting errors

    ------------------------------
    GMacri
    ------------------------------



  • 6.  RE: Formula fields not calculating

    Posted 01-28-2020 09:08
    At what point are you getting these errors? Is it when you try to save the formula, view a record, save a record.?.?

    ------------------------------
    Brandon Drake
    JHI Group
    Monroeville OH
    ------------------------------



  • 7.  RE: Formula fields not calculating

    Posted 01-28-2020 09:50

    Here is what the formula does in my test.  Is this supposed to be the final result??  Or should the Data Element fields contain that text as well?




    ------------------------------
    Brandon Drake
    JHI Group
    Monroeville OH
    ------------------------------