ACT 10.1 Update

Recently, the 10.1 update of ACT! was released by Sage Software. In this release, several fields in the ACT! data provider were renamed. In particular, the record type field was renamed from "History Types Name" to "HistoryType Name" in the history tables, and the stage field was renamed from "Opportunity Stage Stage" to "Stage Stage" in the opportunity tables.

Unfortunately, these changes have the potential to break some Stonefield Query for ACT! reports. If a user has a report using one of these fields, and updates to ACT! version 10.1, then the report will no longer work; the report references the old field name, but the database now has only the new field name. We have updated Stonefield Query for ACT! to deal with this issue, and the fix is included in the currently posted version of Query.

Also, ACT! has made available two new items in their data provider: Opportunity History and Logon History. The opportunity history gives information about the changes made to opportunities over their lifetime, such as when their field values have been changed. The logon history gives information about the history of users' logons to the ACT! database. We have added support for these two new items to Stonefield Query for ACT!. Contact us if you're interested in an interim build with these additions (this is only available for current subscribers). Keep in mind that interim builds haven't been fully tested and the documentation hasn't been updated.

Adding Fields and Relationships

As many Stonefield Query users already know, you can use a setup.sqs file to do some customization of the data dictionary when Stonefield Query starts up. For example, lets say you had an "Orders" table in your database, but the table didn't have a field for the total amount of the order. We could add such a field by placing the following in a setup.sqs file:


loField = SQApplication.DataEngine.Fields.AddItem('ORDERS.AMOUNT')
loField.Caption = 'Order Amount'
loField.Type = 'N'
loField.Format = '$'
loField.Length = 18
loField.Decimals = 2
loField.Expression = "RUNSQL('select SUM(UnitPrice*Quantity) from"+;
" [Order Details] where OrderID = ?OrderID')"
loField.Calculated = .T.
lofield.FieldList = 'Orders.OrderID'


Above, we are first adding a new field to the orders table called amount. We set the caption to Order Amount, which is how we will see this field being displayed in Stonefield Query. The secret to making this work is what we set the Expression to. For our new Amount field, the expression is a call to the Stonefield Query function RunSQL, which sends whatever SQL we like to directly to the database. So for this example, we send the SQL necessary to calculate the total that we are after.

Not many Stonefield Query users know that you can also add new joins with the setup.sqs file as well. Suppose that Stonefield Query didn't have a relationship defined between Customers and Orders in the data dictionary. We could add one with the following setup.sqs:


loJoin = SQApplication.DataEngine.Joins.AddItem('Customers,Orders')
loJoin.ChildTable = 'Orders'
loJoin.ChildExpression = 'Orders.CustomerID'
loJoin.Jointype = 0
loJoin.ParentTable = 'Customers'
loJoin.ParentExpression = 'Customers.CustomerID'


So, the above creates a new relationship, with Customers as the parent table, and Orders as the child table. The ChildExpression/ParentExpression fields contain the field from that table to join on, and a JoinType of '0' means an inner join.

If we instead wanted to have a more complex relationship, we could do something like the following:


loJoin = SQApplication.DataEngine.Joins.AddItem('Customers,Orders')
loJoin.ChildTable = 'Orders'
loJoin.JoinExpression = 'Customers.CustomerID = Orders.CustomerID"+;
" and Customers.Country = Orders.Country'
loJoin.Jointype = 0loJoin.ParentTable = 'Customers'


