Stonefield Query 3.2 in Beta

Stonefield Query 3.2 is now in beta. Here are some additional changes we made this week:

• The Desktop Image configuration property was removed since Stonefield Query no longer has a background window to display it in.
• The SQApplication object has a new CanEditReports property you can set to False to prevent the user from creating or editing reports under certain conditions (for example, perhaps demo users should only run existing reports).
• The Import Report function now handles invalid files better.
• You now get a warning message if you open a report in the Advanced Report Designer that has an image file that no longer exists or has been moved to a different folder. The image is replaced with a placeholder that you can edit to correct the file location.
• The Configuration Utility no longer terminates after you've activated it.
• Changing the connection settings for a database from ODBC to VFP no longer gives a Locate DBCXREG dialog when you refresh the database.
• The properties page for a database in the Configuration Utility now includes settings for Add All Tables to the Data Dictionary, Include Views, and Prompt for Subtables. Formerly, these settings were only available in the Add Database dialog, making it difficult to use different settings when you refresh the database.
• Including two enumerated fields with the same name (for example, RECTYPE from two different tables) in a report no longer displays blank values for the second field.

Additional Stonefield Query 3.2 Changes

Stonefield Query 3.2 is due for release on March 1, 2008. This week, we made some of the last changes before testing begins:

• Stonefield Query no longer has a background screen. All dialogs now appear directly on your Windows desktop and can be moved around independently.
• The report wizards are no longer modal. This means you can modify more than one report at a time if desired.
• Line spacing is now correct in labels that have fields in larger fonts.
• You no longer get an error if you choose Copy or Edit from the File menu when the Template Editor is open.
• You can no longer open multiple instances of the Template Editor.
• The correct controls are now displayed in the Options page of the Reports Explorer when it is the current page and you change from a Label report to a different type or vice versa.

The Latest Build

We added several new features and fixed a number of bugs in the last three weeks.

