Answers

• colin Posts: 15,177Questions: 1Answers: 2,590

  August 2021

  It appears to be working fine here. Could you look at that, please, and see if it helps. If it's still not working for you, please can you update my example, or link to your page, so that we can see the problem.

  Cheers,

  Colin
• Moebius Posts: 12Questions: 3Answers: 1

  August 2021

  Hi Colin,

  Thank you for the response and the example. It confirms my further research.

  In your example, as far as I can tell, the 'Between' operator does not appear when these columns are chosen:

  Name
  Position
  Office

  But it does appear when these are chosen:

  Age
  Start Date1
  Start Date2
  Salary

  I see the same behavior in my application, so it has nothing to do with having one date column or not. I guess I need to revise my question.

  Is there some condition that causes the Between operator to appear or not to appear?
• tangerine Posts: 3,350Questions: 37Answers: 394

  August 2021

  That would appear to be dates and numbers, unless you are expecting something like "between Edinburgh and San Francisco" or "between Technical Author and System Architect" - neither of which make any sense to me.
  What behaviour are you looking for?
• Moebius Posts: 12Questions: 3Answers: 1

  August 2021

  In my app, the issue is with date columns. I get that 'Between' on a non numerical column doesn't make sense. I was just trying to figure out the criteria that SearchBuilder uses to populate the drop down list.

  Here's what I've checked and double checked:

  • There are two different tables in my MySQL database
  • Both of them have DATE columns
  • Both are the same data type in MySQL
  • Both are formatted the same in the PHP page

  SearchBuilder returns the 'Between' option on the date column from the first table, but does not return the 'Between' option on the date column in the second table. I just need to find out why, because it's a requirement for my end users.

  The data is proprietary, but if necessary I can fake something up. Thank you for the support.
• tangerine Posts: 3,350Questions: 37Answers: 394

  August 2021

  Ah, okay. Certainly I would expect "Between" to be offered for both date fields.
  Are both tables on the same page? And if so, do they have different ids?
• Moebius Posts: 12Questions: 3Answers: 1

  August 2021 edited August 2021

  Yes, both are on the same page, but in separate tabs (nav tabs).

  They both have different id's, as in id="tableOne" or id="tableTwo". Then, I pass that to the SearchBuilder / javascript as #tableOne and #tableTwo.

  So right now, I have a separate script for each tab, as below. Maybe that's the problem?

              $('#tableTwo').DataTable( {
                      dom: 'Qlfrtip'
                  });
  
• colin Posts: 15,177Questions: 1Answers: 2,590

  August 2021

  It sounds to me like your date is being treated as a string. It would be worth verifying that all the values are indeed dates.

  If no joy, as I said before, please can update my example to demonstrate the problem, or link to your page so we can take a look,

  Colin
• Moebius Posts: 12Questions: 3Answers: 1

  August 2021

  Okay, I copied and anonymized some date from my application and make a fork of Colin's live stream:

  http://live.datatables.net/kevegane/1/edit

  If it works, hopefully it shows that I have two columns, Created Date and Closed Date.

  Created Date returns the Between Option and Closed Date does not.
• Moebius Posts: 12Questions: 3Answers: 1

  August 2021

  New fork:

  http://live.datatables.net/fibifure/1/edit
• colin Posts: 15,177Questions: 1Answers: 2,590

  August 2021 Answer ✓

  Yep, the problem is your date "0000-00-00" - that's not a valid date. Yep, the format is correct, but there isn't a month or day with value "00" , so the column is being treated as a string.

  The best way to address would be either to change the data at the source, or blank it out in columns.render - see example here: http://live.datatables.net/fibifure/3/edit

  Colin
• Moebius Posts: 12Questions: 3Answers: 1

  August 2021 edited August 2021

  Yes, that worked. Thank you, thank you, thank you.

  Apparently the root cause is that the fields in the legacy Access database were blank. When they were exported to CSV and imported into MySQL in a date column, it rendered that as '0000-00-00'. I don't know if there's a way to handle that when loading the data, but lesson learned.

  One other thing I discovered is that to assign that to multiple columns it would be:

                      columnDefs: [{
                      targets: [4, 5],
                      render: function(data) {
                          return data === "0000-00-00"? '' : data;
                      }
                      }]
  

  Does that seem correct?

  SearchBuilder is an amazing plug in. Thank you for your support and help.
• colin Posts: 15,177Questions: 1Answers: 2,590

  August 2021

  Yep, that's correct! Glad you're enjoying SearchBuilder - it's one of our newest extensions,

  Colin