SearchBuilder providing between option on some date fields but not others
SearchBuilder providing between option on some date fields but not others
So this one is boggling me. I have two date fields in MySql. Birthdate and firstContact. Both formatted as 'Date' exactly the same.
in my serverscript, they are referenced as such:
Field::inst( 'abo.Contributors.Birthday' )
->searchBuilderOptions( SearchBuilderOptions::inst()),
and
Field::inst( 'abo.Contributors.firstContact' )
->searchBuilderOptions( SearchBuilderOptions::inst()),
javascript
{ data: "abo.Contributors.Birthday", visible: false, searchable: false },
and
{ data: "abo.Contributors.firstContact", visible: false},
Yet searchBuilder won't allow me to select between on Birthday, like it will on firstContact:
I would have expected I might need to give searchBuilder a trigger, but it's working correct for one and not the other.
BTW, I also removed the searchable trigger on Birthday, and that did nothing either.
Thanks.
This question has an accepted answers - jump to answer
Answers
Do you mean you removed
searchable: false
?It sounds like Datatables type detection is not detecting the columns as a date column.
Do you have other data besides dates in that column?
Are you doing anything like either of these examples?
https://datatables.net/extensions/searchbuilder/examples/initialisation/date-fmt.html
https://datatables.net/extensions/searchbuilder/examples/initialisation/date-fmt-luxon.html
In order to debug we will need to see the actual data. Post an example of a row of data from the JSON response using the browser's network inspector tool. Also post your full Datatables config. Better is a link to your page or test case replicating the issue so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case
Kevin
Thanks, but this is much simpler than that.
Here is the table structure
So to your answer, no there is nothing other than a date coming through.
There isn't anything to debug on my side, believe me I tried.
as far as my JSON response, comes back fine and can even be edited in an editor form with type: datetime
birthday and firstContact comes back as the follwing example, I checked all 606 instances and they were clean. Some entries are '0000-00-00' but that is true for both fields, so that's not the problem.
That should be good enough for someone to tell me what's going on.
I'm quite certain it is related to the data in the column and how it is being detected. If you can't link to a test case showing the issue and you use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.
Thanks,
Allan
That does seem to be the problem. See this test case:
https://live.datatables.net/folebule/1/edit
The Datatables type detection for dates fails with
0000-00-00
and sets the column type to string. It doesn't seem like0000-00-00
is a valid date as there is no 0 day, month or year. The test case shows that usingDate.parse("0000-00-00")
results inNaN
.You can use Orthogonal data to set the
0000-00-00
values tonull
or""
for thefilter
andtype
operations. Datatables allowsnull
or empty strings to be in a date column. See this example:https://live.datatables.net/xiqonafi/1/edit
SearchBuilder now allows for Between and Not Between options for the Birthday column.
Kevin
Sorry been gone for a while, If this is true @kthorngren, that will be difficult to solve since it's editor that puts in the 0000-00-00 date if I leave it blank. Over and Over again, when I clear the date, Editor places 0000-00-00 back into the field when I submit an edit to update.
I have to use this, so I will see what I can do over the next month. But again, if Editor is setting the column to string, it is doing it intermittently between columns.
I'm not sure about the Editor's expected behavior in this case. Do you have issues with the Editor doing this, aside from SearchBuilder?
Did you try the Orthogonal data option I showed in the example?
https://live.datatables.net/xiqonafi/1/edit
This should work whether its Editor setting the field to
0000-00-00
or something else.Kevin
Thank's Kevin, that worked.