In the Getfocus event of the datawindow:

long ll_col = 1
long ll_pos, ll_x, ll_y, ll_tab
long ll_curr_x, ll_curr_y
string ls_list, ls_piece, ls_column, ls_setting

// user back tabbed to get here
IF Keydown(keyshift!) AND Keydown(keytab!) THEN
	// objects on datawindow
	ls_list = THIS.object.datawindow.objects + '~t'
	DO WHILE ls_list <> ''
		ll_pos = POS(ls_list, '~t')
		IF ll_pos > 0 THEN
			ls_piece = MID(ls_list, 1, ll_pos - 1)
			ls_list = TRIM(MID(ls_list, ll_pos + 1))
			IF TRIM(ls_piece) <> '' THEN
				ls_setting = THIS.Describe(ls_piece + '.Type')
				IF UPPER(ls_setting) = 'COLUMN' THEN // only look at columns
					ls_setting = THIS.Describe(ls_piece + '.Visible')
					//check for visible only	
					IF ls_setting = "1"	THEN			
						ls_setting = THIS.Describe(ls_piece + '.X')
						ll_x = long(ls_setting)
						ls_setting = THIS.Describe(ls_piece + '.Y')
						ll_y = long(ls_setting)
						ls_setting = THIS.Describe(ls_piece + '.TabSequence')
						ll_tab = long(ls_setting) //check tab sequence
						IF ll_tab > 0 AND ((ll_y = ll_curr_y AND ll_x > ll_curr_x) OR (ll_y > ll_curr_y)) THEN
							ll_curr_x = ll_x
							ll_curr_y = ll_y
							ls_column = ls_piece
						END IF
					END IF
				END IF
			END IF
		END IF
	LOOP
	IF TRIM(ls_column) <> '' THEN
		THIS.setrow(long(THIS.Object.DataWindow.LastRowOnPage))
		THIS.setcolumn(ls_column)
	END IF
END IF

On any given datawindow (if you put this in an ancestor) you need to make sure that any columns you place ‘out of view’ are either invisible or have a tab sequence of zero.

string ls_desc
ls_desc = dw_parts.describe("evaluate('lookupdisplay(partTypeID)'," + string(ll_row))

Where ‘ll_row’ is the row number in dw_parts datawindow from which you want the description.

The existing windows normally had your typical ‘search’ datawindows which defaulted to the ‘default’ site assigned to the user and then they could select a location from a drop down datawindow.

Example ‘post open’ event on window code:

datawindowchild ldwc

long ll_rows

dw_search.getchild('loc_code', ldwc)
ldwc.settransobject(SQLCA)
ll_rows = ldwc.retrieve(gs_user_id)
dw_search.object.loc_code[1] = gs_default_site

Example ‘itemchanged’ event on dw_search code

this.getChild("site_to", ldwc)
ldwc.setTransObject(SQLCA)
If ldwc.Retrieve(long(data)) = 0 then
  ldwc.insertrow(0)
End if

So each time the user changed the location value another retrieve was sent to the database.

I needed to add a site column to the search datawindow which would also be a dddw.  This meant that even more calls to the database would occur as the user changed the value in the new column.

My approach involved creating an nvo with two datastores, one for the sites and the other for the locations.  Each were populated when the window was opened (populated based on the user’s security settings).  Then when I needed to set up the drop down datawindow columns I used the following.

// all sites and locations for this user
ls_sites = invo_multisite.ids_site.object.datawindow.data
ls_locs = invo_multsite.ids_loc.object.datawindow.data

dw_search.getchild('site_code', ldwc)
ldwc.importstring(ls_sites)
dw_search.object.site_code[1] = is_site_code
ll_find = ldwc.Find('site_code = ' + string(is_site_code), 1, ldwc.RowCount() + 1)

dw_search.getchild('loc_code', ldwc)
ldwc.importstring(ls_locs)

