Friday, December 21, 2007

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.

Thursday, December 13, 2007

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.

Monday, December 10, 2007

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.

Friday, December 7, 2007

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.

Wednesday, December 5, 2007

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.

Friday, November 30, 2007

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.

Tuesday, November 27, 2007

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.

Friday, November 23, 2007

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.

Thursday, November 22, 2007

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.

Saturday, October 20, 2007

Version 3.2 Features (Proposed)

The following features are on the chopping block for Version 3.2. Your feedback will help us determine which of these features make the cut. Send your feedback to Stonefield Development.

Medium (1 – 2 weeks)• Option to specify that a field should appear in a specific group header (e.g. grouping on city and want to show state in group header as well)

• Output options:

• Specify file type for email attachment (PDF, Word, Excel, etc.)

• Specify document settings for PDF, Word, Excel: title, author, etc.

• Support features in Field Properties that are missing in Advanced Report Designer eg. linking

• Support same named reports in different folders. Issue with methods like GetReportByName.

• Support for “week” for date format in cross-tab and charts. Need items in Options dialog for how formatted and starting day of week

• Single dialog for all ask-at-runtime conditions

• More template options for cross-tab reports e.g. different formatting for row, column, and total fields

• Add description of each function in Expression Builder

• Multi-line headings for fields

• Option to sort by aggregate values in cross-tabs

• Support HTML text in GoldMine and ACT

• Link action: Google map or MapQuest

• Link action: search multiple Web sites for selected contact: Google, Google Photos, LinkedIn, etc.

Small (1 – 4 days)

• Support Crystal reports

• Show progress as pages rendered

• Adding a field in the Advanced Report Designer with the same name as a field already in the report but from a different table should automatically add “_A” suffix

• Support "include all fields in same table in group header" with summary reports

• Email option in preview window

• Auto-import reports in auto-import folder

• More options in Field Properties dialog about how to display date in cross-tab or chart e.g. display month as Mmmm YYYY, Mmm YYYY, Mmm YY, etc.

• Named vs. concurrent users

• Specify position (e.g. first or last page) of chart in quick or cross-tab report

• Option to include field value in cross-tab subtotal lines (e.g. “Subtotal for Canada”)

• Location of reports folder and target app folder in Options dialog

• More control over what users can do in Maintain Users and Groups dialog e.g. access Template Editor, scheduling reports, etc.

• Field Properties dialog: rotation option so don’t have to use Advanced Report Designer

• Mover bars in Rows and Data lists for cross-tabs

• Function in Configuration Utility to export to XML. This allows someone to export, modify in something like Excel or Access, then re-import.

Again, send us your feedback on these proposed features. We want to know if these are the things you want in Query. If there's something you would like that's not on the list, submit those too.

Version 3.2 Features (Definite)

Stonefield Query Version 3.2 is in the planning stages. There are hundreds and hundreds of features we could add, however, we can't add them all.

We've narrowed down the list and broken them into 3 categories: small (1-4 days), medium (1-2 weeks), and large (> 2 weeks).

This list includes the "definite" features for the next release. See the next blog entry for a list of features still on the chopping block.

Target release date: 03/31/2008

Large (More than 2 weeks)

• “Negative” queries. (ie. Show me a list of customers that did not have any orders last year)

• Multiple detail bands. (ie. Give me a complete view of a company's activities, history, forecasted sales, and invoices.)

• Join dialog so user can change joins without changing SQL statement (ie. “include records even if no records” changes join to RIGHT OUTER). This is only done if negative query is just another join choice. Options would include: contacts who have activities, contacts whether they have activities or not, activities whether they have contacts or not, contacts with no activities with specified filter (ie. in date range), activities with no contacts with specified filter

Medium (1 – 2 weeks)

• Excel data-only output for cross-tabs

• Templates control default page size and orientation

• More information in Setup Dialog e.g. email information

• Advanced button in Label and Cross-Tab Wizard (e.g. gain access to customize SQL and top n)

• Support VFP 9 SP2 features, including rotation, dynamics, advanced page, etc.

Small (1 – 4 days)

• Test Syntax button on Expression Builder (Done)

• Default table for each data group (Done)

• Option to pass “ignore this value” for ask-at-runtime conditions on command line (Done)

• SQApplication.DataEngine.GetValuesForField support default values and Ignore this Condition option (Done)

• Deleting folder deletes all reports in that folder

• Option to turn on/off point labels for all series

• Import reports from REPORTS.DBF (another option in Import dialog)

• Option to export all reports from a folder or just selected reports. Change “Export Report” to “Export Selected Report” and “Export All Reports” to “Export Reports...” which brings up a dialog with choices for selected report, all reports, and a TreeView of all reports with checkboxes (selecting a folder selects all reports in that folder)

• Display proper page number when group resets to page 1

• Options to not create label for new field and to not auto-size field in Advanced Report Designer

• Don’t check user count or name when run from command line or schedule or else can’t run report when already logged into UI

• Dialog to edit properties for a folder (ie. security)

• Option for which databases a user can access in Maintain Users and Groups dialog

• Encrypt connection user ID and password in error log and error report

• Display Date Only setting in Advanced Report Designer: enables user to turn this off if turned on in data dictionary

• Save “database” selection in filter page if “use next time” checked for all users (currently doesn’t work for runtime)

• If user cancels in GetValuesForField dialog called from Select script, don’t give “no records match” error

• Workstation-only installer (generic name so no branding issues) or no workstation installation at all (ie. all runtime files in program folder)

• Option to put all fields in same table in one line of group header

• Implement new features in TeeChart 8

Please send us your feedback on these features. We want to know if these are the things you want in Query.

Friday, October 12, 2007

CSIA DEMOgala

Stonefield Software was invited to Denver this week for a trade mission organized by Industry Canada, Saskatchewan Trade and Export Partnership, and the Canadian Consulate to attend CSIA's Annual DEMOgala event.

As part of the event, Doug Hennig (CTO) was interviewed by Larry Nelson of W3W3.com on how Customized Reporting Solutions Give Your Business an Edge.

On Tuesday evening Doug did a brief presentation on Stonefield Query to approximately 100 guests at a welcome reception hosted by the Canadian Consulate.

On Wednesday, we (Jeff, Chris, Doug, & Mickey) met for several hours with Bob Ogdon and his staff at SwiftPage to discuss how to integrate our products and were very excited about the possibilities for both integration and cross-marketing.

Thursday was DEMOgala 2007, Colorado's premier IT event. We had a booth and showed Stonefield Query to a lot of very interested people. At lunch, Doug was part of a roundtable of C-level executives moderated by Jon Nordmark, CEO of ebags.com. Doug had the opportunity to talk about Stonefield Query in front of an audience of about 600 CEOs and CIOs, plus he had a great discussion about problems caused by the falling US dollar/rising Canadian dollar and the challenges of recruiting staff in the red-hot Canadian economy.

This was a fantastic event for Stonefield and will be seeking out similar events in the future.

Monday, October 1, 2007

Doug Hennig receives 13th Microsoft MVP Award

Doug Hennig, Stonefield's CTO, was awarded a Microsoft Most Valuable Professional (MVP) award for the thirteenth consecutive year.

The Microsoft MVP Award recognizes outstanding members of a technical community for their participation and willingness to help others. The MVP Award is given to exceptional technical community leaders who foster the free and objective exchange of knowledge by actively sharing their real-world expertise with technology users.