SearchBuilder providing between option on some date fields but not others

Stacey1134

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.

kthorngren

BTW, I also removed the searchable trigger on Birthday, and that did nothing either.

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

Stacey1134

Thanks, but this is much simpler than that.
Here is the table structure

CREATE TABLE `Contributors` (
  `id` int(11) NOT NULL COMMENT 'Key Index field of contributors',
  `FullName` varchar(255) DEFAULT NULL COMMENT 'This field will be DELETED',
  `LastName` varchar(255) DEFAULT NULL,
  `FirstName` varchar(255) DEFAULT NULL,
  `PreferredName` varchar(255) DEFAULT NULL,
  `PrisonID` varchar(30) DEFAULT NULL COMMENT 'DOC Number',
  `FacilityID` int(5) DEFAULT NULL COMMENT 'Key Index field to National Facilities Database',
  `PrisonUnit` varchar(255) DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL COMMENT 'This field will be DELETED',
  `City` varchar(255) DEFAULT NULL COMMENT 'This field will be DELETED',
  `State` varchar(15) DEFAULT NULL COMMENT 'This field will be DELETED',
  `Zipcode` varchar(15) DEFAULT NULL COMMENT 'This field will be DELETED',
  `MailingNotes` text,
  `Birthday` date DEFAULT NULL,
  `Age` int(2) DEFAULT NULL,
  `Race_Ethnicity` varchar(54) DEFAULT NULL COMMENT 'Self Described',
  `Sexual_Orientation` varchar(255) DEFAULT NULL COMMENT 'Self Described',
  `Gender_Identity` varchar(100) DEFAULT NULL COMMENT 'Self Described',
  `Disability` varchar(255) DEFAULT NULL COMMENT 'Self Described',
  `Donations` text COMMENT 'This field will be DELETED',
  `Bio` int(11) DEFAULT NULL COMMENT 'Index key to scanned_bios table',
  `BioText` text NOT NULL,
  `Subscribed` varchar(7) DEFAULT NULL COMMENT 'This field will be DELETED',
  `NoCards` varchar(7) DEFAULT NULL COMMENT 'This field will be DELETED',
  `image` int(11) DEFAULT NULL COMMENT 'Index Key to scanned_files table',
  `hasWaiver` tinyint(4) DEFAULT NULL COMMENT 'Contributor has signed Waiver/Release',
  `isSubscribed` tinyint(4) DEFAULT NULL COMMENT 'Subscribed to Newspaper',
  `noGCards` tinyint(4) DEFAULT NULL COMMENT 'cannot have Greeting Cards',
  `firstContact` date DEFAULT NULL COMMENT 'Date contributor joined',
  `achievements` text,
  `EditedBy` varchar(255) DEFAULT NULL COMMENT 'email of person making last changes',
  `si_race` varchar(255) NOT NULL,
  `si_sex_or` varchar(255) NOT NULL,
  `si_gen_id` varchar(255) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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.

,"Birthday":"1940-10-09",
,"firstContact":"2020-04-27",

That should be good enough for someone to tell me what's going on.

allan

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

kthorngren

Some entries are '0000-00-00' but that is true for both fields, so that's not the problem.

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 like 0000-00-00 is a valid date as there is no 0 day, month or year. The test case shows that using Date.parse("0000-00-00") results in NaN.

You can use Orthogonal data to set the 0000-00-00 values to null or "" for the filter and type operations. Datatables allows null 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

Stacey1134

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.

kthorngren

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

Stacey1134

Thank's Kevin, that worked.