ldwc.setfilter("site_code = '" + is_site_code + "' AND status = 'Active'"

ldwc.filter()  // only show locations associated with the chosen site

You need to remember to turn off Autoretrieve on the DDDW columns you are using for this.  You also need to have the datawindow object used in the nvo datastores the same as that used in the other datawindow dddw columns.  If you need to account for user access being changed during the sesssion, set up a timer in the nvo and periodically refresh the datastores.

Here is what you have to do:

The tooltip Enabled property must be TRUE.

The tooltip text must not be an empty string or NULL.

The tooltip duration must be greater than zero.

All of these are properties which can be set via expressions and/or modify statements.  Make sure they evaluate correctly as well.

I have updated the presentation files along with exports of the PB objects so that folks using versions prior to 12.5 can take a look at the code and the functionality it provides.  The new file is available here.

The examples presented cover some basic datawindow functionality techniques which can improve the ‘usability’ of an application.  Most of these can be considered ‘second nature’ in that many ‘modern’ applications do these sorts of things already.  Taking advantage of the power of the datawindow, these types of improvements can be made quickly and with very low risk to an already established user base.  What I mean here is since the ‘improvement object (IO)’ is inherited from a datawindow, all you need to do is to establish a reference between any datawindow already in your application and the IO, wire some events on the existing datawindow to corresponding events on the IO, and you have access to the new functionality.

Once you have the IO in place, it’s easy to add even more functionality to it and have those improvements flow into as many datawindows in your applications as you like.

In this version of the IO I have included ‘Type ahead’ functionality.  My earlier blog post on this is here.

Treeview like tooltips.  Blog post is here.

‘Mouseover’ effects.  Similar blog post is here.

And a ‘Universal Searcher’ tool which can be used not only on datawindows but also multi line edit, rich text edit, and treeview controls.

This is an extract from the session I presented at the SAP PowerBuilder Developers Conference 2012.

Now you might think that using the LibraryExport and LibraryImport methods will do the trick but you would be mistaken. A newly imported datawindow object keeps the same version as when it was exported.

To accomplish the task you need to use the datawindow CREATE method.

integer li_file, li
string ls_old_syntax, ls_importsyntax
// old datawindow syntax saved to a file
ls_old_syntax = 'C:\temp\d_oldstuff.txt'
// open and read the file
li_file = Fileopen(ls_old_syntax, TextMode!)
Filereadex(li_file, ls_importsyntax)
// remember to close the file
FileClose(li_file)
// check version of ls_importsyntax
IF POS(ls_importsyntax, 'release 12.5;') > 1 THEN
   // its already new, leave it alone		
ELSE
   // dw_new is object on the window
   dw_new.reset()
   dw_new.dataobject = ''
   // create the new datawindow object from the old syntax
   li = dw_new.create(ls_importsyntax, ls_err) 
   // now get the new syntax
   ls_new_syntax = dw_new.describe("DataWindow.Syntax")   
END IF

Now you can do whatever you want with the new syntax.

I was working on a generic data drill down control which has a filtering process to eliminate data based on a date argument. Since I also wanted this date value for other purposes (expressions) on the datawindow I decided to include it within the result set. There are a variety of ways to do the same thing but this is what I fixated on while figuring out the control’s functionality. Initially my SQL was something like this (MS SQL Server 2008):

...
,IsNull(part_name, 'No Part') AS part_name
,:adt_date AS svc_date
...

Everything is fine but during my testing I encountered the message: “Select Error: Column lists do not match.”

Hmmm…

Running a SQL trace gave this result for the query:

...
,IsNull(part_name, 'No Part') AS part_name
,NULL AS svc_date
...

So my date argument in this case was null. I could code in PowerBuilder to ensure the paramenter is always populated with a date but I preferred to following:

...
,IsNull(part_name, 'No Part') AS part_name
,IsNull(:adt_date, Getdate()) AS svc_date
...

What I initially coded was this:

ll_open_x = Long(adw.describe(as_colname + '.X') + &
 adw.describe(as_colname + '.height')) + 10

I run the window and my visual object is no where to be seen.

Looking in the debugger I see that ll_open_x is being set to 72270!

Fixed code is this:

ll_open_x = Long(adw.describe(as_colname + '.X')) + &
 Long(adw.describe(as_colname + '.height')) + 10

Since the Describe method always returns a string the values ’72’ and ‘270’ were simply being concatenated prior to being converted to a long.

So we look at the datawindow object on the application window they are having issues with. Must be something in an expression on the window which, when combined with some type of unusual data, results in a bad expression. The multiple messages comes from the window being refreshed on a periodic basis and, as long as the bad data exists, we get further messages.

Turns out there is nothing wrong with the datawindow or any of the expressions on it. The timer on the window was issuing a Find on the datawindow using data retrieved by some other datastore. Turns out a column used in the find was a descriptive column and the data contained a single quote. That caused the find string to be messed up resulting in the error.

Moral of the story is to check strings inserted into find expressions for quotes and insert a tilde to escape them (unless you don’t want to allow quotes in your strings in the first place).