SearchBuilder providing between option on some date fields but not others

SearchBuilder providing between option on some date fields but not others

Stacey1134Stacey1134 Posts: 112Questions: 20Answers: 0

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

  • kthorngrenkthorngren Posts: 21,336Questions: 26Answers: 4,951
    edited January 26

    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

  • Stacey1134Stacey1134 Posts: 112Questions: 20Answers: 0

    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.

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    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

  • kthorngrenkthorngren Posts: 21,336Questions: 26Answers: 4,951
    edited January 31

    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

  • Stacey1134Stacey1134 Posts: 112Questions: 20Answers: 0

    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.

  • kthorngrenkthorngren Posts: 21,336Questions: 26Answers: 4,951
    Answer ✓

    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

  • Stacey1134Stacey1134 Posts: 112Questions: 20Answers: 0

    Thank's Kevin, that worked.

Sign In or Register to comment.