Search Builder Extension. Two numeric (age) columns. < or > operators to act as a 'within' range

mode2ben

Link to test case: https://cataloguesocialcare.uk/datasets/adult-social-care-survey-ascs/
Debugger code (debug.datatables.net): Unable to load to server via Bookmarklet
Error messages shown: N/A
Description of problem:
We have 2 age columns in our datatable (the table contains rows of survey questions).

Age from (e.g. 18) AND Age to (e.g. 120)

These two numbers define an age range that a survey question applies to: anyone between 18 and 120.

We’d like the Search Builder Extension to display rows when using [Age to] is [Less Than] [65] (i.e. it is in our range of 18 - 120).

Currently the Search Builder checks if the 120 is less than 65, which it isn’t, and discards those rows.

But 65 (and 119 down) ARE all lower than the value of the Age To of 120.

Using the Search Builder plugin we want to be able to enter an “Age to” of 65 and get all results where the Age to is lower (than e.g. 120) and that row “kept” in the table of results.

NB: I have searched the forums and docs to no avail, and although the range filtering plug-in heads in the right general direction, it takes two values [upper / lower] and compares to one number cell , rather than a seeing if data is lower than a field value.

Thanks in advance for an awesome resource and any help/direction you can give,
Ben

kthorngren

You are using SearchBuilder 1.3.2. The latest version is 1.4.0. It is possible that was a bug that is now fixed. That issue isn't seen in this example using 1.4.0. I would try upgrading. Open the CDN link you have for the concatenated JS or CSS and use the link proved to update your code.

Also on line 62 you are loading searchbuilder/1.0.0/css/searchBuilder.dataTables.css which conflicts with the 1.4.0 CSS that is loaded in line 61. You should remove it.

If you prefer the search plugin route you can modify this plugin to get one value and just perform a less than comparison.

EDIT: You can also change the plugin to handle both columns with an AND search of greater than or less than values, ie, "within".

Kevin

mode2ben

Thanks so much for your speedy response Kevin.

I have followed your suggestions and have updated to use latest SearchBuilder JS and remove CSS duplicate load.

~ ~ ~

The "problem" (it's not a bug - it's a UI / UX issue really) persists and is that users are trying to use, e.g., the 'Age to' value to locate records using 'Less Than' and a number that is lower than the actual 'Age to' value.

'Age to' cell in table is 120

User creates SearchBuilder condition [Age to ] [ Less than ] [ 65 ]

120 is not less than 65 therefore no records shown.

But to the user 65 is less than 120 so ALL records under 120 should be shown!

[See attached gif for illustration of failing at 65, working at 121].

Our data has an implied age range of 'Age from' TO 'Age to'.

It feels like we need to have another (non table?) field e.g. 'Target age' 'in SearchBuilder for the user to enter a single age and the data filtered to show all records that have 'Age to' less than the 'Target age' and the 'Age to' greater than the 'Target age'.

This is the opposite of the example plugin that allows a single number column/cell to have 2 custom fields to contain upper and lower values: https://datatables.net/examples/plug-ins/range_filtering.html

I think we are trying to achieve the opposite - one number, two columns. Is the number between those two columns - keep the row.

Thanks again. Feels like we're getting somewhere even if it's just articulating the requirement more clearly.

Ben

kthorngren

120 is not less than 65 therefore no records shown.

Sorry, yes I see your question now.

one number, two columns. Is the number between those two columns - keep the row.

Not sure SearchBuilder will do this. However you can create a search plugin to compare if the number is between the values in the two columns. For example:
http://live.datatables.net/xilumucu/1/edit

Kevin

mode2ben

Hi Kevin
That JS Bin is a great demo of a solution to the requirement - thank you.

I think the team here would be keen to incorporate the UI you've presented "within" the SearchBuilder UI as a new condition option.

Do you know how I investigate that possibility? This is not a commercial product / website but we should be able to commission / fund some development or support credits if that is the appropriate route.

Thanks again for your help,
Ben

kthorngren

Datatables and the extensions, except Editor, are open source. You can add the "within" capability to SearchBuilder. @allan can probably give you some guidance if this is the route you choose. He can also tell you if its reasonably possible to add this feature to SearchBuilder. Maybe its a feature he can add or possible you can pay him to develop it for you.

