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.

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.

After a week or so I started looking through the applications I work on and found some issues with some of the fonts; they were too big in Windows 7.  Now, if you went through a Windows upgrade before this may come a no surprise; I remember some issues going from Win95 to Win 2000 to Win XP back in the day.  Here is an example of what I’m talking about.

Now these buttons had MS Sans Serif font sized at 9 point.

So I tasked myself with going through each window/visual object in the application libraries to check for this.  The last thing I want to have  happen is my apps to go to customers looking like crap.

Lo and behold you don’t need to do this.  The answer is a setting in Windows.

Right Click on the desktop:

Choose ‘Personalize’

Choose ‘Display’

Make sure you are using the default setting.

Now things are fine:

Now this seemed to only be an issue with MS Sans Serif  so I’m not sure if there are other fonts to be concerned with or not.

Reportevent External Function and Eventcreate command line command

You can use the command shell to do this too but this has the drawback of not being able to assign the source of the event.

First you need the following External Functions:

Function ulong RegisterEventSource ( &
	ulong lpUNCServerName, &
	string lpSourceName &
	) Library "advapi32.dll" Alias For "RegisterEventSourceW"

Function boolean ReportEvent ( &
	ulong hEventLog, &
	uint wType, &
	uint wCategory, &
	ulong dwEventID, &
	ulong lpUserSid, &
	uint wNumStrings, &
	ulong dwDataSize, &
	string lpStrings[], &
	ulong lpRawData &
	) Library "advapi32.dll" Alias For "ReportEventW"

Function boolean DeregisterEventSource ( &
	ref ulong hEventLog &
	) Library "advapi32.dll"

Then in PowerScript

integer li_messagelevel
string ls_errmsg[]
string ls_messagesource
ulong lul_eventsource
// the event message is here
ls_errmsg[1] = "Event Log Entry " + String(now(), 'hh:mm:ss') 
// setting the source of the message
ls_messagesource = this.classname()

lul_eventsource = RegisterEventSource(0, ls_messagesource)
IF IsNull(ls_eventsource) THEN ls_eventsource = -1
// set the level of the message
li_messagelevel = 4 // Information,  also 1 = Error, 2 = Warning

IF lul_eventsource > 0 THEN
	// write to the log
	ReportEvent(lul_eventsource, li_messagelevel, 0, 0, 0, &
        UpperBound(ls_errmsg), 0, ls_errmsg, 0)
	DeregisterEventSource(lul_eventsource)
END IF

Viewing the Log

Per the MSDN documentation, if the source name cannot be found the event logging service uses the Application log. Although events will be reported , the events will not include descriptions because there are no message and category message files for looking up descriptions related to the event identifiers.

Thanks to Roland Smith for most of the code for this functionality.

You are also able to use the ‘EVENTCREATE’ command line command.

From the Microsoft Technet page (found Here):

Enables an administrator to create a custom event in a specified event log.
Syntax
eventcreate [/s Computer [/u Domain\User [/p Password]] {[/l {APPLICATION|SYSTEM}]|[/so SrcName]} /t {ERROR|WARNING|INFORMATION|SUCCESSAUDIT|FAILUREAUDIT} /id EventID /d Description
Top of page 
Parameters
/s   Computer   : Specifies the name or IP address of a remote computer (do not use backslashes). The default is the local computer.
/u   Domain \ User   : Runs the command with the account permissions of the user specified by User or Domain\User. The default is the permissions of the current logged on user on the computer issuing the command.
/p   Password   : Specifies the password of the user account that is specified in the /u parameter.
/l { APPLICATION | SYSTEM } : Specifies the name of the event log where the event will be created. The valid log names are APPLICATION and SYSTEM.
/so   SrcName   : Specifies the source to use for the event. A valid source can be any string and should represent the application or component that is generating the event.
/t { ERROR | WARNING | INFORMATION | SUCCESSAUDIT | FAILUREAUDIT } : Specifies the type of event to create. The valid types are ERROR, WARNING, INFORMATION, SUCCESSAUDIT, and FAILUREAUDIT.
/id   EventID   : Specifies the event ID for the event. A valid ID is any number from 1 to 65535.
/d   Description   : Specifies the description to use for the newly created event.
/? : Displays help at the command prompt.

In PowerScript:

// command line event log
integer li_rtn
string ls_msg, ls_eventmsg
ls_msg = 'Command Line Message'

 ls_EventMsg = 'EVENTCREATE /T ERROR /ID 100 /SO "My Application" /l APPLICATION /D "' +ls_msg + '" '
 li_Rtn = Run (ls_EventMsg,  Minimized!)

 IF li_Rtn <> 1 THEN
	messagebox('event log', 'error loging msg')
 END IF

And the message in the Event Log viewer

To use this the user must be an administrator on the machine or you have to provide the id/password combination of one.

Thanks to Kyle Griffis for this tip.

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.

Using the techniques I’ve described in earlier posts, I created an interop project, dropped the webcam object on it, and exposed various methods and properties I needed to make the thing work. Testing on my PC worked fine; issues arose when I tried to register the thing on another PC.

The deployment of this component involves two dll files, one for the web camera control and the other for the interop wrapper control that the webcam sits on. I’m sure there are ways to incorporate the one into the other without the need for two dlls but that’s something to tackle later.

The web camera control project was set up as a standard class library. Since the second project I started with utilized the “VB6 Interop UserControl” template, it was already marked as COM visible when it was created. Once the web camera project was built I put a reference to it into my second project and made sure the setting to ‘copy local’ was set.

Once the project was complete and built, I could place the control onto a window in PowerBuilder and use it without problems. When I go to register the dlls on a separate PC (Windows 7 64 bit) I’m going to use the regasm utility. I copy all the files from the second project \bin folder to the PC then, from a command prompt, execute “Regasm webcamera.dll”.

This gives me the reply: RegAsm : warning RA0000 : No types were registered.

I do some searching on the internet and MSDN I find and set up the following in my projects.

Flag the Web Camera Control project to be COM visible. See the following:

Sign the assembly. You can create the strong name key file from the dropdown.

Do this for both projects (each has its own key file).

Copy all the files from your project’s output folder to the PC you wish to register the control on and execute the following for each project:

regasm PBWebcam.dll /codebase /tlb: PBWebcam.tlb
regasm WebCameraController.dll /codebase /tlb: WebCameraController.tlb

Leaving out the “/tlb” section will register the control, and you can see it in PowerBuilder, but you won’t have access to any methods/properties on the control. For further information on regasm go here.  You can avoid some of this mess by putting the dll into the Global Assembly Cache (GAC) but that’s a whole different can of….

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
...