Filter with date functions

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
ambroise
Posts: 3
Joined: 2017-02-26 05:54

Filter with date functions

Post by ambroise » 2017-02-26 05:59

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

ambroise
Posts: 3
Joined: 2017-02-26 05:54

Re: Filter with date functions

Post by ambroise » 2017-02-26 20:11

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...

Alisson
Veteran Member
Posts: 81
Joined: 2017-02-25 20:32

Re: Filter with date functions

Post by Alisson » 2017-03-07 17:09

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.

User avatar
fbrano
Veteran Member
Posts: 70
Joined: 2018-03-19 10:39
Location: Slovakia
Contact:

Re: Filter with date functions

Post by fbrano » 2018-07-27 23:59

Neither this command CurDate() from example (https://forums.appgini.com/phpbb/viewto ... f=7&t=2062) do not work in filters. Any suggestion?
ver 23.15 1484

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Filter with date functions

Post by pbottcher » 2018-07-28 08:13

Hi,

can you explain where you need to apply the filter?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: Filter with date functions

Post by a.gneady » 2018-08-15 12:42

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');
})
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

User avatar
fbrano
Veteran Member
Posts: 70
Joined: 2018-03-19 10:39
Location: Slovakia
Contact:

Re: Filter with date functions

Post by fbrano » 2018-09-07 11:35

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.
ver 23.15 1484

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Filter with date functions

Post by pbottcher » 2018-09-07 12:40

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.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
fbrano
Veteran Member
Posts: 70
Joined: 2018-03-19 10:39
Location: Slovakia
Contact:

Re: Filter with date functions

Post by fbrano » 2018-09-19 06:45

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
ver 23.15 1484

Ionut Bocanet
Posts: 28
Joined: 2017-03-12 09:26
Contact:

Re: Filter with date functions

Post by Ionut Bocanet » 2019-08-14 06:13

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
Best Regards,
Ionut Bocanet

User avatar
wsiconcrete
Posts: 18
Joined: 2015-04-27 16:39
Location: SC, USA

Re: Filter with date functions

Post by wsiconcrete » 2020-04-17 15:17

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

Post Reply