Search: A value from a list box of month and year sorted by the latest month

Applications Grid / Reports
#1

Hi everyone,

I have a grid form and in the search criteria i have the month/year field. What i have to do is to show all months of the year starting by the previous one(ex: now we are in september… the list should start with august). So i think maybe i should use GETDATE() function… but i have no clue where to put it,as the events in the search menu are not enabled. Does it even take any event?? I am trying to come up with something that works but so far…no luck! If anyone has any idea i would be very very grateful!!

Thanks in advance,
Kind regards

#2

Re: Search: A value from a list box of month and year sorted by the latest month

Hi,
supposedly you are using mysql, one idea would be a “Stored Procedure”.
A start could be the following. This one delivers just the month as a number and the long name. So you have to alter it to fit your needs.


DELIMITER $$

CREATE PROCEDURE `getmonth`()
BEGIN
	DECLARE x INT;
	DECLARE sel_date DATE;
	SET x = 1;
	SET sel_date = CURDATE() - INTERVAL 1 MONTH;
	CREATE TEMPORARY TABLE IF NOT EXISTS month_list 
			(mon_nr INT, mon_text VARCHAR(10));
	DELETE FROM month_list;

	WHILE x <= 12 DO
		INSERT INTO month_list SELECT 
			MONTH(sel_date), 
			MONTHNAME(sel_date);

	    SET sel_date=sel_date + INTERVAL 1 MONTH;
		SET x = x + 1;
	END WHILE;
END

Call the procedure in the onScriptInit event of the search module (or wherever you need it):


sc_exec_sql("CALL getmonth");

Change the field type to “Select” and use the Automatic Lookup feature.


SELECT mon_nr,mon_text
FROM month_list

Hope this points you into the right direction.
jsb