• The Cross-Tab, Label, and Chart wizards now have an Advanced button that brings up the same dialog available in the Quick Report Wizard to specify TOP value, customize the SQL statement for a report, or specify code to execute at certain places for the report.
• You can now specify that a field should go into the group header for another field. For example, suppose you group on City from the Customers table but want to show the Region in the group header as well. If you turn on the Include all fields in same table in group header option in the Group page in the Field Properties dialog, all fields in the Customers table will appear in the group footer, which isn't what you want. Instead, turn the new Include this field in the group header for setting on and select City from the drop-down list to place just City and Region in the group header. Since all fields that appear in group headers now appear in summary reports, Region will no longer be excluded from such a report.
• The Options dialog has a new Locations page where you can easily change the location where reports are stored.
• The Setup dialog now prompts you for your email settings. These are needed to email reports. However, many people don't realize they need to set these settings in the Options dialog so they can't email reports.
• You can now select specific reports to export or export all or some of the reports in a specific folder. The Export All Reports function in the File menu was renamed to Export Reports; selecting it now displays a dialog from which you can select which specific reports or folders to export. The Export Report function in the File menu was renamed to Export Selected Report.
• You can now edit the security properties for a folder by selecting the folder and clicking Edit in the Reports Explorer.
• The Information page in the Reports Explorer now show what groups the selected report or folder is available to.
• Importing a report that uses a data group that doesn't exist any more (for example, it was renamed) no longer makes the report invisible but instead resets the data group to All.
• Renaming a user in the Maintain Users and Groups dialog automatically moves all their reports to the new name.
• The Commonly Used Expression drop-down in the Expression Builder now only shows common expressions that match the data type of the expression being built. For example, the common date expressions now only appear for date expressions. Also, a new First Day of Previous Month expression was added.
• A splash screen can now appear when Stonefield Query starts up if you create an image named SPLASH.BMP, JPG, JPEG, or PNG that you want displayed in the splash screen.
• You can now distribute customized Standard and Elegant templates in the Sample Templates folder. Previously, the internal versions of these templates were always used.
• Returning False from the DataEngine.GetCustomMetaData script now terminates Stonefield Query so you can close the application if you detect a problem in such a script.
• Grand totals at the end of the report no longer appear on a second line if they will fit on the first one.
• The Generate Setup function in the Configuration Utility no longer gives an "Inno Setup is not installed" error if you installed only the Inno Quick Start Pack and not the full Inno Setup.
• A bug that gave an error when running a report with a filter from a text data source was fixed.
• A bug that prevented case-insensitive filters when the filter Compare to setting is set to Field was fixed.
• A bug that sometimes caused the login dialog to appear off-screen so you couldn't see it was fixed. For details, see http://doughennig.blogspot.com/2008/01/dont-save-if-minimized.html.
• A bug that caused a field needed by a link action to appear in both the available and selected lists in Step 2 of the Quick Report Wizard was fixed.
• A bug that sometimes caused a filter condition to be mangled if it contained a single quote (for example, SomeField = 'Barney's Store') or a pair of empty quotes (for example, SomeField = '') was fixed.
• The cFilter property of the loReport object is now set to the filter for the report.

Displaying Company Header Information

 

Yesterday, a customer asked me how to handle a single record "Company" table. The single record in this table contains information about the Company that is using the software, like the Company Name and the Address. So, if we here at Stonefield were looking at our entry in that table, the Company Name field would contain "Stonefield Software Inc.", and the Address field would be "2323 Broad Street".

The question becomes, "How do we get this information into our reports?". When I first ran across this situation, my initial reaction was to simply create a join/relationship with that table and every other table in the database. There are a couple of problems with doing it this way:

• We don't have a field in the table to join on, so the join would need to be an expression that evaluates to true.
• If the database has a large number of tables, creating a relationship with all of them would be impractical.
• Having to include this company information in the data selection step of Stonefield Query's report wizards would be very clumsy. The information would repeat itself over and over again at the detail level. Information like this generally belongs in the header of a report.

A better way to handle this situation is to use a script that looks this information up for us and stores it in a global variable. Here is an example of such a snippet from a GetCustomMetaData script:

*variable declarations 
public gcAddress, gcCompanyName 
local lnSelect, loDatabase, lcSelect 
*store the current work area 
lnselect = select() 
*get a reference to the database we are currently connected to 
loDatabase = SQApplication.DataEngine.Databases.GetMainDatabase() 
lcSelect = "select CompanyName, Address from Customers" 
*send the SQL statement to the database 
loDatabase.ExecuteSQLStatement(lcSelect, .NULL., 'curCompanyCursor') 
*store the address and company name we just retrieved 
gcAddress = curCompanyCursor.Address 
gcCompanyName = curCompanyCursor.CompanyName 
*restore the workarea 
select (lnselect)

Once this is done, we have two global variables (gcAddress and gcCompanyName) that we can reference from the output expression of a field object in the advanced report designer. We could even reference these from a template, and have company information like name and address appear in the header of every report by default.

This Week's Build

Here are some new features implemented in the past couple of weeks:

• If you turn on the Include All Fields in Same Table in Group Header setting in the Field Properties dialog, you can now specify that all fields in the group header should appear on the same line rather than each on its own line by turning on the new Place All Fields in Group Header on Same Line setting.
• You can now specify multi-line headings for fields, both in the Configuration Utility as a default for the field and in the Field Properties dialog for a field in a particular report.
• You can now press the Esc key to stop a report run at any point rather than just while the pages are being rendered as was the case in earlier versions.
• You now get a warning if you run a report that has no filter, with an option to run the report anyway. This prevents a long-running report because you forgot to create a filter. An new setting in the Options dialog allows you to turn this warning off.
• You now get a warning if you have a custom SQL statement for a report and filter on a field in a table that isn't specified in the SQL statement. The reason is because the filter condition can't be just added to the WHERE clause of the SQL statement since its table isn't specified in the FROM or JOIN clauses.
• The View Field Contents function in the Configuration Utility is now disabled for calculated fields with the Stonefield Query Expression setting turned on, but now works properly if that setting is turned off.
• A new GetReportFilter method of the loReport object can be used to display the filter conditions for the report.

ACCPAC Query is Back!

In 2000, we created a customized version of Stonefield Query for the ACCPAC accounting system. It became so popular with ACCPAC resellers and customers that ACCPAC International, the company that makes ACCPAC, licensed it from us as one of their ACCPAC Options products. We renamed it to ACCPAC Query and private labelled it for them so it had their company name, logo, and activation mechanism. Later that year, we created a similar version for the ACCPAC Pro Series accounting system, formerly known as SBT.

Fast forward seven years. By a mutual decision, Sage Software (which now owns ACCPAC International) has returned all rights to ACCPAC Query back to Stonefield Software. Both companies are very happy about this, because it allows Sage Software to concentrate their support and marketing resources on their own products (they've been returning almost all of their "OEM" products back to the original developers for this reason) and we are now free to develop and market the product as we see fit.

For ACCPAC Query users and resellers, this is definitely a great thing for a variety of reasons:

• You'll get support directly from Stonefield Software. Who knows software better than the developers? As you can see from the testimonials, Stonefield is renowned for providing timely, superior technical support for all of our products.
• More frequent updates. The release of new versions of ACCPAC Query is no longer tied to the release cycle of Sage products nor is it held up by the limited resources of the Sage product development team. For example, a new version of ACCPAC Query, providing powerful new features such as charting and drilldowns, is planned for March 2008. This version wasn't due for release from Sage Software until Q3 2008.
• We have a new reseller program Sage Business Partners will love, including better margins and support. We're in the process of staffing a new Channel Account Manager for Accounting Systems; once that's done, he or she will contact all Sage Business Partners with details.
• More and better training. We're in the middle of revamping training for Stonefield Query and plan to roll out a number of new courses that go more in-depth into the program early in the new year.

If you're a Sage Business Partner or Sage ERP or Sage Pro ERP user, contact us now to find out about the new ACCPAC Query and how it can help your business. Call Mickey at 800-563-1119 today.

This Week's Build: SDK

Most of the work over the past couple of weeks has been finishing the new negative query feature and bug fixes, although we did sneak in a couple of new features as well:

• A new Prompt for Subtables option in the Add Database dialog of the Configuration Utility allows you to indicate whether you are prompted for subtable names when multiple relationships are found between tables or a table has a self-join. While this is turned on by default, you may wish to turn it off if you don't plan on dealing with multiple relations and don't want to be prompted for them every time you refresh the data dictionary.
• A new Include Database in Header report option adds the database name to the report header. This is handy if you have multiple data sources and want to show which one a particular report run is for.
• Opening a project in the Configuration Utility makes it the default one. This still also happens when you close it, but now you don't have to close the Configuration Utility to make it the default. This is handy if you switch between multiple projects a lot like we here at Stonefield do.
• You no longer get an unexpected error but instead a warning message if you try to report on two tables that aren't related in a very large (i.e. hundreds of tables) data dictionary. (In technical terms, we prevent blowing the call stack when the recursive code trying to find a relationship gets too deep.)
• You can no longer resize the Reports Explorer window so small that the reports list is hidden.
• The Registration dialog now gives you a warning if you click the Print button without first filling in the serial number, and the prompt in the dialog tells you that value is needed.
• The hotkey for the Advanced Layout function in Step 5 of the Quick Report Wizard was changed from "V" to "D" so it doesn't conflict with the Preview function.
• Renaming a database added to the Configuration Utility through an OLE DB provider no longer causes a new database with the original name to be added to the data dictionary when you refresh.
• Renaming a table, subtable, or virtual table when you have more than one data dictionary table no longer results in records for both the old and new names.

New Versions of Stonefield Query for AccountMate

AccountMate recently released version 7.3 of AccountMate for SQL (and express). This release included a handful of new fields, as well as a new method of connecting to the database (DSN-Less connection). There is a new version of Stonefield Query for AccountMate SQL up on our website that supports these changes.

In addition to this, AccountMate also provided an upgrade path for users using the LAN version to upgrade their Payroll module. Essentially, the rest of the database continues to exist as it did before, but the Payroll tables reside on a SQL Server instead. The new version of Stonefield Query for AccountMate LAN supports this type of Payroll module.

This Week's Build

Work last week focused mainly on the negative query feature I blogged about last week. We also did a number of bug fixes and added a couple of new things:

• Filtering on a calculated field that has a different output data type than how it's actually stored (for example, a character field you want treated as a logical field) in a custom SQL statement now works correctly.
• Using the RunSQL function in the Advanced Report Designer with a SQL statement containing a join now works correctly.
• Previously, if you used the zoom feature for the code for a script (right-click and choose Zoom), then selected a different script, then closed the zoom window, the selected script's code was overwritten with the code in the zoom window. Now, the code is saved to the originally selected script.
• The Subtable Filter of a subtable has to contain a reference to the subtable, not the original table. To prevent errors, when you fill in the filter, the Configuration Utility automatically converts the original name to the subtable name in case you entered the wrong name.
• The DataEngine object has a new GetFieldsFromSQLStatement method that, similar to the GetTablesFromSQLStatement method, returns a collection of the fields involved in the specified statement.

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.