Grid - jump to special date - predefined search

Applications Grid / Reports
, ,
#1

Hi

i’m still new to SC and hopefully there is a simple solution for my problem

I have a grid that shows all of the events - some are in the future (until dez 2015) and a lot are in the past - the grid shows 20 events - and is sorted by start_date - you can see it here http://development.mitsm.de/EduMS/mITSM_Education_Management_System/

It is more likly that the user will have to work on an event that is in the next future - let’s say the next two month - so when the grid is opend i would like to jump to an event thatn has the startrdate “today” an the schould be last event in the grid - so the users sees the “next” 20 events -

to make it even more complicated , unfortunately not every day an event is started -

Can I do someting like a predefined search? that is executed the firs time the grid is opend like with the criteria start_date > “today”. -

some more questions

  1. would that be first in grid not last? that would be ok - i could do “start_date > “today” - 10 days” or so
  2. can I assign the search to a button that is display at the top of the grid so even if user changed it he can jump again to today pressing the button
  3. would the solutions also work for forms?

Thanks a lot !!!
rob

#2

that’s not complicated at all man… simply modify the query in your SQL section for the grid for somehting like


SELECT * //or the field list of your choice
FROM    events 
WHERE  EventDate BETWEEN CURDATE() and CURDATE()+10

if this is helpful donate a coin to someone in need =D

#3

thanks for the fast answere - let’s call it SQL solution - here an optimized version

SELECT *
FROM event
WHERE start_date BETWEEN (DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) and (DATE_add(CURDATE(), INTERVAL 2 MONTH))   
ORDER BY start_date

but that is not exactly what I want, because then I would have to maintain 2 grids, right? grid_event_all and grid_event_intervall

i would prefer the predefiend “search button solution” if possible - also for other predefined searches

Thanks rob

donate a coin to someone in need =D

I will :slight_smile:

#4

Note - I’m totally new to SC - probably I should have posted this question in the Search area?

#5

another version - easier than typing DATE_ADD or DATE_SUB all the time - rob

WHERE start_date BETWEEN (CURDATE() - INTERVAL 1 MONTH) and (CURDATE() + INTERVAL 2 MONTH)
#6

Nono, far as I see this post is about the search in a Grid so it should be posted under Grid, there’s a special application Search as well just like Grid. Don’t ask me what you can do with it tho, never used it myself.
Most people won’t really notice where you put it anyways btw, quite certain most people just use the “What’s new”-function.

#7

could i do this with “sc_redir” - would be a redirct to the same grid but … somthing like

l

sc_redir(grid_event, start_date = ????

thanks rob

#8

you got it man…

the use of global variables… try to do something like this…

modify your SQL to do something like this…


SELECT *
FROM   events
WHERE [g_Condition]

where [g_Condition] is your change factor =P

in your onApplicationInit Event… use something like


if ( [g_Condition] == "" )
{
    [g_Condition] = 1;
}

that will modify your query brigin all the records… create a button in your toolbar and in the code you can do somehting like


sc_redit( 'myGrid', g_Condition = 'start_date BETWEEN (CURDATE() - INTERVAL 1 MONTH) and (CURDATE() + INTERVAL 2 MONTH)');

```php


you c?... its easy... uust create another button "show all" for example and use the same php code but make g_Condition = '1' and thats it... you got it all in one single pretty looking grid.... fuck your are good man.
#9

Thanks - Il give it a try an report back - happy eastern -
Go skiing with my kids first :slight_smile:

#10

Here is what i did and it almost works -

  1. change SQL
SELECT 
*
FROM event
WHERE [date_condition]
ORDER BY start_date DESC

WORKS!! I’m asked to define [date_condition] and It works with both - “TRUE” and “start_date BETWEEN (CURDATE() - INTERVAL 1 MONTH) and (CURDATE() + INTERVAL 2 MONTH)”

  1. set global Variable

I set the default value of [date_condition] in the MAINMENU , because I read this in the forum

" … useually advise to create all global variables in one spot, i.e. in the logon application if you have one. It prevents problems like these and it allows a better documentation in code."

I also had some troubles with in/out settimg of the global variable but finally it works fine!!

  1. Button “all”
    I tried
sc_redir( 'grid_event', date_condition = 'TRUE');

with a button “all” and it also works like a charm

  1. Button “intervall”
    I created a Button “+2/-1” and the code
sc_redir( 'grid_event', date_condition = 'start_date BETWEEN (CURDATE() - INTERVAL 1 MONTH) and (CURDATE() + INTERVAL 2 MONTH)');

This is NOT working , must be a Typo , where is it?

Thansk rob

#11

to avoid reloadin the DB I tried also

if ([date_condition] != 'TRUE') {
	sc_redir( 'grid_event', date_condition = 'TRUE');
	}

This is also NOT working -
thanks for any help
Rob

#12

I have done it like that an it works

$date_condition = "start_date BETWEEN (CURDATE() - INTERVAL 1 MONTH) and (CURDATE() + INTERVAL 2 MONTH)";
$date_condition_description = "<span style=\"color:red\">!FILTER +2/-1 ACTIVE!</span> - Only events 2 month in the future and 1 month in the past are shown";

[date_condition] = $date_condition;
[date_condition_desc] = $date_condition_description;

sc_redir( 'grid_event', $date_condition);
#13

well… gj man keep it up and you’ll finish your project in no time.

Regards

#14

Hi

for me this task is solved - unfortunately i cannot change the titel by myself - …

Anyway probably someone is interested in my solution - thanks for the help guys -

Instead of Buttons I use a “submenu” since I have a lot of “predefined searches” or “filters” for the ladies in the office
you can see the result here

http://development.mitsm.de/EduMS/menu_main_edums_education_management_system/

not every menu item is working at the moment but that is only “extra work”

one advantage working with a submenu instead of buttons is that the code is placed in one place - event -> onexecute -
here is a short snippet of the code -

if ({sc_menu_item} == "item_6")
	{
	$event_date_condition = "TRUE";
	$event_date_condition_description = "ALL events in the future and past are shown.";

	[event_date_condition] = $event_date_condition;
	[event_date_condition_desc] = $event_date_condition_description;

	sc_redir( 'grid_event', $event_date_condition);	
	}
if ({sc_menu_item} == "item_8")
	{
	$event_status_condition = "event_status_id != 6";
	$event_status_condition_description = "</br><span style=\"color:red\">! FILTER - STATE ACTIVE !</span> - Only events that are NOT in status \"canceled\" [6] are shown";

	[event_status_condition] = $event_status_condition;
	[event_status_condition_desc] = $event_status_condition_description;

	sc_redir( 'grid_event', $event_status_condition_description);	
	}

Thanks to kafecadm for the help

rob

PS [event_date_condition_desc] and [event_status_condition_desc] are displayed in the header of the grid so everyone can see which FILTER is active