when I change the value of a text or date field to blank I want to update the database with NULL so I can query against the database for NULL records.
How do I set the value to NULL when the field is blank?
Try the following.
set in the database default value NULL
when updating a record
(1)Create the UPDATE
inside server connect choose to update the value only if input.value!=’’
also
(2) create an additional if step after the update (1) is completed.
if inputvalue==’’ then update the specific fleld with NULL
Think you ll be ok with that.
Thank you!
If you do not get it working send a screenshot of your database update step and we will advise
I never thought of adding a condition uner the update as @t11 suggested however if I enter the conditions as suggested I get errors. I’m thinking I can add the condition as suggested but use the post values?
If you have a form on your page with 10 inputs and 3 inputs are blank, and therefore should be sent to your database insert as NULL there are 2 ways to do this.
-
Send the blank input on form submit but replace an empty field with NULL by using the built in dynamic data formatters to say that if no data supplied replace with a default value of NULL, like this.
-
Second way is to make sure your database not only has those columns set to allow NULL values but also to DEFAULT to NULL if nothing provided, however sending nothing is not sending an empty string like
"", it literally has to not submit those fields at all, and this is done with adding a conditional parameter to the inserts input like this.
2 Likes
Hi @psweb, does it make any difference if you add both default("NULL") and a condition as follows? This probably doesn’t make any sense but I can’t get my head around it 
My data is set to default to null but the problem appears to be because there are mixed null and blank values in the data at this point. I don’t “seem” to be able to get a blank to set a null (at this point)
these are date fields so the database seems to be receiving a 00-00-0000 when no date is picked so for now I’m just filtering my results to include 00-00-0000 and NULL until I figure out how to make it send a NULL
Is there a default value set on that column in the database. The zero date is a special date from mysql and is not a valid date string. You can disable zero dates in mysql https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date.
Also the expression for default null should be {{$_POST.data_modified.default(null)}}. A null value, not a string.
5 Likes
Thanks Patrick, never knew about the null not being a string, but makes sense now that you say it, now to go look through 30 projects and fix them all, bbedit multi file search to the rescue or im going to be here all night