Discussions

Expand all | Collapse all

If formula syntax for if field is blank

  • 1.  If formula syntax for if field is blank

    Posted 21 days ago
    Hi

    I have a formula rich text field to help indicate if a number added in the "Measured" field next to it is within the desired criteria.  It looks like this:



    This is the example I found in the QB formula support that I used to create my formula:

    If([Total]>=100 and [Total]<250, 25,
    [Total]>=250 and [Total]<1000, 50,
    [Total]>=1000, 100, 0)


    If([Moisture Content]>=[Abrasive - Moisture threshold of Current Incoming inspection], "<div style=\"color:white; background-color:#FF0000;\"> The sample is out of spec!</div>",
    [Moisture Content]<[Abrasive - Moisture threshold of Current Incoming inspection], "Sample is within the acceptable range of <" &[Abrasive - Moisture threshold of Current Incoming inspection]*100 &"%",
    "The acceptable range is <" &[Abrasive - Moisture threshold of Current Incoming inspection]*100 &"%")

    The problem is that when the field is blank, instead of getting what I want, I get this:
    It doesn't seem to recognize that the field is blank.  I realize in the QB example that there there could be other numbers (i.e. anything <100) in the field that would trigger the "0" whereas my formula covers all real numbers and is only looking for the lack of data. 

    Is there some way to instead write for if the field is = blank?  I tried using "ISNULL" in my formula, but either I didn't do it correctly or it just didn't work.  How can I make this a correct statement: If[field]="blank"

    The point is I want the people entering the data to first see what the threshold is supposed to be.  However, I don't really want them to have to think about whether or not it is within the spec, it's designed to tell them if it is.  That's why there's the warning if it isn't.  (Note: regardless of whether the data is out of spec, it needs to be recorded.  It's part of QA documentation to determine if material needs to be rejected.)

    A secondary issue is that I couldn't seem to figure out how the correct syntax for "The sample is out of spec!" so that it would also contain the desired criteria:
    "The sample is out of spec! It should be < " &[Abrasive - Moisture threshold of Current Incoming inspection]*100 &"%"

    For some reason it didn't sit well with the color coding, but I'm not sure why.

    Any suggestions would be much appreciated. If I should be going about this a different way, do let me know. It did cross my mind to try form rules for warnings instead.  The challenge is the moisture specifications come from a different table that is the "grandchild" to this table.

    ------------------------------
    Kim Gardner
    ------------------------------


  • 2.  RE: If formula syntax for if field is blank

    Posted 21 days ago
    Can you try changing the field properties for the [Measured] field to deselect (uncheck) to treat blank as zero.

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



  • 3.  RE: If formula syntax for if field is blank

    Posted 21 days ago
    Thanks for tip! So many little things to remember. I know I've probably noticed that option in the field settings before.  It works fine now.

    Any suggestions for my secondary question regarding the syntax for my warning message to include both a color and pull the data from the other table?

    ------------------------------
    Kim Gardner
    ------------------------------



  • 4.  RE: If formula syntax for if field is blank

    Posted 21 days ago
    Edited by Mark Shnier (YQC) 21 days ago
    Try this in a new formula to test.

    If([Moisture Content]>=[Abrasive - Moisture threshold of Current Incoming inspection],
       "<div style=\"color:white; background-color:#FF0000;\"> The sample is out of spec! It should be < "
     & ToText([Abrasive - Moisture threshold of Current Incoming inspection]*100) & "%" & "</div>",

    [Moisture Content]<[Abrasive - Moisture threshold of Current Incoming inspection],
      "Sample is within the acceptable range of <" &[Abrasive - Moisture threshold of Current Incoming inspection]*100 &"%",

    "The acceptable range is <" &[Abrasive - Moisture threshold of Current Incoming inspection]*100 &"%")


    ..... as for the null issue, I believe that Excel always treats a blank cell as a zero if you are doing mathematical operations but in Quickbase you get to choose whether a blank in a data entry numeric field means zero or a blank means null. 


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



  • 5.  RE: If formula syntax for if field is blank

    Posted 20 days ago
    Yes! Thank you! It works.  I really appreciate the assistance.

    ------------------------------
    Kim Gardner
    ------------------------------