Stonefield Query Version 3.2 Build 3321

We posted updated versions of the Stonefield Query SDK, Stonefield Query for Sage Accpac ERP, and Stonefield Query for Sage Pro ERP today with the following changes.

All Versions

• Microsoft Word documents generated from Stonefield Query now have the proper margins and orientation.
• Stonefield Query now properly handles custom SQL statements with spaces after commas between fields and is no longer case-sensitive for field and table names.
• Linking on non-character fields is now supported.
• Exporting a folder that contains a folder that contains reports without expanding all levels of folders in the Export dialog no longer gives an error.
• Passing less than 10 values for Is One Of conditions when calling Stonefield Query from the command line no longer causes a problem.
• Custom SQL statements with SUM() no longer fail. Also, the order of ORDER BY and GROUP BY clauses is no longer important.
• The Is One Of condition for numeric fields no longer shows "0" for values you do not enter; for example, specifying only 1 and 2 as the values now displays the condition as "Field Is One of 1,2" rather than "Field Is One of 1,2,0,0,0,0,0,0,0,0".
• The default location to export reports to is no longer the program folder but your Documents folder.
• Outputting to a table now outputs variable-length character fields (also known as Varchar) as character rather than memo fields.
• A summary report with multiple groups and sorted on a summed field now displays properly sorted.
• You no longer get an error if a grouped field is listed after a calculated field in Step 2 of the Quick Report Wizard for a summary report.
• You no longer get an error filtering on certain types of calculated fields that don't appear in the report.
• Stonefield Query now supports online activation and maintenance renewal when a proxy server is used.
• The Analyze Database function now adds new tables and fields in the data dictionary to the "favorites" set.

SDK

• The new Active property of the Database object allows you to programmatically disable a database under certain conditions, such as if the database is optional and isn't available for this particular user.
• The Test button on the relations page of the Configuration Utility now works with tables in different databases and with database engines that don't support the SQL TOP clause.
• The GetFolders method of the ReportEngine object now returns all folders when you don't pass a user name and password to the LoadProject method of an SQProxy object.
• Several bugs in the ImportFF and ImportFFXML utility functions were fixed (including updating reports security so they are available to everyone) and they work more reliably with different types of reports.
• If an error occurs when running a report using SQProxy, an error is raised in the calling application, so you should use error handing in your applications using SQProxy. Also, if an error occurs when calling LoadProject, ErrorMessage contains information about the error and the new ErrorCode property returns the same value SFQuery.EXE returns when running a report from the command line.
• Calculated date fields are now handled properly when used in the column of cross-tab reports.
• Returning an empty string from DataEngine.FinalizeSQLStatement tells Stonefield Query not to continue running the report.
• Stonefield Query now works with the SQLite database engine.

Sage Accpac ERP

• Uni Sales Analysis is now supported.
• All Rate Operator fields now use the same list of possible values.
• The Audit Time fields now display times as HH:MM:SS.
• The relationship between Inventory Control Transfer Headers and Transfer Details was fixed.
• The Payroll Employee Tax Information table is now available for reporting.
• "Refund" is now available as a value for Document Type and "Debit Note - Advance Claim Credit" and "Refund - Posted" are available for Transaction Type in Accounts Receivable Documents.
• ICILOC now appears as "Location Item Details" since that's the type of information contained in that table.
• Most relations from tables to Inventory Control Locations were changes to Location Item Details instead.
• A bug that caused fields in Inventory Control Sales Statistics to appear as if they came from Transaction Statistics was fixed.

Sage Pro ERP

• Several bugs in the Import ACCPAC Report Writer Reports function were fixed (including updating reports security so they are available to everyone) and it works more reliably with different types of reports.
• Numeric fields now use 999,999,999.99 as the format in the Advanced Report Designer rather than 9999999999999999999.999999.
• The caption of the ICISUM was changed to Inventory Sales/Usage History File since both it and ICHIST had same caption (Inventory History File).
• There's now a direct relationship between the Inventory Summary File and the Inventory Master File. Also, there are new Year and Period fields in the Inventory Summary File to make filtering on, for example, a specific year easier.
• Filters on certain values of certain fields, such as Type of Invoice (in the Accounts Receivable Invoice Transaction File) equals Normal Invoice, now work properly.
• Inventory History File is no longer listed for Sage Pro ERP version 7.5 or later since that table was removed.
• Errors in the relationships between the GL Posted Transaction Headers and Details Files and between GL Unposted Transaction Headers and Details Files were fixed.
• Drop Shipment is now available as one of the choices for Type of PO in the PO Transaction File.

