questions about SearchBuilder

LimpEmu

Link to test case: live.datatables.net/mezaxuxa/1/edit?html,console,output
Debugger code (debug.datatables.net): No debugger messages, it's working, just does not quite do what I expect.
Error messages shown: No error messages.
Description of problem:

I recently discovered the searchBuilder extension and was able to integrate it easily into a fairly simple data table. However, when I tried to use it with a more complex data table, I ran into several issues that I have been unable to resolve with the rich documentation provided. [Note that the editor extension is used in my work as well, but I have removed all relating to this extension from the test case.]

1.
I cannot get the searchBuilder option to work that restricts the columns available in the primary searchBuilder drop-down. No matter what I do, all columns are included. My syntax:

        "searchBuilder": {
            columns: [0,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49],
        },

I have also tried using the column names and a CSS style, those methods did not work either. Specifically, I do not want the columns hospno and ccs to be included. ccs must be searchable/filterable though.

2.
The table has several time columns. Currently a type "time" is not supported, therefore I treat these columns as type:"num-fmt" which works well as long as the user enters the time in the format HH:mm or H:mm. It would be very nice to be able to use the time selection widget in these instances.

The table also has a column named GA (= gestational age) which is provided as completed weeks and days. The column should be displayed as WW/D. I am using the type:"num-fmt" for this column as well, but several searches do not work (between, less than, greater than, not between, less than or equal, greater than or equal). Based on https://github.com/DataTables/SearchBuilder/blob/master/src/criteria.ts, for searches of type:"num-fmt", everything but numbers and periods is removed, so I am puzzled why it doesn't work. Note that in the example I actually added number formatting that displays the value as WW.D, and it still does not work.

3.
The table has several date columns. I use different combinations of type and display properties to get the search to work as below.
For the column DOB I use type:"string" since it allows me to use YY-MM-DD.
For Admit Dt I use type:"date" and searches work if I format the column as YYYY-MM-DD. My guess is this works since this is how the date is captured from the date input widget that pops up. Is this correct?
For Disp Dt I am using type:"date" and searches do not work since I did not change the column display from the format that I would like to use.
Is it possible to extend the date search functionality, so it is more flexible with respect to the displayed value? I understand that I can write a plug-in that will behave the way I need.

4.
The table has a date/time column Updated with type:"date". SearchBuilder only shows the date widget, but never the time widget. Why does the time widget not show? I found https://datatables.net/forums/discussion/64674/searchbuilder-pre-define-values-for-moment-datetimepicker, which has the behavior I would like. Unfortunately the links in that discussion did not help me.

5.
For several columns, e.g., Birth Loc I wrote a plug-in htmlclone that only includes three options. The reason is that these fields are shown with the ellipsis plug-in, which results in the select list being populated with XXXXXX… Is it possible to allow the full string to be used for the select box?

I am very impressed with this extension and greatly appreciate your feedback, thank you!
Beate

sandy

Hi @LimpEmu ,

1. Since you are using SearchBuilder with a button, any config that you apply to it must be in that buttons config option, not the global SearchBuilder config option. Take a look at this example. I've also edited your example to show it working there.

2. You should be able to filter on formatted dates and times by using moment. Take a look at this example to see how it is done.

3. The date column type parses ISO8601 date formats. For anything else, you'll need to use moment to get the filtering to work. Again, take a look at the example referenced in part 2. For both the date and moment types, the date time selector appears yes. It might also be worth noting that if SearchBuilder doesn't recognise the type it defaults to string.

4. To get the time widgets to show you'll need to use a moment format. As mentioned above, just date will only work with ISO8601 standards.

5. This is the code that SearchBuilder uses to populate the select elements. Notice here that it uses the display type to populate the text in the select element. If you were to copy this function, then you could change that to get the raw data from filter and it shouldn't have the ellipsis then.

Thanks,
Sandy

LimpEmu

Thank you so much @sandy ! Getting to work on making all these changes, this will be a wonderful enhancements for our users!

LimpEmu

Thank you for the fix for excluding columns from searchBuilder.

But I am not having much luck with the date and time suggestions.

I have tried following the example in your response, see live.datatables.net/zuleceko/2/, but I am getting a JavaScript error. I am including the two libraries you recommended, and I am also using type:"moment".

Chrome indicates:

Uncaught DateTime: Without momentjs or dayjs only the format 'YYYY-MM-DD' can be used

And the JS Bin indicates:

"Script error. (line 0)"

Also, could you check on the second part of question 2 above, regarding the GA column?

colin

The problem was because the Moment.js include was after SearchBuilder. See here - that removes the error.

That said, it's still not correctly parsing on the dates. We'll take a look and get back to you - it'll probably be in a week's time (DD-1693 for my reference).

Colin

LimpEmu

Thank you, @colin ! I much appreciate it!!!

And I thought more about the problem with the GA column. In the JSON array, it has a character format, I have a fix in mind. So don't spend your valuable time on that!

allan

The problem with the dates was two fold:

1. The columns.type was being specified for these columns. It was being set to just moment, which is incorrect. In general you should never set the type parameter for a column. Let DataTables' type detection do it for you. If that doesn't work, then forcing it to be a type isn't going to work either.
2. The date format in the DOB column is DD-MM-YYYY not DD-MM-YY as was used in the code (although Admit Dt appears to use that format).

Here is a partly updated example. I've not gone through all of the columns to remove the types specified.

Allan

LimpEmu

Thank you, @allan .

I usually do not use the type specification for the columns in a table and added them because I had trouble getting things to work. I just removed all my type: settings, and this addressed my problem with the date, times and date/time columns.

The JSON array always uses the format MM-DD-YYYY for all the dates, but I want to only display MM-DD-YY in the table to save a little space.

Thank you again for all your help, all my problems are fixed!