Page 1 of 1

Filter with date functions

Posted: 2017-02-26 05:59
by ambroise
Hi,
How can I acheive a filter using date(now()) or getdate() (not an expert here!) for example to get all orders where shipping date is empty or greater than today's date?
I'm new to AppGini, with latest version.
I've been through the forum and help and it seems like I'm the first to ask?!?!?
Thanks!
Ambroise

Re: Filter with date functions

Posted: 2017-02-26 20:11
by ambroise
By the way, is there any way to filter based on data from another table? I need all records from table 1 which has a record in table 2 with a particular value...

Re: Filter with date functions

Posted: 2017-03-07 17:09
by Alisson
I'm also looking for the same thing. Filter fields that are equal to the current date, by I also need current time to see any overdue.

Re: Filter with date functions

Posted: 2018-07-27 23:59
by fbrano
Neither this command CurDate() from example (https://forums.appgini.com/phpbb/viewto ... f=7&t=2062) do not work in filters. Any suggestion?

Re: Filter with date functions

Posted: 2018-07-28 08:13
by pbottcher
Hi,

can you explain where you need to apply the filter?

Re: Filter with date functions

Posted: 2018-08-15 12:42
by a.gneady
You could, using JavaScript, insert a link (probably above the table view) that applies your date-based filter. For example, the code below, if placed in hooks/tablename-tv.js (where tablename is the name of the concerned table), would filter the data to show only records dated tomorrow (assuming the date field is the 3rd field in your table):

Code: Select all

$j(function() {
    var date = new Date();
    // set date to tomrrow
    date.setDate(date.getDate() + 1);

    // formatted date .. change order and separator to the ones you selected in your AppGini project
    // in the below line, the format is month/day/year
    var formattedDate = (date.getMonth() + 1) + '/' + date.getDate() + '/' + date.getFullYear();

    // now build the 4 parts of the filter
    var filter = {
        'filterAnd[1]': 'And',
        'filterField[1]': 3,  // change this to the actual index of your date field
        'filterOperator[1]': 'equal-to',
        'filterValue[1]': formattedDate
    };
    
    // building the filter url ... change tablename to the actual table name
    var url = 'tablename_view.php?' + $j.param(filter);

    // finally, insert the filter button
    $j('<a class="btn btn-lg btn-default" href="' + url + '">Due tomorrow</a>').prependTo('#top_buttons');
})

Re: Filter with date functions

Posted: 2018-09-07 11:35
by fbrano
In my project it works this way:

$j(function() {
var date = new Date();
date.setDate(date.getDate());

// formatted date .. change order and separator to the ones you selected in your AppGini project
// in the below line, the format is month/day/year
var formattedDate = (date.getDay()+2) + '.' + (date.getMonth()+1) + '.' + date.getFullYear();

// now build the 4 parts of the filter
var filter = {
'SortField=&SortDirection=&FilterAnd[1]': 'And',
'FilterField[1]': 6, // change this to the actual index of your date field
'FilterOperator[1]': 'less-than-or-equal-to',
'FilterValue[1]': formattedDate
};

// building the filter url ... change tablename to the actual table name
var url = 'pouzivatelia_view.php?' + $j.param(filter);

// finally, insert the filter button
$j('<a class="btn btn-lg btn-default" href="' + url + '">Ukoncene zmluvy</a>').prependTo('#top_buttons');
})

Can you explain why I have to add +2 day na +1 month to get today date? Also when I put code in tablename-tv.js where was other function used for batch action it did not work. Only when this code is alone in tablename-tv.js.

Re: Filter with date functions

Posted: 2018-09-07 12:40
by pbottcher
Hi,

not sure if you are lucky with this as the getDay returns the day of the week, not the day of the month.

so you should change it to

Code: Select all

 formattedDate = (date.getDate()) + '.' + (date.getMonth()+1) + '.' + date.getFullYear();
Thhe getMonth returns you the month starting at 0, so that is why you need to add +1 in order to reflect the correct month.

Also you do not need the

Code: Select all

date.setDate(date.getDate());
as this does actually nothing in your case.

For your second question it might be that you handle the function and the jquery code not correctly, so that might be a syntax error that prevents correct execution.

Re: Filter with date functions

Posted: 2018-09-19 06:45
by fbrano
This Works:

$j(function() {
var d = new Date();

// formatted date .. change order and separator to the ones you selected in your AppGini project
// in the below line, the format is month/day/year
var formattedDate = (d.getDate()) + '.' + (d.getMonth()+1) + '.' + d.getFullYear();

// now build the 4 parts of the filter
var filter = {
'FilterField[1]': 6, // change this to the actual index of your date field
'FilterOperator[1]': 'less-than-or-equal-to',
'FilterValue[1]': formattedDate
};

// building the filter url ... change tablename to the actual table name
var url = 'pouzivatelia_view.php?SortField=6&SortDirection=desc&' + $j.param(filter);

// finally, insert the filter button
$j('<a class="btn btn-lg btn-default" href="' + url + '">Todays' teminations</a>').prependTo('#top_buttons');
})

Thank you

Re: Filter with date functions

Posted: 2019-08-14 06:13
by Ionut Bocanet
How can I add in the same button another filter ?

I want to add besides the passed date but also the "Completed" Status

I want to show the records that are overdue and with the status <> "Completed"

Thank you

Re: Filter with date functions

Posted: 2020-04-17 15:17
by wsiconcrete
Wasn't able to get this to work in 5.82 so compared URL to what AG was generating from the filter page. To get this to work on my app generated with 5.82 I had to uppercase the parameters in the filter var and lowercase the 'and' like so:

Code: Select all

    var filter = {
        'FilterAnd[1]': 'and',
        'FilterField[1]': 11,  // change this to the actual index of your date field
        'FilterOperator[1]': 'equal-to',
        'FilterValue[1]': formattedDate
Maybe that will save someone the 30 minutes I spent figuring it out if they have the same issue.

Thanks!
Brandon