Friday, December 7, 2007

Link Actions

Stonefield Query now supports Link Actions. This means that if a field on a report is marked with a Link Action then clicking on that field in the Report Preview will perform that action.

There are three built-in actions in Stonefield Query: Email, which launches your email program with an email address filled in; Web site, which navigates your browser to a specific URL; and Report, which runs the specified report.

You can also define custom actions by creating a Link Action script in the Configuration Utility. If you turn on the Link action setting for a script, Stonefield Query automatically includes the description contained in the Description setting in an Action drop-down list in the Link page of the Field Properties dialog.

As an example you may have a Stonefield Query project that reports on a Real Estate database. It would be very useful to add a Link Action where the user can click on a Property ID and have their web browser automatically open to Google Maps with a map of that Property displayed. Below is an example of how to do this but first there are a few rules that Link Action scripts must follow:

The code for a link action script must accept two parameters. The first is a field name and the second is the value of that field in the record the user clicked. Your code can do anything it wishes with these parameters, except if the first parameter is blank, this is a "discovery" call from the Field Properties dialog. In that case, the script must return a comma-delimited list of field names that should appear in the Action Parameter drop-down list in that dialog.

Here is a quick example:

lparameters tcFieldName, tuValue

if empty(tcFieldName)
return 'Customers.CompanyName'
* do whatever is necessary

To open a specific address in Google Maps all we need is the base Google Maps URL ( then we can concatenate our Address, City, State, Zip and Country fields to it. Google Maps is very forgiving so the order that you add the fields to the URL is not relevant and it is pretty good at finding the address even if some fields are missing.

To create a Google Maps Link Action: make a new script, check off the "Link action" option and fill in the Description text box with something like: Google Maps

Then, copy and paste the skeleton code below. The only section of code that you will need to alter is the TABLE.FIELDNAME value that gets returned in the discovery call and the function that looks up your specific Address, City, State, Zip and Country fields (here it is called GetLocationforGMaps).

* Google Maps Link Action Script
lparameters tcFieldName, tuValue
local lcURL, lnSelect, lcLocation

* Declare the ShellExecute function so that we can use it to open the
* users browser to our
specified URL
INTEGER nWinHandle,;
STRING cOperation,;
STRING cFileName,;
STRING cParameters,;
STRING cDirectory,;
INTEGER nShowWindow

* Return the field that will appear in the Action Parameter drop down
* when we Link on this field

* Change this to return your correct TABLE.FIELDNAME

if empty(tcFieldName)

* Save the current work area so we can restore it later
lnSelect = select()
select 0

* tuValue will contain the PROPERTYID that the user clicked on
* We need to use that to lookup the Address, City, State, Zip
* and Country fields

* The
GetLocationforGMaps() function will be specific to your project
lcLocation = GetLocationforGMaps(tuValue)

* Our lcLocation variable now contains the concatenated Address, City
* State,
Zip and Country fields separated by spaces
if not empty(lcLocation)

* Append the location info to the base Google Maps URL
lcURL = '' + lcLocation

* Pass the completed URL to ShellExecute
* ShelleExecute Parameter list:
ShellExecute(0, 'Open', lcURL, 0, 0, 5)


* Restore the Original Work Area
select (lnSelect)


There is no limit to what you can accomplish with a Link Action script. You could even have your script communicate with another running program and perform a certain action when a user clicks on a field.

For example, in Stonefield Query for Goldmine we have a Link Action where you can make Goldmine itself automatically move to a certain Contact when you click that Contact Name in the Stonefield Query Report Preview window. This is very useful while doing database cleanup.