The above creates a relationship between Customers and Orders on two fields, CustomerID and Country (pretend this makes sense for me please). In addition to being able to add relationships with this file, we can also make changes to existing ones. For example, say we wanted to change an existing relationship between Customers and Orders to a left outer join instead (so we get all Customers, even if they don't have Orders). We could do this with:


loJoin = SQApplication.DataEngine.Joins.Item('CUSTOMERS,ORDERS')
loJoin.JoinType = 1


A JoinType of 1 means left outer join, while 2 is right outer, and 3 is a full join.


New Features Added Today

We added a couple of new features to Stonefield Query today:

• If you had multiple fields in the Row or Data lists in Step 3 of the Cross-Tabulation Wizard and wanted to rearrange them, you previously had to drag them all out of the list and then drag them back in the desired order. You can now rearrange them by dragging a field up or down in the list.
• You are now prompted for all "ask at runtime" filter conditions in a single dialog rather than one dialog per condition when you run a report.

These features will be available in version 3.2 when it ships in Q1 2008.

Bug Fixes in Stonefield Query SDK Version 3.2

As I mentioned in the previous blog entry, work has just begun on Stonefield Query version 3.2 and we've already fixed a bunch of issues. In no particular order:

• A problem that caused advanced layout reports to have too long of a page length when previewed or printed from the Preview window, resulting in some records not being displayed, was fixed.
  
• Under some conditions (sorting on a certain field data type when at least one record has a null value in that field), a single character was displayed rather than the entire contents of a field. This has been corrected.
  
• Charts now display correctly when there's missing data in some categories for some series.
  
• The Selected Field list in Step 2 of the report wizards now shows real table and field names correctly when you edit an existing report with the Display real table and field names option turned on.
  
• Renaming a report in the Reports Explorer didn't change the report header like renaming it in a report wizard; this was fixed. Also, copying a report now also changes the report header.
  
• The bookmarks panel in the Preview window is now sized correctly when clicking a field that runs a linked report and that report has bookmarks.
  
• A problem with not saving the email settings in the Options dialog was fixed.
  
• Specifying a period for the "CommonFiles" entry in Data.INI now means Stonefield Query's data files are located in the same folder as Data.INI rather than SFQuery.INI. Other types of relative paths are now handled correctly as well. Also, the program does a better job of handling Data.INI and its data files in "legacy" locations (locations other than the Data subdirectory of the application folder), and handles the case where its resource file (SQResource.DBF) exists in both the application folder and the Data subdirectory.
  
• Stonefield Query no longer exits with an error code when a report that has no records is run from another application; formerly, this prevented a report from running if multiple reports were specified.
  
• A problem with entering too long of a contact name in the Registration dialog was fixed.
  
• A problem with selecting the wrong database when reporting on more than one database at a time under some conditions was corrected. Also, an issue with similarly named data sources (for example, "Company" and "Company Database") was fixed.
  
• The cause of a rare "invalid index key" error when running a cross-tabulation report was fixed.
  
• An error caused by sorting on a field that exists on a report but doesn't appear when Summary Report is turned on was fixed.
  
• The numeric part of image file names created when a report is output to an image now have zeros for place holders (for example, Report0009.GIF and Report0010.GIF rather than Report9.GIF and Report10.GIF) so they appear in the correct sorted order in Windows Explorer.
  
• A bug that prevented sorting on a grouped field if you created a variable in the Advanced Report Designer on that field was fixed.
  
• HTML, Word, text, and table output now work correctly for chart reports. You can no longer output charts to RTF because the results aren't very good.
  
• Clicking the Values button in Step 2 of the report wizards nows displays an empty list of values rather than no list at all if the selected table contains no records.
  
• The Filter Condition dialog now shows the correct list of tables and fields in the Cross-Tabulation and Chart Wizards for new reports.
  
• Linking to another report on "display field from related table" fields now works correctly.
  
• Stonefield Query now properly handles a table or field that has "SELECT" at the end of its name (for example, "TaxSelect").
  
• Failure to connect to the update Web site when checking if a new version is available no longer causes an error message to be displayed.
  
• A second error that occurred while displaying the error message for a first error that may have occurred in the Preview window (for example, when running a linked report that had a problem) was resolved.
  
• The report dropdown list that appears on the Link page of the Field Properties dialog now shows the correct icon for chart reports.
  
• In previous versions, if you created a report but didn't go to Step 6 of the report wizard, everyone could run the report but only you could edit it. Now, the default is that everyone can both run and edit the report. You can, of course, change this in Step 6.
  
• A problem with the Chart Wizard hanging up after previewing a chart report that had no data in it was fixed.
  
• There is no longer a problem with displaying reports created by users with user names longer than eight characters.
  
• Pressing the Esc key while a report is running from one of the report wizards no longer causes an error.
  
• A problem with a filter condition containing an apostrophe under certain conditions was fixed.
  
• The Import Reports function now displays a proper error message if you select an empty file to import from.
  
• Clicking in one of the activation code boxes in the Registration dialog or Add License dialog after activating the program online added spaces to the activation code, making it invalid. This has been fixed.
  
• Changing the parameter information for a linked report caused the wrong information to be stored. This was corrected.
  
• An issue with the automatic update mechanism on Windows Vista systems was corrected.
  
• The Page Up and Page Down keys no longer move you from one page to another in a wizard; this feature prevented these keys from being used in other controls, such as dropdown lists. Instead, use Shift + Page Up and Shift + Page Down.
  
• An issue with the Login dialog sometimes appearing behind Stonefield Query (and thus not visible, leading you to think the program had hung up) was fixed.
  
• Email addresses selected in the Select Email Addresses dialog are now separated with commas rather than semi-colons to prevent an issue with some email programs.
  
• The New Folder function in the File menu is disabled for runtime licenses, since it doesn't make sense to create a folder if you can't create any reports in that folder.
  
• A problem caused by zooming the code window for a script, then clicking on a different item in the Configuration Utility, then saving the changes in the code window was corrected.
  
• Delimited field and table names for MySQL databases, which use the reverse apostrophe as the delimiter character, are now handled properly in Stonefield Query.
  
• Reports using an expression filter condition (such as "Order Date equals DATE() - 7") on tables that have a Select script are now handled properly.
  
• Calculated fields using non-Stonefield Query expressions that reference a field in another tables are now handled properly.
  
• The Configuration Utility now creates a help file correctly when the Target Application's Name setting is filled in but Need Target Application Directory is set to False.
  
• A bug that caused the wrong Default Language or Default Data Group for New Reports to be displayed in the Configuration Utility was fixed.
  
• A problem that caused a bogus error message when refreshing a data dictionary which doesn't contain all the tables in the database was corrected.
  
• An error that occurred when converting a data dictionary table named something other than REPMETA.DBF to the latest structure was fixed.
  
• Calculated fields using non-Stonefield Query expressions and calling functions native to the database engine (for example, "DATEADD(minute, 360, Order.OrderDate)") that return data types different than specified in the data dictionary now work properly in reports.
  
• A problem importing a data dictionary that didn't specify relationships was fixed.
  
• An issue that caused an error when you specified a value for Custom Properties for a field was fixed.
  
• The user no longers gets an ask-at-runtime dialog twice if a report has an ask-at-runtime filter condition and you have a DataEngine.FinalizeSQLStatement script.
  
• The user no longer gets a "no records matched" message if you display a dialog in the DataEngine.FinalizeSQLStatement script (such as calling the DataEngine object's GetValuesForField or GetValuesForParameter methods) and the user chooses Cancel in that dialog.
  
• The Configuration Utility now treats "PLAN" as a reserved word, so delimiters are automatically added around a table or field with that name.
  
• The ProjectLoaded property of the SQProxy object is now only set to True once the project has finished loading. In previous versions, it may have been set to True a little too soon, especially if you had a lot of scripts that slowed down startup.
  
• Fields using "R" for the Format property no longer have the Picture property applied twice when a value selected in the Values dialog is inserted into the Filter Condition dialog.
  
• An issue with MySQL when using multiple databases that caused the connection to the second database to fail because the user name and password were specified twice in the connection string was fixed.

Contact us if you've run into one of these issues and would like an interim build to resolve the problem (this is only available for current subscribers). Keep in mind that interim builds haven't been fully tested and the documentation hasn't been updated, so you shouldn't distribute this to customers unless necessary.

New Features in Stonefield Query SDK Version 3.2

Work has just begun on Stonefield Query version 3.2 and we've already added a bunch of new features. In no particular order:

• The Preview window has a new Email button allowing you to send an email directly from the window.
  
• The Login dialog no longer displays a choice for the database to query on if the database is specified as a command-line parameter.
  
• The Login dialog now shows the default user name and password (ADMIN and ADMIN) when the program hasn't been activated so new users don't have to guess or (heavens!) read the help file.
  
• Multi-page TIFF files are now supported for report output.
  
• Stonefield Query now looks in the Windows Registry for the location of the help file if the UseRegistry setting in Data.INI is Yes.
  
• Stonefield Query now returns error code -27 when called from another application and it cannot locate its resource file.
  
• You can now select more than about 20 files at a time when importing reports.
  
• You can now specify an expression for a chart title.
  
• The Expression Builder now has a Test Syntax button.
  
• The Reports Explorer and Field Properties dialog now load faster when there are lots of reports. Also, the report dropdown list on the Link page of the Field Properties dialog no longer displays folders that have no reports.
  
• You can now tell Stonefield Query to ignore an ask-at-runtime filter condition in a report run from another application by specifying "Value=" (that is, with no value specified) in the command line parameters.
  
• The performance of some reports with a filter condition on certain types of fields was improved, especially when there are lots of records in the table.
  
• The Add License dialog has a new Copy button which places the license information on the Windows clipboard.
  
• The Use old engine setting in Step 5 of the Quick Report Wizard is now always enabled, not just when Advanced layout is turned on. Turning this on allows the Preview window to appear more quickly, especially for very long reports, but the Preview window used when this is turned on has fewer features and some advanced options, such as dynamic formatting and rotation, aren't supported.
  
• Multiple filter conditions on the same field are now supported in linked reports as long as each condition uses a different operator.
  
• Deleting a folder now deletes all reports in that folder rather than giving you an error message and requiring you to manually delete all the reports.
  
• New Create/update column heading and Adjust field width automatically options in the Field Properties dialog of the Advanced Report Designer provide you with more control over what Stonefield Query does automatically when you save a field.
  
• The AfterRun code for a report can now change something about how a report is output and re-run the output.


• You can now define a default table for each data group.
  
• The new Icon File configuration setting allows you to specify your own icon for the Stonefield Query dialogs.
  
• The new Display Application Name configuration setting allows you to specify whether the application name is displayed before the logo in the About dialog.
  
• The new Support Subscription Reminder configuration setting allows you to specify whether your users will receive reminders when their subscriptions are due to or have lapsed.
  
• Stonefield Query now supports the TopSpeed ODBC driver.
  
• New DataEngine.AfterResultSetRetrieved, DataEngine.SetOperatorList, and Application.ReportsExplorerActivated events are available.
  
• You can now double-click a memo field in the window displayed when you select View Table Contents in the Configuration Utility to display the contents of the memo field in another window.
  
• The dialogs displayed when you call the DataEngine object's GetValuesForField or GetValuesForParameter methods now include the "ignore this condition" option. You can also pass these method a collection of default values to use.
  
• The Application object has a new GetValuesCollection method and new ApplicationDirectory, ApplicationDataDirectory, ShortApplicationName, and Version properties.
  
• The ADODataSource object has new ConnectionTimeout and CommandTimeout properties so you can control timeout settings.
  
• The LoadProject method of the SQProxy object now accepts the name of the data source to query on as a parameter.
  
• The SQProxy object now has a Parameters collection you can use to pass user-defined parameters to Stonefield Query, much as you can pass user-defined parameters on the command line.
  
• Turning on or off the checkbox for a database in the Data Groups or User Groups pages now turn on or off the checkboxes for all tables in that database.
  
• You no longer have to specify parameters in a stored procedure passed to the ExecuteSQLStatement method of a Database object using syntax like "exec MyProcedure ?FirstParameter, ?SecondParameter". Instead, pass a parameters collection as the second parameter to ExecuteSQLStatement and specify the stored procedure with any parameters (in other words, simply "exec MyProcedure").

A lot of other features are planned: see http://stonefieldquery.blogspot.com/2007/10/whats-next-for-stonefield-query.html and http://stonefieldquery.blogspot.com/2007/10/version-32-features-proposed.html for some of them. We've also fixed a number of bugs; these will appear in the next blog entry.

If you'd like an early peek at these new features, contact us and we'll send you an interim build (this is only available for current subscribers). Keep in mind that interim builds haven't been fully tested and the documentation hasn't been updated, so you shouldn't distribute this to customers unless there are features or bug fixes they really need.