Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

Filter Table Date Column with Form Builder?

I've created an application and included a table that has DATETIME timestamps column. The visualization is Tabular Report.
I need to be able to set the form builder to filter the results in the table by date. Here is what I have so far:
  • 12 Grid
  • Single Line column
  • Label Name: Date:
  • Field Name: create_time
  • Default: {$_today_}
  • Data Type: Date Range
  • OnChange Script:
    select * from qw_detail where date_format(create_time, '%Y-%d-%m %h:%i:%s') between '{$fromDate}' AND  '{$endDate}'

I don't get any errors with this. When I preview the application, all entries in the table are visible and the form for choosing date says "no data found!" next to the Submit button. Changing the date to one that matches one of the entries results in the same thing. No filtering and the form says no data found.

What am I missing or doing wrong?

Comments

  • Hi,

    Please check the fromDate and endDate, the format are : %Y-%m-%d, so your application query shoud be:

    select * from qw_detail where date_format(create_time, '%Y-%m-%d') between '{$fromDate}' AND '{$endDate}'

    If you are using the Date Range control, the application script should be:

    select * from qw_detail where date_format(create_time, '%Y-%d-%m %h:%i:%s') between '{$date_start}' AND '{$date_end}'

    The date range will generate TWO input values: _start and _end



  • edited July 30
    It is still giving a "no data found!" message next to the submit button. I am using the Date Range control with the 2nd query you've posted. The MYSQL column datatype is DATETIME with CURRENT_TIMESTAMP() as the default expression.
    The database is being used by an Ionic 1 app and needs to automatically generate the timestamp in that column when data is added to the table.
  • Hi,

    I am sorry for this issue.
    How about
    select * from qw_detail where from_unixtime(create_time, '%Y-%m-%d') between '{$fromDate}' AND '{$endDate}'

    If the column type is timestamp, we should use from_unixtime to format the columns to the date range format.

    Please also take a look the logs window, and see the SQL query that submitted database.

    Thanks
  • I'm still getting the same result. The only thing in the log window is:
    2017-08-07 18:44:05 SELECT `stock`, `vin`, `make`, `model`, `year_model`, `create_time` FROM `qw_detail`
Sign In or Register to comment.