Stonefield Query for SalesLogix Update

We have posted an updated version of Stonefield Query for SalesLogix, with the following changes:

• Stonefield Query now supports SalesLogix version 7.5 databases
  

Stonefield Query for Sage BusinessVision Update

We have posted an updated version of Stonefield Query for Sage BusinessVision, with the following changes:

• Stonefield Query will now attempt to automatically detect available BusinessVision databases. This removes the need for ODBC data sources to be set up manually for use in Query.
• The data source management options have been improved. Maintaining data sources is now done through the Databases page on the Options screen, rather than through the Maintain Data Sources screen.
• The Stonefield Query installation now includes the BusinessVision DDF files for versions 7.0 - 7.2 of BusinessVision, and Query will add them to BusinessVision database folders when possible. These files are required to be in the BusinessVision database folders for ODBC applications such as Query to be able to access the data, but BusinessVision does not automatically install them itself.
• The available tables have been organized into data groups, such as Payroll and General Ledger, to make it easier to find tables in the list.
• New fields Last Invoice Date and Last Invoice Number have been added to the Inventory table.
• The new sample reports Inactive Inventory Items and Two Year Monthly Client Sales Comparison have been added.
• The captions of the Country fields in several tables have been updated to distinguish between the Country (ie "Canada") and the Country Code (ie "CDN").
• BusinessVision databases have a number of numeric fields in which logical values are stored as either 0 or 1. These are now treated as logical fields in Stonefield Query.
• The behaviour of the "Ship To" address fields in the Sales Order and Purchase Order tables has been changed to account for BusinessVision's support for multiple ship-to addresses.

AccountMate SQL 7.5 and AccountMate LAN 7.1 Now Supported

A small update of Stonefield Query for AccountMate SQL is now available that supports the new versions that were recently released by AccountMate. This includes AccountMate 7.5 for SQL and AccountMate 7.5 for Express. As long as your Stonefield Query subscription is current, you can get this new version from here.

Also, a small update of Stonefield Query for AccountMate LAN is now available that supports the new version, 7.1, that was recently released by AccountMate. You can get this new version from here.

Stonefield Software is Sage Gold Development Partner and Sage Certified

Stonefield Software was recently awarded Gold Development Partner status with Sage (formerly known as Sage Software). Also, our Stonefield Query versions for Sage products, such as Stonefield Query for Sage Accpac ERP, Stonefield Query for Sage Pro ERP, and Stonefield Query for ACT!, were recently certified by Sage.

Stonefield Query Version 3.2 Build 3253

We posted updated versions of the Stonefield Query SDK, Stonefield Query for Sage Accpac ERP, and Stonefield Query for Sage Pro ERP today with the following changes.

All Versions

• Fields with commas in the captions no longer cause a problem when used in cross-tabulation reports.
• Under some conditions, previewing a report, then editing the report and closing the report wizard, then opening another data source and running a report used data from the previously open data source. This has been fixed.
• Stonefield Query now gives a warning when you run a report that includes a memo field in a group header.
• You can now use the SUM() function in a custom SQL statement.
• Some dialogs were adjusted so things fit better when Spanish and Dutch are used. Also, the Find dialog displayed in the preview window is now properly localized.
• Some labels didn’t have a sufficient left margin. This was fixed.
• Filter conditions are now displayed properly when something other than a period is used as the decimal place character.
• Clearing the filter for a report (that is, clicking the Clear button in the Filter dialog) no longer clears the exclude conditions for the report.
• Exclusion filters weren't working under some conditions. That's been fixed.
• Filter conditions are now displayed correctly if you change the language or date format Stonefield Query uses after creating the filter.
• The currency symbol now appears correctly in a report if you use something other than the dollar sign ($) and specify a dynamic color or style expression for the field.
• Linked fields, such as email or Web linked fields, now work properly in a PDF file; that is, you can click the linked value in the PDF reader to navigate to the Web site or bring up your email program.