If he misses this thread you can contact him directly using the Ask a Private Question button.

Kevin

allan

Really interesting requirement this - thanks for suggesting it. There isn't the concept of being able to work with multiple different data points in SearchBuilder at the moment - which is really what is required for this. SearchBuilder will let you pick a single column and then perform its conditional logic on that. It is also possible to have multiple values operating on a single column (e.g. the between operator) but what you need here is a single value operating on two (or more) data points.

Doing that would require a significant rethink of how SearchBuilder works, since the UL fundamentally starts with a picker for the column you want to work on. Thinking about it more, I actually think this would require an entirely new extension for DataTables, allowing an equation to be entered and do the search on.

However, all is not lost! I do have one possibility for you that I think will work. Add an extra column that contains the start and end age data as a rage - e.g. 16-120. Then create a custom condition plug-in for SearchBuilder that will be used on that column and have an "Includes" condition that takes a single value. Then the search function for the plug-in would perform a calculation on the range (split the number and parse them, then check the input is in the range).

It would require a little bit of development, but I think entirely possible with SearchBuilder as it currently stands.

Allan

mode2ben

Thanks so much @allan .

Apologies for the stalling on this.

That sounds great. We could easily create another column in our datatable with the concatenated [from]-[to] info and work with that as our ‘Target age range'.

In effect we would display a new ‘Target age range’ column as you suggest e.g. [16-21]

I met with the researchers that will use this data recently and they articulated their requirement more clearly which would have some impact on final UI / solution.

FIRST CASE:

The user enters a single integer into a Search builder field e.g. [19] and the table keeps all rows that have that number within the new ‘Target age range’ concatenated cell value. [16-21] . (16, 17, 18, >>19<<, 20, 21)

SECOND CASE:

The user enters a lower and upper range into the Search builder, so they would want to type into two fields [16] [19] / or a single [16-19] field. The search would then check that at least one of the contents of the entered range (16, 17, 18, 19) is within the new ’Target age range’ field as above [16-21] . (16, 17, 18, 19, 20, 21). Importantly for this scenario results would be kept where ’Target age range’ is of e.g. [0-16], [17-18], [19-120] etc. At least one item of the search array is within the 'Target age range' array.

When you mention " (split the number and parse them, then check the input is in the range) " this sounds like they both could be possible - but I do feel this is some way beyond our skills!

Given those requirements - feeding in range as well as a single value - do you think this is still doable within SearchBuilder as it stands --- and if yes fingers crossed would you be able to advise on how we engage to move this forwards e.g. support credits, development commission etc. ??

Thanks again - agree, this is an interesting challenge to address!
Ben

allan

Hi Ben,

Are you using server-side processing, or client-side?

Allan

mode2ben

Hi @allan - all the datatables stuff is happening client-side.

Thanks,
Ben

mode2ben

Hi @allan - did you have a chance to consider our development requirement? (Also not sure why the <?php tags got inserted into my post -- it wasn't intentional!

Many thanks,
Ben

mode2ben

Just wondering how I should engage with the DataTables / SpryMedia team or community to discuss this custom development / support peice. More than happy to purchase support credits or whatever is the appropriate method.

Tagging @allan and @kthorngren as you've both been so helpful to date.

Thanks
Ben

allan

Gosh - sorry, this one has fallen by the wayside a bit! I'm the only one who works for DataTables / SpryMedia at the moment - Kevin is a community supporter, offering his time free to help people, and goodness me he does a good job!

I'm not sure that I have the bandwidth for the development of this at the moment. I'm tried up with a bunch of other tasks for DataTables at the moment, so it might be a little while before I can tackle this I'm sorry to say. It should be possible with a custom plug-in and the range column I suggested before though. If I get a gap, I'll take a look at it!

Allan

mode2ben

Many thanks for letting me know about this @allan -- is it best to leave this with you and check back in a few weeks?

allan

I've given it a quick bash just now: https://live.datatables.net/pibakase/1/edit . This handles the first use case in your post. Perhaps you'd like to try extending it for the second?

Allan