PowerBuilder – Move Frequently Chosen Items to Top of DropDownDataWindow

Posted on Friday, January 7th, 2011 at 8:20 pm in

I’m looking at an application which basically records demographic information for a population. One of the fields listed is ‘primary language’ which has a typical dropdowndatawindow interface. I look at the options and BAM!, a huge alphabetical list (including Esperanto!). Now this application is generally used in the US so my guess is at least 50% of the time the entry will be ‘English’ with quite a few ‘Spanish’, ‘Arabic’, ‘Russian’, etc. depending upon the locality it is used in.

The ‘plain Jane’ dddw does allow you to jump to the first entry of a particular letter. There are a number of examples of code allowing for ‘Quicken style’ behaivior wherein the entry responds to all the letters typed in. I was thinking of a way to ‘float’ the most common items to the top of the entire list to make data entry a little less intensive.

My solution involves creating a table to log the most frequently chosen items, a stored procedure to update that table, and a datawindow object which joins to the frequently chosen table to set a sorting column. To make things even more interesting for myself I chose to do the database work in MySql.

The table definition is as follows:

CREATE TABLE most_used (most_used_id integer 
 , table_name varchar(125) NOT NULL
 , column_name varchar(125) NOT NULL
 , record_id integer NOT NULL
 , tally integer NOT NULL)

Note the ‘most_used_id’ column is set to AUTOINCREMENT and is the primary key of the table. One of the columns in this table is record_id and is set to be a numeric. A base assumption for this process is that the codes being tracked all have numeric key values. If this is not the case, changes will have to be made.

MySql stored procedure syntax is as follows.

 CREATE PROCEDURE usp_update_most_used(IN atable_name VARCHAR(255), IN acolumn_name VARCHAR(255), IN arecord_id INT)
     DECLARE count_used INT;
	 SET count_used = (SELECT IFNULL(tally,0) FROM most_used WHERE table_name = atable_name and column_name = acolumn_name and record_id = arecord_id);
	 IF count_used > 0 THEN
	    UPDATE most_used SET tally = tally + 1 WHERE table_name = atable_name and column_name = acolumn_name and record_id = arecord_id;
		INSERT INTO most_used (table_name, column_name, record_id, tally)
		VALUES (atable_name, acolumn_name, arecord_id, 1);
     SELECT count_used;
   END //
 -- MySql syntax for calling procedure from phpMySqlAdmin
 CALL usp_update_most_used ('state','state_cd',3)

Converting this to other DB syntax should not be too involved. Remember to set permissions on this as well.

I created a small sample application to demonstrate the functionality. In my sample database I have a ‘state’ table which contains entries for some of the states in the US. When you first run the application and click on the state dropdown, you see the familiar list in alphabetical order.

When I choose a State and then click ‘Update’ the previous choice is logged and brought to the top of the list. Closing and re-opening the application now shows the list has been modified. A simple expression on the background color of the column gives a visual indicator that the item at the top is not in ‘regular’ (in this case alphabetical) order.

Now as the list is accessed and the choice updated the list is dynamically changed to reflect the most used values. The example here would apply this change to all users on the system connected to the database. With a little modification you could have this apply to individuals, departments, branches, etc. based on whatever criteria you choose. You could even limit the resorting of the list to only those which have been chosen more than a specific number of times. To apply this functionality to other columns you simply need to change the values for ‘table_name’ and ‘column_name’. If there is somewhere you don’t want to apply it, don’t include the join in the datawindow SQL.

The code in the example application which applies the choice to the database is in the Update button clicked event and is as follows:

//update the most_used table with the state choice
//so the most chosen items go to the top of the list.
datawindowchild ldwc
integer li_rc
long	ll_id
string ls_filter
// if the row in the datawindow is using a code and not an ID, you need to 
// search for the row in the child datawindow to get the ID.
// in this example I could just use dw_1.getitemnumber(1,'state_id') to
// get the ID but I am doing the filter anyway.
li_rc = dw_1.getchild( 'state_id',ldwc)
ls_filter = "state_id = " + string(dw_1.getitemnumber(1,'state_id'))
li_rc = ldwc.setfilter(ls_filter)
li_rc = ldwc.filter()

IF li_rc > 0 THEN
	ll_id = ldwc.getitemnumber(li_rc,'state_id')
	// update the table reflecting the choice the user made
	DECLARE most_used PROCEDURE FOR usp_update_most_used 'state'
	, 'state_cd'
	, :ll_id

	EXECUTE most_used;
	IF SQLCA.sqlcode = -1 THEN
		messagebox('SQL', 'usp_update_most_used error~r' + SQLCA.sqlerrtext)
	CLOSE most_used;
// remove filter and reset the dropdown list
li_rc = ldwc.setfilter('')
li_rc = ldwc.filter()
ldwc.retrieve( )

The SQL for the datawindow which makes up the dropdown is as follows (MySql syntax):

select state_cd, state_name, IFNULL(tally, 0) as tally, state_id
from state
left join most_used on state.state_id =  
most_used.record_id and IFNULL(table_name, 'state') = 'state'
and IFNULL(column_name, 'state_cd') = 'state_cd'
order by tally DESC, state_name

Even though there is an order by in the SQL, add the same as a sort order on the datawindow object itself.