Discussions

Expand all | Collapse all

IsNull doesn't work with the required fields

  • 1.  IsNull doesn't work with the required fields

    Posted 11 days ago

    Hi,

    I have a problem with the "IsNull" function and the date field. My date field "Assignment End Date (DD-MM-YYYY)" is required and of course it is visible on the form. I also have a formula - Rich Text field with formula (finally the formula is longer but I shortened it to check the IsNull function):

    var text assignmentEndDate = If(IsNull([Assignment End Date (DD-MM-YYYY)]),"Is NULL","Not NULL");
    
    $assignmentEndDate
    The field with the formula is visible on the form.  We have two case:

    1. The "Assignment End Date (DD-MM-YYYY)" field has a value, and the formula field text is "Not NULL" - Correct
    2. The "Assignment End Date (DD-MM-YYYY)" field is empty, then QB does not generate this field in the DOM, and instead only shows "???" -Not correct

    I was able to work around this problem on the form by removing the required property from the "xxx" field and selecting it in the form itself to make it mandatory. After this procedure, QB correctly calculates the formula. However, this is not a good solution because this field must be required in the entire system, not only on one form.

    How do I use the "IsNull" function on fields that have the Require property set?

    Best Regards
    Jacek

    ------------------------------
    Jacek Lewandowski
    ------------------------------


  • 2.  RE: IsNull doesn't work with the required fields

    Posted 11 days ago
    If the field is required at the field properties level, then how can it be empty?  Or is this just a one time cleanup of existing data?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: IsNull doesn't work with the required fields

    Posted 11 days ago

    On the form, I can delete data from the field, or still not complete it when adding a new record.



    ------------------------------
    Jacek Lewandowski
    ------------------------------



  • 4.  RE: IsNull doesn't work with the required fields

    Posted 11 days ago
    My point is that if you make the field required at the field level, then the form will prevent the record from being saved if the date is blank.  So you really do not need that rich text message field.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: IsNull doesn't work with the required fields

    Posted 11 days ago

    It's not answer for my question.

    As I wrote in the first post, this is just a fragment of the formula. I do not use this field to inform the user that he has an empty required field, because it is a basic QB functionality and there is no point in duplicating it.

    I use this calculated field to create JSON which is later used in js which is loaded into the form. Therefore, it needs that if the required field is not completed, the IsNull function will work and I will be able to write to JSON that this field is empty. At the moment, QB is generating an error and does not display the calculated field at all.



    ------------------------------
    Jacek Lewandowski
    ------------------------------



  • 6.  RE: IsNull doesn't work with the required fields

    Posted 11 days ago
    Strange,
    I did a test using this formula

    var text RequiredMessage = If(not IsNull([required date]), "OK", "Missing");
    $RequiredMessage

    and it said "Missing" when the required field for [required date] was blank.  So I cannot duplicate your issue.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: IsNull doesn't work with the required fields

    Posted 11 days ago
    This error occurs when you first fill in the date field and then delete the value from that field.

    ------------------------------
    Jacek Lewandowski
    ------------------------------



  • 8.  RE: IsNull doesn't work with the required fields

    Posted 11 days ago
    OK, right, I see that behaviour now too.  It is odd that the ??? result only occurs when you make it blank after filling it in.

    So sorry but after all that we are where we were.  I tried making a formula to detect the ??? but its not really a ??? result, its note like an @ERR result in excel which then contaminates formulas which refer to that cell​.

    Plan B could be to make a form rule to always fire (uncheck the checkbox) to copy the value of the date field to a non required date field that you use in the warning formula.  The mirror field will need to be on the form, even if in a hidden field.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 9.  RE: IsNull doesn't work with the required fields

    Posted 10 days ago

    Thank you for your answer.
    So this is another bug workaround. It allows you to keep the fields required at the level of its properties, but it forces adding "unnecessary" fields to the entire table.

    Do you know if and when this bug will be fixed in QB?



    ------------------------------
    Jacek Lewandowski
    ------------------------------



  • 10.  RE: IsNull doesn't work with the required fields

    Posted 10 days ago
    Edited by Mark Shnier (YQC) 10 days ago
    Realistically, I doubt that they will consider it a bug and if so, I doubt it would be a high priority.  So I suggest that you plan to have a work around or else make the field not required at the field level.

    An alternative to Required fields at the field level is to set up Custom Data Rules.  Just thought of that now.  I believe that will totally solve your issue by allowing you to make the field not be required but block the save in all modes (Grid edit, import, Automations etc ) if not provided.

    Here is the help for Custom Data Rules.
    https://help.quickbase.com/user-assistance/formulas_custom_data_rules.html

    I think the help article is a bit incoherent.

    They should lead by

    1. Enable Custom data rules on a table by the checkbox in Advanced Properties
    2. Make a formula which calculates a text error message to the user.
    3. The save will be blocked if the error message calculates to not being blank. 
    4. If not blank the message will be displayed and he save will  be aborted.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------