A Few More Changes

Last week, we blogged about the "last changes" in version 3.2. We actually did a few more in the past week: one new feature and the rest fixes for bugs found in beta testing.

• A new workstation installer, WSSETUP.EXE, is included in the application folder. This allows the user to install Stonefield Query on their system, then run WSSETUP.EXE from the server on each workstation to install just the runtime files required. This makes it easier for users to do workstation installs; some users found running the same SETUP.EXE for both server and workstation to be confusing, plus there are very few prompts in the workstation installer. It also installs the help file on the local drive to eliminate the security issue with CHM files on network drives.
• Stonefield Query now handles the TOP N clause in MySQL by changing it to a LIMIT N clause.
• If you have multiple action scripts, they're now shown in alphabetical order in the Link page of the Field Properties dialog. Also, a bug that prevented link actions from being saved when the link action script returns only a single field name was fixed.
• A bug that causes an error when you click the Values button in the Exclusion Condition dialog was fixed.
• A bug that caused an error when returning a cursor with no records from a Table.Select script was fixed.
• The OK button in the Locate dialog, which only appears if the TargetApp entry in DATA.INI points to an invalid folder, is now enabled after selecting a valid folder.

Improvements in Stonefield Query for GoldMine

In addition to all the new features and bug fixes in Stonefield Query (and if you've been following this blog, you know there are a lot of them), we made the following improvements in Stonefield Query for GoldMine:

• There are new Read, In History, and Attachments fields in the Mailbox table.
• The Key1 through 5 fields in the upper quadrant in GoldMine are now available in the Combined Main and Other Contacts table.
• You can now report on both the History and Mailbox tables at the same time. Also, if you filter on the History Date but don't filter on the Mailbox Date, the History Date is automatically used for Mailbox to prevent a lot of records from being retrieved and slowing down performance.
• The Create Group in GoldMine function now displays a progress bar, which is handy if there are lots of members.
• It better handles the GoldMine DBALIAS.INI file in different locations.
• An issue with filtering on the various Opportunity and Project tables using OR conditions with dBase tables was resolved.
• A bug that gave an error when converting from Stonefield Query version 2.4 or earlier was fixed.
• A bug that prevented custom fields in GoldMine from showing up in Stonefield Query if you upgraded GoldMine from version 4 at some point was fixed.
• The proper captions for fields in the Combined Main and Other Contacts table are now displayed.
• A bug that caused only the first 35 characters of the Web site to be displayed was fixed.

These improvements will be in version 3.2, due for release later this month.

Improvements in Stonefield Query for HEAT

In addition to all the new features and bug fixes in Stonefield Query (and if you've been following this blog, you know there are a lot of them), we made the following improvements in Stonefield Query for HEAT:

• The performance of queries involving a filter on a date field is now dramatically faster than before.
• New Elapsed Hours (Based on Help Desk Hours) and Elapsed Days/Hours (Based on Help Desk Hours) fields in the Calling Logging and Assignments tables show the elapsed hours for a call taking the hour of operation (rather than total elapsed time) into account.
• We eliminated the cause of duplicate fields in the Profile table.

These improvements will be in version 3.2, due for release later this month.

The Last of the 3.2 Changes

Release Candidate 1 of the Stonefield Query SDK is now available. It has quite a few changes since the previous blog post.

New/Updated Features

• Improvements were made in how link action scripts are handled, making it easier to do things such as link to Google Maps.
• The new Analyze Database function in the Tools menu (also available in the Setup dialog) goes through your database and marks only those tables and fields that contain meaningful data as "favorites". Step 2 of the report wizards will then have a new Show only favorites option. Turn this option on to show only those tables and fields marked as "favorites". Turn it off again to show all tables and fields. This feature is only available if you turn on the new Enable Table/Field Favorites configuration setting, and is particularly useful for large (hundreds of tables) data dictionary in which not all tables and fields may contain meaningful data.
• You can now drag a Stonefield Query import file (an SFX file) from Windows Explorer to the Reports Explorer to import that report. You can also drag an FRX file from Windows Explorer to the Reports Explorer to bring up the External Report Wizard for that report.
• The Field Properties dialog for the Label Wizard has a new Display this field in the report setting. Turn this setting off if you want the field included in query (for example, you want to sort on it) but not display it in the report.
• Step 3 of the Chart Wizard now displays the axis (X or Y) in the caption for the appropriate boxes, making it clear where a field will be graphed.
• The Reports Explorer and Help menu now have a Manual function that displays the content of a PDF with the same name as the executable (SFQUERY.EXE by default). These functions are only visible if that file exists.
• The close box on the Report Wizards is now functional.
• The report wizards and Template Editor now appear in the Windows Task Bar when they're open so they're easy to locate when you have a lot of windows open.
• You can now have multiple code zoom windows (right-click on the code box when a script is selected and choose Zoom) open in the Configuration Utility.
• A new Application.ReportsExplorerReady script is available.
• The new Execute method of the SQApplication object can be used to launch an application.
• The Template Editor now has a shortcut menu.

Bug Fixes

• Improvements were made in all dialogs so keyboard-only users (including disabled users) can move from one control to the next correctly.
• A bug in how the project list in the Configuration Utility (displayed by clicking the arrow beside the Open Project button in the toolbar) is filled when opening projects was fixed.
• The size and position of the Reports Explorer are now saved when you choose Exit from the File menu.
• A bug that prevented a report from being exported properly immediately after renaming it in the Reports Explorer was fixed.
• A grey box that appears when you clicked the Available To list in the Information page of the Reports Explorer no longer appears.
• Closing Stonefield Query while multiple report wizards are open now works correctly, including the case where, when prompted if you want to cancel a report, you choose No. Also, the "cancel this report" dialog that appears now displays the name of the report so you can tell which report is being closed when multiple report wizards are open.
• You no longer get a warning that a report has no filter if it has no filter but does have an exclusion filter or a TOP N clause.
• Stonefield Query no longer stubbornly insists on downloading an update if you choose No in the Download Update dialog.
• The Prompt for Subtable option no longer appears in the Add Database dialog when you choose to create an empty database.
• A difficult-to-reproduce bug that caused an error immediately after adding a calculated field or virtual table in the Configuration Utility was fixed.
• The Test button on the properties page for relations in the Configuration Utility is now disabled if the relationship information isn't complete.
• A bug that caused an error if you try to sort on a calculated field that derives its value from a memo field was fixed.
• A bug that caused an incorrect warning that a filter condition contains fields that aren't in the query when you have a custom SQL statement involving a subtable (got all that?) was fixed.
• Ctrl+O is no longer the hotkey for the New Folder function in the File menu since that hotkey is usually reserved for an Open function in most applications.
• A bug that prevented the code zoom window from being restored to the correct location if you had two monitors and the window was previously open on the second one but you only have one now was fixed. (Ok, that one was really obscure!)
• If it exists, the value of the Desktop Image configuration setting is now moved to the Logo Image setting if that setting is blank.
• Changing the value of the Advanced User setting for yourself in the Maintain Users and Groups dialog now takes effect immediately rather than after restarting Stonefield Query.
• Downloading individual files during a product update now works correctly.
• A bug that cut off the link status display in the preview window was fixed.
• A bug that caused the Show All Fields option in the Filter Condition dialog to disappear after clearing the filter was fixed.
• A bug that allowed you to click the OK button in the Login dialog a second time after the DataEngine.GetCustomMetaData script returned .F. (meaning Stonefield Query should terminate) and continue using the application was fixed.
• A bug that prevented a field containing characters requiring it to be delimited (for example, a field named [Some-Field]) from being displayed correctly when defined as an enumerated field was fixed.
• The Include this field in the group header for option in the Grouping page of the Field Properties dialog is no longer enabled if there are no groups.

Neil Sorensen Joins Stonefield Software

Stonefield Software Inc. is pleased to announce that Neil Sorensen has joined us as Director of Channel Development.

Neil has spent the last 15 years working with accounting solution software companies including Softline Systems, ACCPAC International, and most recently Sage Software. He has extensive experience working with reporting solutions and supporting the dealer channel.

Neil will be available to assist you with all of your Stonefield Query sales, training, and marketing needs effective immediately.

Neil can be reached at:

nsorensen@stonefield.com
(604) 945-4169
(800) 563-1119

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.