Add an IfNotBlank() formula

Suggestion Box
1

There is no IfNotBlank() formula. Which is a problem because IfBlank().Not() does not have the same functionality as IfBlank().

X.IfBlank(value) will return value if X is blank. X.IfBlank().Not(value) is just an error.

1 Like
2

Hey @Connor_McCormick are you familiar with IsBlank()'s brother, IsNotBlank()? Pair this with if() :: if(isblank(),true,false) or if(isnotblank(),true,false)

Does that get you there?

3

Yeah of course, but the benefit of IsBlank is you can pop it onto the end of a long complex formula in order to override the normal behavior if the result happens to come back blank. I want that but the inverse.

E.g.

If(
    MyTable.Filter(Quantity > 3).Components.isNotBlank(),  
    MyTable.Filter(Quantity > 3).Components, 
    thisRow
)

is much uglier than

MyTable.Filter(Quantity > 3).Components.IfNotBlank(thisRow)
4

I believe your “ugly” formula there is actually equivalent to MyTable.Filter(Quantity > 3).Components.IfBlank(thisRow) – no need for a IfNotBlank() formula.

(As an aside for future readers, WithName() can also help with formulas that repeat elements like that.)

Assuming the ugly one was a typo, I would re-write your “pretty” version like this:

If(MyTable.Filter(Quantity > 3).Components.IsBlank(),
   "",
   thisRow
)

Of course this is longer than your formula, but I think it’s actually more clear to understand what’s going on.

More generally, IfNotBlank() would be a strange formula because A.IfNotBlank(B) would be the equivalent of If(A.IsBlank(), "", B) which is not really the opposite of A.IfBlank(B) and I’m not sure that that is a common enough pattern to warrant it’s own formula to save just a few keystrokes.

Do you agree? Or can you think of other examples where IfNotBlank() would be better (prettier or clearer) than this kind of a re-write?

1 Like
5

Thanks so much for your response!

I’ll keep an eye out for these scenarios. What keeps happening is I find a location where I want to reach for IfNotBlank, but then I don’t post it right away. So when I do post an example it’s not really the one that made me want to reach for IfNotBlank, it’s one I came up with in the context of trying to demonstrate the usage—but perhaps not the need for—IfNotBlank. Nonetheless, I think you could be right here, IfNotBlank might not be a common enough pattern to merit a new formula, plus, as you note,

is quite readable. Whereas A.IfNotBlank(B) is not quite so.

As you point out, the need for this is obsoleted by WithName. This is pretty good:

WithName(MyTable.Filter(Quantity > 3).Components, moreThanThreeComponents,
  If(moreThanThreeComponents.isBlank(), thisRow, moreThanThreeComponents)
)
6

Just found myself reaching for it again. Here’s the scenario:

image
image1776Ă—1010 57.4 KB

I have a column called Compared With that doesn’t require an input.

I get an error on the bottom two cells because there is no value for Compared With. The formula that will fix this is:

SwitchIf(thisRow.[Compare With].IsNotBlank(),  
  thisRow.[Compare With].[Included Budget Items].Filter(thisRow.[Included Budget Items].Contains(CurrentValue).Not()),
""
)

But I would prefer to write:

thisRow.[Compare With].IfNotBlank(thisRow.[Compare With].[Included Budget Items].Filter(thisRow.[Included Budget Items].Contains(CurrentValue).Not()))

@Ryan_Martens2 what do you think? What am I missing?

7

Hi Connor, it seems to me the pattern you want to compress into one formula is:

If(IsBlank(A), "", B) ⇒ A.IfNotBlank(B)

There are two reasons I don’t think this is a good idea:

  1. IfBlank is conceptually quite different from IfNotBlank. In A.IfBlank(B), A is the value of interest so to speak, and B is like an alternative default. Ie, "I want the value A, but if it doesn’t exist, then just use B instead. A.IfNotBlank(B) on the other hand is a different beast. Now, the only way I get an output of A is when it is one single boring value of Blank. B is now the value of interest and it just gets forced blank when A.IsBlank().
  2. There is a hidden third argument. When I read A.IfNotBlank(B), then I know that “If A is not blank, then B is the output.” However, it’s not immediately clear what the output should be if A is blank. Of course in some cases you may argue it really should be just Blank but I bet there are other circumstances where you may prefer a default value more like 0 or false… or any arbitrary value for that matter.

So in my mind, it is better to write out the full and explicit logic, and tweak it as you may:

If(IsBlank(A),
  C,  // where C is `Blank` or 0 or anything else
  B
)

Anyway, my arguments are kinda feeling based, so feel free to feel differently :smiley:

As an aside, in the particular example you wrote, I feel your pain about this error. I hope we get a solution to your other suggestion.

2 Likes