how can i search date via search builder if i searched from array of dates columns

justleaveitblank

my table return users all transaction dates but the transaction dates columns is an array this is it how it written on the table

Transaction Dates
2015-03-24, 2015-03-27, 2015-04-01, 2015-05-18, 2015-06-09, 2015-07-22, 2015-07-28, 2015-08-03, 2015-08-19, 2015-09-09, 2015-10-06, 2015-10-09, 2015-10-14, 2015-10-28, 2015-11-24, 2016-02-18, 2016-02-23, 2016-06-07, 2017-05-15, 2017-07-12, 2017-07-12, 2017-07-13, 2017-09-15, 2017-09-26, 2017-09-27, 2017-10-05, 2018-04-05, 2020-07-11, 2021-05-26, 2021-10-27, 2022-06-16, 2023-05-10

here is my current code

<script>
$(document).ready(function() {
        $('#user-table').hide();
        let datasets = <?= json_encode($endUsers)?>;
        
        var columns =  [
            { data: "User_ID" },
            { data: "First_Name" },
            { 
                data: "transactionsCompletedDates",
                render: {
                    _: '[, ]',
                    sb:'[]'
                },
                searchBuilder: {
                    orthogonal:'sb',
                    
                },
                searchBuilderType: 'array',
            },
                
        ];

 var table = $('#user-table').DataTable({
            data: datasets,
            fixedHeader: true,
            stateSave: false,
            responsive: true, 
            colReorder: true,
            dom: 'QlBfrtip',
            columns: columns,
            searchBuilder: {
                logic: 'OR',
                columns: [
                    0, 1, 2
                ],                
            },
            columnDefs: [
                {
                    target: hideColumns,
                    visible: false,
                },
                {
                    targets: 2, // Column index 17 (Transaction Dates)
                    type: 'date-range', // Set the column type as 'date-range'
                    searchBuilderType: 'date-range', // Set the search builder type as 'date-range'
                },
            ],
            
            order: [[0, 'asc']],
            
        });
    });
</script>

if i search on the serchbuilder like data = transaction dates and condition appears that the transaction dates read as a string so i cant filter it as a date but if i change searchBuilderType: 'array' to 'date' my table is not responding

can someone please help me with this, any help is greatly appreciated. Thanks in advance

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

justleaveitblank

Sorry for that here is the link of test case

I just want to filter the array of dates as dates

kthorngren

Using Contains seems to work but using Equals does not with transactionsCompletedDates. It looks like there is an issue in SearchBuilder 1.6.0 when using Equals with rendered arrays. I can replicate the issue with this example. Selecting Permissions > Equals > Accounts does not work even though there are two rows with just Accounts as the value.

@allan will need to take a look at this.

Kevin

kthorngren

Or are you saying you want a date picker instead of a select list to choose the dates?

Kevin

justleaveitblank

My the problem is i need it to filter as a date like the conditions should be like Equals, Before, After, Between, and etc. but when i change searchBuilderType: 'date', my table don't even filter, maybe because the array returns as a string not a date or something, i am just guessing.

kthorngren

Hmm not sure how that would work with multiple dates in the column. Take for example these dates "2023-09-25", "2023-09-30", "2023-10-10". Lets say I choose is after "2023-09-29". Two of the dates are true and one is false. Would you expect the row to be hidden or displayed?

I could be wrong but I don't believe SearchBuilder supports searching by dates when they are in an array. @allan can confirm this and if not can confirm adding it as a feature.

You could create your own date picker with the Equals, Before, After, Between, and etc options and create a search plugin to apply these options as required for your solution. This plugin performs a date range search. You could adapt it to support arrays based on your business logic.

Kevin

justleaveitblank

What i want to do if any of the dates is true to the condition it will display a row on the table

Ex 1: if i search Transaction Dates > Equals > 2023-09-25
if one of the values of the array match with this value it will be displayed on the table

Ex 2: Transaction Dates > Between > 2023-09-25 > 2023-10-21
if one of the values matches this condition it should be displayed as well>

same to others before and after and etc. the catch is if just one value matches the search condition it should be displayed on the table

justleaveitblank

i already try this Date Range Filter with my case of array of dates but not lucky with this either

justleaveitblank

if possible i don't want to add specific rangefilter like this.

colin

As Kevin said, the search plugin would be the way to go there. Your situation is so specific to your data, there's not going to a pre-canned solution, so it'll need that custom plugin to perform the logic.

Colin