SDK

• The FilterCondition object now accepts the "is yes" and "is no" operators.
• Adding multiple conditions to the FilterConditions collection now works.
• Custom icons are now used properly when they exist in a directory other than the project

Sage Accpac ERP

• Stonefield Query for Sage Accpac ERP now supports multiple languages. Currently, English, Spanish, German, and Dutch are supported.
• All quantity fields now default to no decimal places. You can, of course, choose the number of decimals you want to use in the Field Properties dialog for a field in a particular report.
• New Margin and Profit fields are available in Sales History Details in Order Entry.
• Reports using Sales History Details from Order Entry and Customers from Accounts Receivable now work better.
• Hours and Overtime House in Payroll Check Details are now formatted properly.
• The new Amount field in Payroll Check Details displays negative values for deductions, so summing works as you'd expect in reports.
• Filtering on Document Date (and some other fields) in the Combined Invoices & Credit Notes and Combined Invoice & Credit Note Details tables in Order Entry now works properly.
• New Quantity (Stock U/M) fields in Invoice Details, Credit Note Details, Order Details, and Combined Invoice & Credit Note Details displays quantity amounts into the same unit of measure so they can be added up correctly. For example, if you enter one invoice of a desk lamp of a quantity of 5 with a UOM (unit of measure) of "each" and another of 10 with a UOM of "dozen", this field will give you a total quantity 125 as opposed to the incorrect 15 when these two records are added.

Sage Pro ERP

• The Salesperson File in Order Entry now also appears in the Accounts Receivable module. Also, a new Full Name field in that table makes reports easier to read.
• Scheduled reports now run properly.
• You can now sort and group on the Email Address and Web Site fields in the Customers, Vendors, National Accounts Master, and Customer Connect Company Files.

Stonefield Query Version 3.2 Build 3226

We posted updated versions of the Stonefield Query SDK, Stonefield Query for Sage Accpac ERP, and Stonefield Query for Sage Pro ERP today with the following changes.

All Versions

• Stonefield Query is now available in Spanish thanks to Juan Antonio Santana.
• Stonefield Query now supports linked reports that pass a value to a report with an "is between" ask-at-runtime condition by using the same value for both the starting and ending values.
• Multiple text lines (for example, in a memo field) are put into the same cell in a Microsoft Excel spreadsheet rather than separate cells.
• An issue where the dollar sign is cut off for currency values or the value shows as **** when the largest-width item in the column is a negative value was fixed.
• The correct currency symbol now displays in charts.
• You can now call the built-in GetConditionValue function from an expression in the Advanced Report Designer.
• Using a currency field in the data area of a cross-tabulation report but formatting it as "Count" no longer displays the currency symbol.
• Cross-tab data columns are now sized properly in the unusual case where all row totals are zero (that is, individual columns have a mixture of positive and negative values so that the row total is zero and all rows have this condition).
• You no longer get an error if you edit a group in the Maintain Users and Groups dialog, select the Users tab and when asked to save, choose No, and then change the license type.

SDK

• Joins involving fields with the same name as the tables they are in (such as "Category.Category") are now handled properly.
• You can now access the FilterConditions object in DataEngine.FinalizeSQLStatement scripts.
• Anonymized projects no longer give an error looking for SQOUTPUT.EXE when you print a report.
• Running a report with ask-at-runtime filter conditions and a custom SQL statement from SQProxy no longer displays the ask-at-runtime dialog if you specify that the conditions should be ignored by passing blanks for their values.

Sage Accpac ERP

