If trim with text and number fields help

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

Hi All,


I hope hoping you can help me. I am using the Trim command in an IF statement to see if certain fields have information filled in, I have used this before but only for text fields, now I have a mixture of URL, Email and Number fields, how can I have all of these in one statement?


This is the first command I use (which works as they are both text fields)

If(
Trim([Auditor])<>""and
Trim([Web])<>"",
1,0)

But now I have different types of fields I need to include these in the above.

Thanks,
Photo of Andrew

Andrew

  • 0 Points

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 67,670 Points 50k badge 2x thumb
I have not tested them all but likely the email and URL will work with the Trim function.   Post back if they don't.

The numeric fields are different though.


If you have the field properties on the numeric field set to NOT treat blank as zero, then you test for empty like




IF(IsNull([my number field]),  .....

but if you have that set to treat blank as zero, then you need to test

IF([my number field] <>0, .....

It's best to set the numeric field not treat blank as zero if you need to test for empty as zero may be a valid response and different from empty.  Like on a scale of 0-5 how much do you love cats.  For that question, zero would be a perfectly acceptable response - for me anyways.
Photo of Andrew

Andrew

  • 0 Points
Hi,

Thanks for the advice, I have tried using ISNULL on the fields but for some reason the field just keeps on returning 0 when I have now put something in every field so should return 1?

I have posted the full formula below, can you notice anything I am doing wrong with it?

The email address field is Email type, Job Title is text and GP field is numeric.

If(
IsNull([Email Address])and
IsNull([Job Title])and
IsNull([Estimated Yearly GP]),
1,0)


Thanks,
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,670 Points 50k badge 2x thumb
You may not use IsNull on a text field. It is documented that is it not meant to be used on text fields.false

For text fields you need to use

IF(
Trim([Email Address]) =""
and Trim([Job Title])=""
and Trim([Estimated Yearly GP]),1,0)