Odd: Text Field vs Date Field - different result

Panorama X
1

Hello and Happy Saturday,

I’ve run into a weird issue that I believe comes down to my not understanding how PX sees Text Fields vs Date Fields (photo below). I’m using this procedure called UpdateTestPitch:

Field PitchStatus
Formulafill 
    ?(emptycell(Pitch_ID) and length(Pitch_Oxy)>0,"Pitch to ID",
        ?(emptycell(Pitch_Oxy) and length(Pitch_ID)>0,"Pitch to Oxygen",
            ?(emptycell(Pitch_ID) and emptycell(Pitch_Oxy),"Pitch to Both","Pitched")))

And I’m running:

call UpdateTestPitch

from the code panes of Pitch_Oxy and Pitch_ID.

If I leave the fields set to Text, everything works as expected.
If I switch the fields to Date, it says “Pitch to ID” every place it should say “Pitch to Both” -

TextField-vs-DateField
TextField-vs-DateField.jpg720×480 93.1 KB

Right now, this is a brand new empty database I’m trying out, since it seems I often “break things” along the way. The only thing in it are these three fields, the few records I’ve created, and the code above. Thanks for any thoughts, and have a great rest of your weekend!

Best,
Biagio

2

I think you have turned up a minor gotcha regarding the length( function. In an empty date cell the length( function returns 1 instead of 0. Apparently the empty date cell is interpreted at having a value of 0 so it has a length( of 1 while a populated date cell would have 7 digits and a length( of 7.

Bottom line is to change the length( functions to emptycell( functions like you are using in most other places.

1 Like
3

Thanks, @gary. Looks like that was it. I got it working using not( in conjunction with emptcyell(

Code below in case it’s helpful to anyone:

Field PitchStatus
Formulafill 
    ?(emptycell(Pitch_ID) and not(emptycell(Pitch_Oxy)),"Pitch to ID",
        ?(emptycell(Pitch_Oxy) and not(emptycell(Pitch_ID)),"Pitch to Oxygen",
            ?(emptycell(Pitch_ID) and emptycell(Pitch_Oxy),"Pitch to Both","Pitched")))

Thanks for helping me sort that out!
Best,
Biagio

4

If you want to determine the actual amount of space used by data in a field, you can use the sizeof( function. For a text field the length( and sizeof( functions return the same value, but for a numeric or date field, there is a difference between zero and a completely empty field. (In fact the length( function is not designed for use with non-text values at all – if you look at the documentation you’ll notice that the parameter is supposed to be a text value. If you give it a numeric or date value it will convert it to text and then count the length of the text. The numeric value of an empty numeric cell is 0, which is one character long, the digit zero.)

The sizeof( function will return zero for a completely empty field. In fact, internally the emptycell( function is simply implemented as

sizeof(thefield)=0

So in the original code,

length(Pitch_Oxy)>0

could be replaced with

sizeof("Pitch_Oxy")>0

Note that the sizeof( function requires the field name to be quoted.

Of course using not(emptycell( will work also, I would just leave your code that way, but I wanted to elaborate on the details.

1 Like
5

Thanks @admin! Pretty amazing how deep the program goes - feel like I’ll be learning stuff for years to come. Switching over to it has been great, and I’m able to do so much more than my previous attempts at db creation with other software. Seems like there’s nothing that can’t be done.

Thanks again for all,
Biagio