Stonefield Query Version 3.2 Build 3062

We posted updated versions of the Stonefield Query SDK, Stonefield Query for Sage Accpac ERP, and Stonefield Query for Sage Pro ERP today with the following changes.

All Versions

• An advanced layout report with a link on a field no longer gives an error when you click the linked field in the Preview window.
• Using the RunSQL function in an advanced layout report no longer gives an error if the SQL statement passed to the function includes a table that isn't in the data dictionary.
• Converting reports from version 2.4 or earlier now properly handles corrupted reports.
• A bug which s0metimes caused the totals column in cross-tabulation reports to show the wrong totals when output to HTML Table Format was fixed.
• A custom SQL statement containing an aggregate function such as COUNT(SomeField) AS SomeField is now handled properly.
• Linking on a date field no longer gives an error.
• An error that sometimes occurred when perf0rming a multi-database query was fixed.
• A bug that caused an error message with an older license file was fixed.
• You no longer get an error when previewing a report called from another application.
• A bug in the Schedule Report Wizard using a weekly schedule on Windows XP was fixed. Also, the scheduler will no longer give an error if the time for the schedule is between 12:00 AM and 12:59 AM or 12:00 PM and 12:59 PM
• You no longer get an error if the Source Database field is the first one in a report.
• Stonefield Query now correctly handles a couple of obscure cases. The first is where a field delimited because its name contains illegal characters (such as [ID-TEST]) that exists in more than one table is involved in the output expression of calculated fields in those tables and both calculated fields are included in the same report; in that case, the SQL statement was erroneously determined as Table1."ID-TEST" AS ID_TEST, Table2."ID-TEST" AS "ID-TEST"_A. The second is when a report has at least two tables and one of the table names starts with a digit (for example, [1099Categories]) and Stonefield Query is configured to perform joins; this case formerly gave an "alias not found" error.
• The Test button in the Manage Databases dialog no longer crashes if a DSN for a different application was chosen but instead gives a warning message.
• The Import Report function no longer gives an "invalid import file" error with older import files.
• An advanced layout report that's linked to another report no longer gives an error when you click the link after importing both reports.
• Stonefield Query now passes the correct value to a grandchild report that's linked to a child report that's passed the value of a filter condition from a parent report.
• A field name that's a reserved word (such as [LINENO]) no longer causes an error when it's used in the output expression of a calculated field.
• The Value to Pass column in the grid in the Link step of the Field Properties dialog now shows the field caption rather than its heading, so it no longer displays "<BR>" in fields that have multi-line headings.

Sage Pro ERP

• A bug that gave an error the first time you connect to a Pro SQL Server database was fixed.
• Stonefield Query now supports companies with only a single Pro module installed.
• Clicking the Values button for the Record Type field in a current + history table now properly displays "Current" and "History" in the list.
• The caption for ARCSUM was changed from Customer History File to Customer Sales History because another table has Customer History File as its caption.
• Clicking the Values button for a field in a SQL Server database now works correctly.
• "Drop Shipment" is now available as one of the values for SOTRAN.SOTYPE.
• All YEARPRD fields now display the full seven digits.
• VFP runtime files are now placed in the Stonefield Query program folder to prevent an issue with different service pack releases of these files.
• Clicking the Close box for the Advanced Report Designer no longer gives a warning that you must close the Advanced Report Designer but instead actually closes the Advanced Report Designer.

Sage Accpac ERP

• The Accounts table now only appears once in the General Ledger module and also appears in all other modules when "All" is selected.
• Stonefield Query now properly handles optional fields for record that have quotes in their key fields (for example, "DOUG'S" in the Customer ID field of the Customers table).
• The captions of several fields were made more understandable.
• Optional fields in CPCHKD and UPCHKD are now handled properly.

SDK

• The installer now installs the version 3.2 SFQUERY.PDF rather than the 3.1 version.
• Adding a new empty database no longer makes it the main database unless it's the first database added.
• You no longer get an error in the Configuration Utility when you select an empty database.

Localization

• Typos in some Dutch translations were fixed.
• The title for the Reports Explorer window and the "Available to" setting on the Information page are now localizable.
• The Advanced button in the report wizards now sizes itself properly when localized.

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'
else
* do whatever is necessary
endif

To open a specific address in Google Maps all we need is the base Google Maps URL (http://maps.google.com/maps?&hl=en&q=) 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
DECLARE INTEGER ShellExecute ;
IN SHELL32.DLL ;
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)
return 'PROPERTY.PROPERTYID'
else

* 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 = 'http://maps.google.com/maps?&hl=en&q=' + lcLocation

* Pass the completed URL to ShellExecute
* ShelleExecute Parameter list: http://support.microsoft.com/kb/238245
ShellExecute(0, 'Open', lcURL, 0, 0, 5)

endif

* Restore the Original Work Area
select (lnSelect)

endif

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.