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.

Stonefield Query Features Added

We added the following new features in the past week:

• Support for terminating licenses, or subscriptions. (Note that we used to call annual fees for updates and support "subscriptions" but have changed that to "software maintenance".) This allows someone to license the use of Stonefield Query one month at a time.


• Templates can now specify the paper size and source (such as a certain bin) so you don't need to use the Advanced Report Designer to, for example, output a report on legal paper.


• Support for queries involving the absense of data (internally, we call these "negative queries"). For example, suppose you want a list of all people you did not contact last year or all customers who didn't buy a certain product. Previously, you had to customize the SQL statement for the report to achieve this. Now you simply specify how to exclude certain records using the same dialog you use to create a filter on a report.


• Places in Stonefield Query that support expressions (such as the report header or footer) now support non-character expressions such as the current date.


• The Text datasource handles more types of date values.


• Bug fix: Stonefield Query wasn't respecting changes to the font style for a field in a report (for example, making it bold).


• Bug fix: the Ignore This Condition choice in the conditions list of the Link page in the Field Properties dialog acted like None (that is, you were still prompted for an ask-at-runtime condition).


• Bug fix: a data group with a name that's a subset of another data group (for example, "My Group" and "My Group 1") now works properly.


• Bug fix: the Advanced Report Designer no longer sizes a field past the right edge of the report when it automatically determines the size.

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.