In your case you can also insert null as a default value, as an empty string is not allowed. Example:
{{$_POST.est_first_pay_date.default(null)}}
1 Like
OK yes - thank you, this is definitely the issue. The date picker is sending an empty string and the database is not accepting that as blank / null.
Is there a way in Wappler I can convert the empty string to a null value, or change the DB setting? Or if I use the condition column in the insert query, can I say to only insert that field if it is not an empty string?
It sounds like it’s not possible and I need to use workbench or similar to change the settings in the database directly.
Sorry didn’t see this - I’ll give this a try
Teodor gave us the problem explanation…
And a solution
Thank you neighbor!!
1 Like
Unfortunately I don’t think I can use this because the field is part of a subtable (so following this method: Inserting Data in Main and Sub Table using App Connect Form Repeat - Docs / Server-side Components - Wappler Community
Unless there’s somewhere else I can insert it? As it stands, the $Post is the array and the names match the database fields.
Otherwise, I guess I can build the repeat manually
In case others have problems with empty date pickers, I tested writing directly to subtable using this and it worked perfectly.
As far as I can see, I can’t apply it to inserting into a main table and sub-table via a form-repeat though as I can’t edit the $_Post for the fields in the subtable. However, it should work if I manually create a repeat in the server action instead of using the Wappler inbuild shortcut.
Thanks again to you both
Try using the condition option here instead {{$_POST.est_first_pay_date != ''}}
I tried but it didn’t work (still tried to insert the empty string):
Not sure if it’s supposed to be referencing the formrepeat? If I select it in the picker I get this, but I guess I would need to correct [0] for some sort of $index.
$_POST.formRepeatExposures[0].est_first_pay_date!=''
In any case - I can easily make a repeat in the server action and that will definitely work.
Ok i understand the problem, we should improve the sub table insert process and allow some data formatting to be applied @patrick
Meanwhile you can connect to your db with some third party tool like mysql workbench and set the sql_mode to ALLOW_INVALID_DATES
Just to close this off and for anyone reading in future:
I found the best solution was to use a repeat in the server action (to insert to the subtable directly) and not use the main table/ subtable insert process. Reasons being:
- I could easily use .default(null) to solve the problem
- Changing mysql modes is complicated with a Docker database as it resets to default settings on every restart without manipulating config files
- You have more control over the insert process in the subtable e.g. combining data from the form with other sources that perhaps shouldn’t be in the form. For example, userid from the security provider and any associated data from the db that a bad actor could potentially manipulate in the front end
Many many thanks again for the super fast responses and help with this.
2 Likes
I ran into this today as well...app connect form repeat with a date picker that is optional, MySQL trying to insert the empty date as a string and throwing an "incorrect date value" error. My approach was to use a custom query and convert the empty string to a null. Seems to be working.
For an empty input field you could use the default formatter.
{{_['date-picker'].default(null)}}
I think there was an update in Wappler as more recently the problem I've found is that the date-picker is sending 'null' as a string. So I've been using a combination of .default(null) and the condition that the value != 'null'.
Does your example become the expression in the query? Sorry, I just want to make sure I understand how/where it would be used.
I tried your suggestion. The issue I'm seeing with this not working, is that an empty string (no date provided on the client-side) does not trigger the .default(null) formatter.
For the moment 
this appears to be working when the date picker is optional on the client-side...
The combination of the .default(null) formatter and the empty string condition together. I'm using MySQL with the InnoDB engine if that matters to anyone.
I tried different data types on the client-side for the date picker, hoping that date picker would stop passing an empty string. The date picker has to be of type text otherwise any dates returned from a server connect "select" query does not display dates in the date picker. The date picker will default to showing the string 'mm-dd-yyyy'.
Crossing my fingers I have crossed this bridge and will not have to look back.
The way inputs work in html is that they send the value as a string to the server, if the input is empty or not set it will send an empty string. The "null" string being mentioned was a bug in the datepicker on the client, this should have been fixed. On the server the default formatter should set the default value if the input was an empty string.
What server language (NodeJS/PHP) do you use?
I'm using Node and Wappler 6.8.0. The date pickers send 'null' as a string for me when empty
I just add a default formatter to my Server Action. You have to remove the single quotes around null manually.
{{$_POST.inputEditServiceEndDate.default(null)}}
1 Like