Control Property - Dialog Binding - NULL if blank

[MarionT]

I have a field (N) on a dialog

I have it set to NULL if blank
My SQL table allows it to be blank

When I submit, a 0 is being placed in the control (I can see it there) and a zero is being passed to my SQL table

Why is a zero being placed in my null field?

zero is not the same as null
I want null, not zero

[MarionT]

Re: Control Property - Dialog Binding - NULL if blank

Ok - I've done some more debugging on this issue.

character-based control: the option to pass a null value to my SQL database works.

numeric-based control: the option to pass a null value to my SQL database does not work; a 0 gets passed

This is unpleasant but not dreadful when the SQL field is just a plain old field.

BUT, when the SQL field is a foreign key, that is permitted within SQL to be null, and a 0 gets passed instead of null, then the whole query fails.

I've written to A5 asking for a work around.

Any suggestions from here?

I guess I need some way to say:
if Field1 = 0, make it null

[kingcarol]

Re: Control Property - Dialog Binding - NULL if blank

I have had some success with setting variables I'm using for arguments to a null value with the null_value() function.... varName = null_value()

I've used that in some insert queries that I write myself and it works. I wonder if you might be able to use this with the server side events of CanInsertRecord or CanUpdateRecord to override what is being submitted to the server? Seems like it would take some if statement to test if the value in the field is null first. I've tested for null values on character fields before by: If dataSubmitted.fieldName = "" then ..... but I don't know if that will work on a numerical field.

But maybe if Alpha is submitting a zero, maybe you could do something like the following (unless, of course, you want it to submit a zero if a zero is really the value in the field):
If data.Submitted.fieldName = 0 then
data.Submitted.fieldName = null_value()
end if

If you figure out how to test for the null value of a number, please post that. I'd like to know...

[MarionT]

Re: Control Property - Dialog Binding - NULL if blank

Hi Carol -

In the Dialog component, Events.ServerSide - I do not see these as options:
CanINsertRecord or CanUpdateRecord

I like this, but I'm not sure where to put it:

If data.Submitted.fieldName = 0 then
data.Submitted.fieldName = = null_value()
end if

seems like I should be able to do something within the validation properties, but, I'm still too new to know where, what, how.

I'll certainly report back when I find a solution.
I'm sure there must be a way.

[kingcarol]

Re: Control Property - Dialog Binding - NULL if blank

Oops, I didn't pay attention that you were using a dialog. Another oops... the double equals sign in that suggested code shouldn't be there.
I haven't been working with dialogs very much yet, but seems like I remember some of the videos describing how to get in between of what was being submitted... maybe someone else knows and maybe that null_value() function will work there.

[cptutrecht]

Re: Control Property - Dialog Binding - NULL if blank

What release are you on, is it still present in the new official release?

We reported this to A5 and got a message back from Selwyn that it would be cured in on of the next prereleases. But we havent installed the new official release either to check that...

[MarionT]

Re: Control Property - Dialog Binding - NULL if blank

@Carol -
I've watched the videos, and have things working - except for the passing of a null in the numeric control

@Ger -
Thank you sooo much for giving me the update - I figured I was not the only one running into this. For the moment, I'll assign a default value so control can find a linked record.

I'm using V11 Build 2414, Trial Version

[danh]

Re: Control Property - Dialog Binding - NULL if blank

Marion, you can use the Server-side dialogValidate or afterDialogValidate event in a dialog, but it's a little more tricky. If the user has not edited the field, you will have to add code to tell it to change the field. Here's how you do it.

dim ds as p = e.dataSubmitted
If (ds.fieldName = 0) then
ds.fieldName = null_value()
e.dirtyColumns = word_unique(e.dirtyColumns + crlf()+ "FIELDNAME")
end if

The "e.dirtyColumns" is the trick to getting it to "take". Also, notice the field name is UPPER case.

Not sure if ds.fieldName will contain a numeric or character value, but if need be, you can convert it to numeric by: val(ds.fieldName)

Hope this helps.

[MarionT]

Re: Control Property - Dialog Binding - NULL if blank

@Dan -

I like your signature line -

In the afterDialogValidate, should your code go before or after the line I already have?

I have:

ExecuteServerSideAction("Save Data::save")


I tried your code both before and after and I'm still getting a zero passed into the numeric field....


I have:

ExecuteServerSideAction("Save Data::save")

dim ds as p = e.dataSubmitted
IF (val(ds.ENTITYACCOUNTANTID) = 0) then
val(ds.ENTITYACCOUNTANTID) = null_Value()
e.dirtyColumns = word_unique(e.dirtyColumns + crlf()+"ENTITYACCOUNTANTID")

END IF

[MarionT]

Re: Control Property - Dialog Binding - NULL if blank

PS - A5 responded that this has been fixed by the latest release built (2466).

[danh]

Re: Control Property - Dialog Binding - NULL if blank

I think you need to change:

val(ds.ENTITYACCOUNTANTID) = null_Value()

to just:
ds.ENTITYACCOUNTANTID = null_Value()

the val() function will change a null value back to 0!

And, maybe try it in the dialogValidate event.

But, then, I'm not sure you can make a numeric var null??
I tried this in the interactive window:
dim x as n
?x
= 0
x = null_value()
?x
= 0

I still get zero!

[MarionT]

Re: Control Property - Dialog Binding - NULL if blank

I had tried it that way initially and it had not worked; that's why I added the val() around it

I had also tried it in the dialogValidate event (rather than the afterDialogValidate) and that had not worked either

But - since Ger has said and A5 has confirmed, A5 say this is fixed by release 2466