How do formula functions respond to capitalization? Is there a guide to this?

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I've looked before and never found a guide to how Quick Base executes formula functions regarding capitalization. I've been using QB for years and just let it do its thing. Today, I found at text test fail due to capitalization, that must have been working until recently.

In this situation it was a 'case' test. Here is a stripped down version:

Case([Item - Canada CSA Status], 
...
"with modification","Model requires modification", ...)

My source field is [Item - Canada CSA Status] = "With modification". This was failing and rolling over to the next case-test, which unfortunately give the wrong information ($5K wrong!). So I had to change this case function to be explicit in its capitalization, with a capital W. 
I had assumed that lowercase text would match to any capitalization, but now my confidence is shaken. So, when does capitalization matter in formula tests for contains, equals, begins, etc?

Is there a guide to this or rule-of-thumb that I should have? 
Photo of ESPEC Data

ESPEC Data

  • 80 Points 75 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,352 Points 20k badge 2x thumb
You have to canonicalize your comparison to achieve case insensitivity:

Case(Lower([field]),
  "with modification", "Model requires modification"
)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
I don't know of an all encompassing guide, but I believe when the test is for equals, such as
If([Status]="shipped",

.. then the test is case specific.

But

if(Contains([Status], "shipped"), ...

is not case sensitive.

I know that report filters are not case sensitive, nor are summary field filters.

Since case (  , ) is essentially a test on Equals, then it is case sensitive.

So my rule of thumb is that for formulas testing equals or Case, then it is case sensitive, else not case sensitive in other situations.

There has been times where is Used UPPER to ensure a match.

such as if(Upper([Status]) = "SHIPPED", true...

so I convert my data to upper case and then test against an Upper Case string.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,352 Points 20k badge 2x thumb
>so I convert my data to upper case

lower case is better as you don't have to hit the shift key.

Trivia: Believe it or not this is why most JavaScript programmer prefer single quote over double quotes - to save a keystoke. This is the one commonplace style convention I have not adopted when writing JavaScript because of a ton of Tcl code I wrote earlier in my career where single and double quotes effected interpolation.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
OK, that tip should push out my onset of carpel tunnel syndrome by an hour or so sometime in the future and increase my billing efficiency.  
Photo of ESPEC Data

ESPEC Data

  • 80 Points 75 badge 2x thumb
Well, I'm honored to have Mark And Dan sniping at each other on my question. I guess my constant work with reports sets my expectations for formula functions. I probably use 'contains' or 'begins' most of the time, so that probably reduces the likelihood of this problem, as well. 

And as to keystrokes, I hate using lower or upper functions altogether for that exact reason. I hate any extra parentheses in general, besides the need for four key strokes, they are the reason most of my formulas don't work the first time.

Thanks for your help, guys!