How to use date range filter and filter on the same table?

How to use date range filter and filter on the same table?

FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

I need to add date range filter + filter on the same table. Date range filter is working but filter is not working as intended. Here is the test case: http://live.datatables.net/radogoxa/1/edit

«1

Answers

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394
    edited July 2022

    filter is not working as intended.

    What do you mean? What happens?

    If you are talking about the search input box, it's working for me in your test case.

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @tangerine Yes, I am talking about search input box. Try to search for "Garrett Winters" and check ...

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    Datatables performs an AND search across all the columns. You would need an OR search to search for items that are not included in the selected date range. To do this you can either create your own search input or take over the default search input, something like this:
    http://live.datatables.net/jorexujo/1/edit

    The search event handler will just call draw() to run the search plugin and not use search(). in your range search plugin you will need to add code to see if there is a match in the row for the search input or if the range matches. If either condition is true then return true. Otherwise return false.

    Kevin

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @kthorngren Could you please explain and help me more... Because I didn't understand... and I am having this issue for long time...

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Which specific part of Kevin's answer and example do you need clarification on? Perhaps you can show us what you have tried so far?

    Allan

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0
    edited July 2022

    @allan What I have tried is in the test case above. I just need a explanation of Kevin's test case on how to implement it with date range filter on my test case... I guess the filter didn't work because only today's row or selected days row appear so the filter only works when on what is appear on the screen... I don't know if it is the case... and I don't know how to fix it... It seems difficult to implement...

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    I guess the filter didn't work because only today's row or selected days row appear so the filter only works when on what is appear on the screen

    Essentially that is correct.

    I created an example of replacing the default Datatables search with a search plugin. It takes over the event handlers for the search input. The plugin uses regex try matching the data in each column. You may find using Javascript indexOf() or includes() to be more efficient. I never compared them. If its of concern you can see if there are Stack Overflow or other threads discussing the difference in performance. Start by getting this example to work the way you want.
    http://live.datatables.net/jorexujo/702/edit

    Once its working then update your date range example to have a boolean variable (rangeFound for example), like the found variable in my example, that is returned at the end of the function. So only one return. Once you have that working then combine the two into one function and at the end use something like return found || rangeFound;. This will return true if either the range matches or the search input matches.

    Kevin

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @kthorngren I wonder why the filter doesn't work unless the rows appear on the screen, but date range filter works very well. I recommend fixing this on the official datatables source code. Now, regarding your test case, I just need to add the date range filter with some modifications?

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

    Try to search for "Garrett Winters"

    "Garrett Winters" is not found because that record is outside the date range you have set.
    That demonstrates the difference between AND and OR which Kevin has explained to you. In that example, your filters are saying "Find records between these dates AND name equals Gareth Winters". Obviously that won't find anything.
    What don't you understand here?

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0
    edited July 2022

    Yeah thanks @tangerine I already understand this part. Now I am trying to find a way to merge date range filter with the regular filter. Again, I hope this will be added in the documentation in the future... Since, I am not that good on coding, and I always use Datatables documentation to make things easier on me...

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    The documentation will always break components down - e.g. the date filter on its own. We assume a certain level of Javascript knowledge since we can't possibly document Javascript from first principles.

    That said, I'm actually not clear on what the problem you are having is still? When you say merge the two filters, do you mean you want a single input element? Or are you just trying to have the date filter inputs and the global filter for the table shown?

    Allan

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    Here is the updated OR search example:
    http://live.datatables.net/radogoxa/3/edit

    I found that you can't use the default Datatables search input. A process within the Datatables code clears the input at some point - presumable because there is no search via the input taking place. Since you are attaching the date input to the builtin search I hid it the search input in initComplete. I created a customer search input and used the toolbar technique in this example to show it. Your date range search didn't seem quite right so I made some changes.

    Kevin

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @kthorngren Thanks! Your test case works pretty well! But if you noticed in my test case, I wrote a code that shows today's code when opening the page/link. How can I add this in your test case? I've added this
    } else if ( max <= "" && min <= "" ) { if ( moment().format('YYYYMMDD') === date ) { return true; } return false; }
    before the last dateRangeFound = false but it didn't work...

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    but it didn't work

    What happens?

    If you use return statements then the other code that checks the search input value won't be executed eliminating the OR capabilities for this case. Use some breakpoints or console.log statements to do some debugging of the new if statement and flow of the code to learn what its doing.

    Kevin

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @kthorngren Yeah so I need to show today's row when opening the page/link. When I add the code above, nothing happens. It still shows all rows when loading the page...

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    You might need to move your code up a little bit to this section:

        // Show the row if the search input and date range input are blank
        if ( search.length === 0  && max === "" && min === ""  ) {
          return true;
        }
    

    On page load this if statement will be true so all the rows will be shown and the code below it won't be exevuted.

    Kevin

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0
    edited July 2022

    @kthorngren I added today's date in the table and I moved the code above if statement but I am getting in the output "No matching records". I tried also moving the code below if statement nothing happened... Still all rows are shown. I reviewed my code in my test case and I found that I MUST put this code

    if ( max <= "" && min <= "" ) {
              if ( moment().format('YYYYMMDD') === date ) {
                return true;
              }
              return false;
            }
    

    under this code

    var date = moment( data[4], 'DD/MM/YYYY' ).format('YYYYMMDD');
    

    so I tried that but I am still getting all rows...

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Please link to a page showing what you have used so we can take a look.

    Allan

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0
  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    I moved your code inside the if statement like I suggested above:
    http://live.datatables.net/bakeyaco/1/edit

    Kevin

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0
    edited July 2022

    @kthorngren Yep, showing today's row on load is working in your test case but the date range filter stopped working... Is it impossible to use date range filter + regular filter + showing today's row on load?

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    Sorry placed this statement in the wrong place:

    var date = moment( data[4], 'DD/MM/YYYY' ).format('YYYYMMDD');
    

    http://live.datatables.net/bakeyaco/2/edit

    Kevin

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @kthorngren Thanks!!! It worked perfectly fine!!! But there is a very small issue I need to ask about in search input box. If I typed the letter "t", all rows that has the letter "t" will appear which is not what I want. I need to filter letter by letter. For example, if I type "t" it must bring all rows that start with the letter "t" and so on. So, I need to search in an order of the letters.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    Change the regex expression to make a more specific search, maybe something like this:

          // Build regex expression
          let re = new RegExp( '^' + search + '$', 'i' );
    

    Kevin

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0
    edited July 2022

    @kthorngren Here is the test case http://live.datatables.net/necananu/1/edit
    I've replaced
    // Build regex expression let re = new RegExp( search, 'i' );
    with
    // Build regex expression let re = new RegExp( '^' + search + '$', 'i' );
    and I tried to type the letter "t" it says no matching records.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    Misread your problem description. Remove the trailing $, like this:

    http://live.datatables.net/necananu/2/edit

    Kevin

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @kthorngren Thanks! 100% done! Thank you very much! Also thanks to @allan

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @kthorngren I've seen you've used an older version of datatables 1.11 I have the latest version which is 1.12 and it didn't work... Is there a way to make it work with the latest verison?

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @kthorngren You made the search input box in a toolbar, but I can make it in the same div/row of the date range filter inside "initComplete" function, not independent in a toolbar?

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    It works with DT 1.12.1:
    http://live.datatables.net/necananu/2/edit

    Your test case is loading datatables.js multiple times which you shouldn't do.

    You made the search input box in a toolbar, but I can make it in the same div/row of the date range filter inside "initComplete" function, not independent in a toolbar?

    Yes, its a custom search input. You can create it however and where ever you want.

    Kevin

Sign In or Register to comment.