• New Combined Invoices & Credit Notes and Combined Invoice & Credit Note Details tables in Order Entry make it easy to report on both invoices and credit notes on the same report.
• You can call new built-in GetStartingBalance and GetStartingBalanceFYPeriod functions in an advanced layout report to display the starting balance for the specified account in the specified fiscal year and period.
• There's a new Quantity on Hand (Current) field in Inventory Control Location Details table. By default, Sage Accpac does not store the quantity on hand but calculates it. When day end is run, all pending transactions are applied and the Quantity on Hand (Last Day End) field is updated to the true quantity on hand. The Quantity on Hand (Current) field allows you to see what is on hand without running day end. It is calculated as Quantity on Hand (Last Day End) + Quantity Received Not Costed - Quantity Shipped Not Costed + Quantity Adjusted Not Costed.
• The built-in functions for date expressions, such as "Today's Date", now work properly for Sage Accpac dates.
• For optional fields that have descriptive as well as code values, a new "Optional field Description" field is available to display the descriptive value.
• There are several new fields in the General Ledger Posted Transactions table which you can use to create more compact transaction listings: Batch-Entry, which combines the batch and entry numbers, Source, which combines the Source Ledger and Source Type values, and Fiscal Year-Period, which combines the Fiscal Year and Period.
• Stonefield Query no longer permits a relationship between Accounts Receivable Customers and Project and Job Costing Projects unless you are using Sage Accpac 5.5 or later.
• The Purchase Order and Account Sets tables are now included in the Order Entry module.
• Since the Salesperson table was removed from Order Entry in Sage Accpac 5.5, all relationships to it now work with the Accounts Receivable Salesperson table.
• The Account Segment Code fields in the General Ledger Accounts table now display the segment code rather than its descriptive name. However, new Account Segment Description fields were added to display the descriptive names. This gives you the best of both worlds: you can display either the code or descriptive name or both.

Sage Pro ERP

• Stonefield Query now displays the proper values for the Transaction Type field in the Inventory Transaction table for issues and receipts.
• All fields in "current + history" tables now have same data type and size as those in the current tables.
• Stonefield Query now properly handles fields that were renamed in Sage Pro version 7.0 so you can use Stonefield Query with Sage Pro version 6.5 and earlier.
• The MACODE field in the Order Entry Transaction table is no longer available since it's obsolete.

Doug Hennig receives 14th Microsoft MVP Award

Doug Hennig, Stonefield's CTO, was awarded a Microsoft Most Valuable Professional (MVP) award for the fourteenth 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.

Email Back Up

Our email server had problems yesterday; it bounced back everything sent to it. Fortunately, it's working again, so please resend any email you tried to send.

Stonefield Query Version 3.2 Build 3169

We posted updated versions of the Stonefield Query SDK, Stonefield Query for Sage Accpac ERP, and Stonefield Query for Sage Pro ERP today with the following changes.

All Versions

