Formula IF() with Null Values in text field

October 6, 2011 12:44PM

I have a calculated field, and worked fine until I installed last version: 2.6 787(rc)

Steps to reproduce:

Create on new iok with 3 text fields: (Original, ChangedByUser, Formula)

Change field Formula and define this formula:
=IF(ChangedByUser="", Original, ChangedByUser)

If I write some text in the Original column, it should be shown in the Formula Field.

This worked fine until I installed yesterday last version.

Now, the condition (ChangedByUser="") ==> Is always False!!!

I think there are a little bit problem with Null/Empty values in a text field.

Because I didn't find a 'function' like IsNull(Value), i had to change the formula to:

=IF(("" & ChangedByUser)="", Original, ChangedByUser)

And then, it works fine... but I need to change a lot of formulas like this!

I think should be a 'function' IsNull(value) or IsEmpty(value), to avoid this problem.

Regards

steve · October 6, 2011 12:56PM

I'm not sure why the ChangedByUser="" would have ever worked. We'll investigate, and see if we can support this.

You can simply write:
ChangedByUser=null

(no quotes).

steve · October 7, 2011 2:15AM

When using the test ""=null, old and new builds of 2.6 evaluate to false. I've tested the latest build, and 2.6 b666 and 2.6 b280.

It's not correct to use "" for null, because "" is a zero-length text value rather than null, absence of any value of any data type ([Some number field]="" is clearly wrong).

(In data cells in Omniscope you can't have zero-length text values, but it's conceivable you might inside formulae.)

October 7, 2011 6:47AM

Thanks steve
did not know the possibility of using =null, I was looking it in functions :-) From now I will use =null.

But (i dont know why), I have iok files that I used IF(=""...), and it worked well, and then, when I install last version, it fails.

As you say, its possible the changes from null to zero-length text value. because the .iok are loaded, modified by user and saved, and reload again, and is possible it changes in this process.

Regards

steve · October 7, 2011 6:58AM

It is never possible to have zero length text values in column data. Even if the user edits the text. But it might be possible to have zero-length text values inside formulae, e.g. using MID function.

If you can find an IOK file and a version of Omniscope which behaves differently, please let me know which version and send the file.

Welcome!

Categories

Ideas Parade

Tagged