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
Filter with date functions
Re: Filter with date functions
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
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
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
Re: Filter with date functions
Hi,
can you explain where you need to apply the filter?
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.
Re: Filter with date functions
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');
})
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.
Re: Filter with date functions
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.
$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
Re: Filter with date functions
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
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
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.
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();
Also you do not need the
Code: Select all
date.setDate(date.getDate());
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.
Re: Filter with date functions
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
$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
-
- Posts: 28
- Joined: 2017-03-12 09:26
- Contact:
Re: Filter with date functions
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
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
Ionut Bocanet
- wsiconcrete
- Posts: 18
- Joined: 2015-04-27 16:39
- Location: SC, USA
Re: Filter with date functions
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:
Maybe that will save someone the 30 minutes I spent figuring it out if they have the same issue.
Thanks!
Brandon
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
Thanks!
Brandon