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

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

justleaveitblankjustleaveitblank Posts: 6Questions: 0Answers: 0

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

Replies

  • colincolin Posts: 15,087Questions: 1Answers: 2,580

    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

  • justleaveitblankjustleaveitblank Posts: 6Questions: 0Answers: 0
    edited October 2023

    Sorry for that here is the link of test case

    I just want to filter the array of dates as dates

  • kthorngrenkthorngren Posts: 19,864Questions: 26Answers: 4,685
    edited October 2023

    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

  • kthorngrenkthorngren Posts: 19,864Questions: 26Answers: 4,685

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

    Kevin

  • justleaveitblankjustleaveitblank Posts: 6Questions: 0Answers: 0

    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.

  • kthorngrenkthorngren Posts: 19,864Questions: 26Answers: 4,685
    edited October 2023

    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

  • justleaveitblankjustleaveitblank Posts: 6Questions: 0Answers: 0

    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

  • justleaveitblankjustleaveitblank Posts: 6Questions: 0Answers: 0
    edited October 2023

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

  • justleaveitblankjustleaveitblank Posts: 6Questions: 0Answers: 0

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

  • colincolin Posts: 15,087Questions: 1Answers: 2,580

    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

Sign In or Register to comment.