• The performance of many types of reports was improved.
• Stonefield Query now supports previewing when called from the command line (we had removed that capability but have added it back).
• You no longer get an error running a report from another application or running a scheduled report if Stonefield Query is already open.
• You can now specify AllowDialogs on the command line to tell Stonefield Query to display the ask-at-runtime dialog if there are any ask-at-runtime conditions rather than having the program terminate with error -13. It will still terminate with that error code if the user clicks Cancel in the ask-at-runtime dialog so your application can tell whether the report ran to completion or not.
• Specifying DBF for the file type and not specifying NOSHOW (that is, you want to display the contents of the resulting DBF file) when calling Stonefield Query from the command line now properly displays the file.
• The Maintain Users and Groups dialog no longer allows you to remove the Administrators group from the last user in that group; otherwise, no one would be able to access this dialog.
• Subqueries in custom SQL statements are now handled better.
• Stonefield Query now supports exclusion filters for tables that have complex joins between them (that is, more than one field matching another).
• Typos in the Dutch versions of some messages were corrected.
• The demo welcome dialog no longer mentions Webinars, since they aren't available for all versions.
• The locations of some runtime files were moved to more logical locations.
• A bug that caused the Table dropdown list to revert to the default table when you select a different table, turn on Show Only Favorites, and click the Values button was fixed.
• If you are editing a report in a report wizard, then activate the Reports Explorer and close it or choose Exit, the report wizard window now comes to the top before asking if you want to cancel the report; this makes it more obvious which report it refers to.
• Stonefield Query now correctly handles a filter value that contains " select" in it.
• Source Database fields are no longer removed from the Available Fields list in Step 2 of the report wizards when you turn on the Show Only Favorites option.
• A bug that prevented changes to the SQL statement for a report from being saved after clicking the Reset button in the Customize Report Wizard was fixed.
• You no longer get an error if you have a report with more than one grouped field, have Show Count in Group Footer turned off for the last grouped field, have Summary Report turned on, and output the report to Microsoft Excel - Data Only.
• Fields that have a different data type displayed in Stonefield Query than are actually stored in the database (for example, date values stored as character strings in the database but displayed as true date values in Stonefield Query) no longer cause an error when output to Web Page - Table format.
• The Edit button is now disabled if you select a report that contains tables or fields you don't have access to. Previously, the button was enabled but brought up the Edit Folder dialog if you clicked it.
• You can now pass a numeric value as the second parameter to the GetConditionValue function to return the filter value for the Nth condition. For example, if there are two conditions for Customers.CompanyName, use GetFilterCondition('Customer.CompanyName', 2) to get the value of the second condition.
• Previously, the page header of a report called from a linked report showed an incorrect filter value for certain types of linking fields (although the data in the report was correct). This was fixed.
• Certain types of character fields weren't output with quotes surrounding them when outputting to a comma-delimited (CSV) file. This was fixed.
• Longer SQL statements are now allowed.
• Filter expressions using Stonefield Query objects (such as comparing Salesperson to oUser.cUserName) now work properly.
• Stonefield Query now properly handles fields containing ")", "(", and " FROM ".
• Previously, if FieldA appears before FieldB in the report, FieldB is grouped, and FieldA appears in the group header band for FieldB, FieldA was covered with template objects such as the grey bar in the Elegant template. This was fixed.
• A bug that didn't properly set up the groupings in a report if you grouped on FieldA, then added FieldB, moved it above FieldA, grouped on it, and moved the grouping above FieldA was fixed.
• A bug that misaligned a field's heading in the second row of column headings when the first row has a multi-line heading was fixed.
• A bug that caused an error if you included a date field in the series of a chart, didn't change the field properties to be displayed as something other than date, and have your Windows configured to display dates using month and day parts of less than two digits (such as M/D/YYYY) was fixed.

SDK

• The Heading property for fields is no longer updated when you refresh the data dictionary. It should either be the same as the caption (internally, it's blank, meaning the use the caption as the heading) or a custom value, so in either case, it shouldn't be changed when refreshing.
• The Resource Editor is now resizable.
• The Values dialog no longer gives an error when you've defined a field as Character or Varchar with less than 254 characters but it actually has more than that.
• A bug that causes an error if you follow the "Joins Between Different Databases" tutorial in the help file but use the Visual FoxPro Northwind database rather than the Access or SQL Server version was fixed.
• You no longer get a warning that you're not eligible to use this version of the Stonefield Query Configuration Utility if your software maintenance expires.

Sage Accpac ERP

• Optional fields are no longer removed from the Available Fields list in Step 2 of the report wizards when you turn on the Show Only Favorites option.
• The Salesperson tables in Accounts Receivable and Order Entry now have "(AR)" and "(OE)" in their descriptions so you can distinguish them.
• There are several new Extended Price fields in the Order Entry Order Details table that display the extended price based on various quantities, such as quantity ordered, quantity backorder, quantity shipped, and so forth.

Sage Pro ERP

• Stonefield Query no longer gives an error if it encounters a blank record in the system company table.
• The relationships for several tables between Sales Orders and Accounts Receivable were changed so reports showing fields from both modules work better.
• The table you would most likely report on is now automatically selected in Step 2 of the report wizards rather than the first table in alphabetical order.
• The MACODE field, which displayed as "Obsolete Field", was removed from the SOTRAN, SOTRN, and SOYTRN tables.