Updating the Stonefield Query for Sage Pro ERP Data Dictionary

Stonefield Query knows all about your Sage Pro ERP database because it has a data dictionary. A data dictionary defines the fields and tables in a database, providing features such as descriptive names so you don't have to know the real names, how tables are connected, or joined, together, and calculated values such as extended price that aren't normally stored in the database.

However, if you've added custom fields or tables to the Sage Pro database, you'll want to refresh the Stonefield Query data dictionary so you can query on those new fields or tables. This is easily done using the Stonefield Query Software Development Kit (SDK). You may also want to use the SDK if you want to report on other databases you have linked to your Sage Pro system.

The Customizing the Stonefield Query Data Dictionary topic in the Stonefield Query for Sage Pro ERP help file has complete information on how to refresh the data dictionary. However, one important thing to note is that if you're using a SQL Server database, you have to tell Stonefield Query which database to read the data structure from. You can't use the DSN that Sage Pro automatically creates because that DSN sets the default database to the Sage Pro system database, which doesn't contain the tables you're interested in. Instead, create a DSN that sets the default database to the Sage Pro data database.

Alternatively, you can change the connection type to "ODBC - Connection String" and enter a connection string similar to the following:

driver=SQL Server; server=ServerName; database=DatabaseName

where ServerName is the name of the server and DatabaseName is the name of the Sage Pro database.

You can then click the Refresh button in the toolbar to refresh the entire data dictionary or right-click a table and choose Refresh Table from the shortcut menu to refresh just that table.

Stonefield Query Version 3.2 Build 3105

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

• A bug that causes a problem with filtering on the second of two fields with the same name was fixed.
• A bug that resulted in a bogus "The exclusion filter involves tables that appear in the report" warning when creating an exclusion filter was corrected.
• Row fields in cross-tabulation report now respect the bold setting for the fields.
• Field and expression filters now work properly with the "contains" operator.
• Typos in some Dutch translations were fixed.

SDK

• Refreshing the data dictionary no longer converts fields you've change to Logical back to the Integer data type they're actually stored as. The same is true for Date fields no longer being converted back to DateTime fields.
• A bug that prevented Setup.Changed from being called when the Setup dialog is closed was fixed.
• The Data Groups dropdown in the table properties page now drops down as expected. Previously, under some conditions, it failed to do so.

Sage Accpac ERP

• Sage Accpac ERP version 5.5 is now supported.
• Accpac databases are now shown as the company name rather than the data source name in the Open Database dialog and the status bar of the Reports Explorer.
• The descriptive names of many tables now matches those in the Accpac documentation.
• A bug causing the wrong company name to appear in the header of reports was corrected.
• Most modules now have a default table. For example, if you select Accounts Receivable when creating a quick report, the Customers table is selected by default

Sage Pro ERP

• Sage Pro ERP version 7.5 is now supported.
• Relationships between a number of tables have been improved. For example, ARMAST is now a preferred over ARCUST as a relation to ARTRAN, ARYTRN, ARTRN, ARCASH, ARYCSH, and ARCSH when all three used.
• Relationships were added  for SOHSHP-SODSHP, SOHSP-SODSP, SOYSHP-SOYDSP, SODSHP-SOTRAN, SOYDSP-SOYTRN, and SODSP-SOTRN. Relationships for SODSHP-SOMAST, SODSHP-SOMST, SODSP-SOMST, and SOYDSP-SOMAST were removed; these relations should go through SOHSHP instead.
• Refreshing the data dictionary from the Sage Pro system database in the SDK Configuration Utility no longer adds system tables to the Stonefield Query data dictionary.
• Selecting an invalid directory for the Sage Pro folder in the Locations page of the Options dialog now displays the correct directory name in the error message that appears.

Using Stonefield Query on 64-bit Windows

Although it's a 32-bit application, Stonefield Query works just fine on 64-bit versions of Windows, including Windows Vista, Windows Server 2003, and Windows Server 2008.

One known issue with Windows, however, is that 32-bit applications can access 32-bit system DSNs, 32-bit user DSNs, and 64-bit user DSNs, but not 64-bit system DSNs. Since the default ODBC Administrator is the 64-bit version (you can run the 32-bit version from the SysWoW64 subdirectory of the Windows folder if necessary), any system DSNs you create aren't visible to Stonefield Query. So, either create user DSNs rather than system DSNs or use the 32-bit ODBC Administrator to create your DSNs.

This impacts any version of Stonefield Query that uses ODBC DSNs rather than connection strings, including:

• Stonefield Query SDK (if you turn on the User Can Manage Data Sources configuration setting)
• Stonefield Query for GoldMine
• Stonefield Query for HEAT
• Stonefield Query for MAS 90 and 200
• Stonefield Query for Sage Accpac ERP
• Stonefield Query for Sage BusinessVision
• Stonefield Query for Sage Pro ERP (SQL version)

Stonefield Query Version 3.2 Build 3082

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

• You no longer get an error in the Registration dialog if you renamed the ADMIN user.
• The size of Avery 5266 labels was fixed so they now print properly (the sizing information provided to us by Avery was incorrect).
• You can no longer drag a report or folder to a blank portion of the reports list; previously, this caused the report or folder to disappear.
• The titles for the Dynamic Color and Dynamic Font Style dialogs are now localizable.
• The Dynamic Color and Dynamic Font Style dialogs and all dialogs in the Advanced Report Designer now use the same icon as the rest of the Stonefield Query dialogs.
• Certain types of fields that gave an error when used in a filter condition that compares another field to them no longer do so.
• Fields that use a drop down list of values in the ask-at-runtime Filter Condition dialog now display the same value as the last time when you run a report several times.
• Filtering on some types of calculated fields when those fields aren't included in the report no longer causes an error.
• Stonefield Query is no longer sensitive to the date format for dates passed as parameters to linked reports.
• Running a report that contains links no longer loses the list of databases the second time you run it. Also, the filter conditions in the FilterConditions object are now valid between runs of linked reports. Also, the header for the parent report is no longer changed to that of the child report.
• Issues previewing reports when Windows is set to Chinese (PRC) have been corrected.
• Turning off the Display Date Only option in the Field Properties dialog for a datetime field resulted in that field being displayed as MM/DD/YYYY rather than the correct Windows setting when used as a filter condition. This was corrected.

SDK

• Errors that occurred when generating help files when Windows is set to Chinese (PRC) no longer occur.
• The Generate Setup function now include the logo image file in the generated ISS script file.
• You can now specify a custom control class to appear in the Setup dialog using the Setup.Settings script.
• An error that occurred when you selected a data group or user group in the Configuration Utility when you have multiple data dictionaries and the second data dictionary contains the same tables as the first one but in different data groups was fixed.
• Setup scripts generated from the Configuration Utility now put the runtime files in the Stonefield Query program folder.
• Although this situation doesn't make sense, Stonefield Query no longer gives an error if you use the Display Field From Related Table option on a foreign key field and the field you choose to display is the primary key from the related table and these fields have the same name.

Sage Accpac ERP

• Application startup performance was improved.

Sage Pro ERP

• We've added support for Return Merchandise Authorization (RM) tables.
• The Detail Line Explanation field in the GL Posted Transaction Details File (GLDENT.GLTDES) now displays the actual description rather than XML.
• Some missing enumeration values were added, including "C" (for "Credit Card") to the PPRIORITY field in APMAST, APMST, and APYMST and "D" (for "Drop Shipment") to the SOTYPE field in SOYTRN and SOTRN.

Stonefield Query Version 3.2 Build 3062

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

• An advanced layout report with a link on a field no longer gives an error when you click the linked field in the Preview window.
• Using the RunSQL function in an advanced layout report no longer gives an error if the SQL statement passed to the function includes a table that isn't in the data dictionary.
• Converting reports from version 2.4 or earlier now properly handles corrupted reports.
• A bug which s0metimes caused the totals column in cross-tabulation reports to show the wrong totals when output to HTML Table Format was fixed.
• A custom SQL statement containing an aggregate function such as COUNT(SomeField) AS SomeField is now handled properly.
• Linking on a date field no longer gives an error.
• An error that sometimes occurred when perf0rming a multi-database query was fixed.
• A bug that caused an error message with an older license file was fixed.
• You no longer get an error when previewing a report called from another application.
• A bug in the Schedule Report Wizard using a weekly schedule on Windows XP was fixed. Also, the scheduler will no longer give an error if the time for the schedule is between 12:00 AM and 12:59 AM or 12:00 PM and 12:59 PM
• You no longer get an error if the Source Database field is the first one in a report.
• Stonefield Query now correctly handles a couple of obscure cases. The first is where a field delimited because its name contains illegal characters (such as [ID-TEST]) that exists in more than one table is involved in the output expression of calculated fields in those tables and both calculated fields are included in the same report; in that case, the SQL statement was erroneously determined as Table1."ID-TEST" AS ID_TEST, Table2."ID-TEST" AS "ID-TEST"_A. The second is when a report has at least two tables and one of the table names starts with a digit (for example, [1099Categories]) and Stonefield Query is configured to perform joins; this case formerly gave an "alias not found" error.
• The Test button in the Manage Databases dialog no longer crashes if a DSN for a different application was chosen but instead gives a warning message.
• The Import Report function no longer gives an "invalid import file" error with older import files.
• An advanced layout report that's linked to another report no longer gives an error when you click the link after importing both reports.
• Stonefield Query now passes the correct value to a grandchild report that's linked to a child report that's passed the value of a filter condition from a parent report.
• A field name that's a reserved word (such as [LINENO]) no longer causes an error when it's used in the output expression of a calculated field.
• The Value to Pass column in the grid in the Link step of the Field Properties dialog now shows the field caption rather than its heading, so it no longer displays "<BR>" in fields that have multi-line headings.

Sage Pro ERP

• A bug that gave an error the first time you connect to a Pro SQL Server database was fixed.
• Stonefield Query now supports companies with only a single Pro module installed.
• Clicking the Values button for the Record Type field in a current + history table now properly displays "Current" and "History" in the list.
• The caption for ARCSUM was changed from Customer History File to Customer Sales History because another table has Customer History File as its caption.
• Clicking the Values button for a field in a SQL Server database now works correctly.
• "Drop Shipment" is now available as one of the values for SOTRAN.SOTYPE.
• All YEARPRD fields now display the full seven digits.
• VFP runtime files are now placed in the Stonefield Query program folder to prevent an issue with different service pack releases of these files.
• Clicking the Close box for the Advanced Report Designer no longer gives a warning that you must close the Advanced Report Designer but instead actually closes the Advanced Report Designer.

Sage Accpac ERP

• The Accounts table now only appears once in the General Ledger module and also appears in all other modules when "All" is selected.
• Stonefield Query now properly handles optional fields for record that have quotes in their key fields (for example, "DOUG'S" in the Customer ID field of the Customers table).
• The captions of several fields were made more understandable.
• Optional fields in CPCHKD and UPCHKD are now handled properly.

SDK

• The installer now installs the version 3.2 SFQUERY.PDF rather than the 3.1 version.
• Adding a new empty database no longer makes it the main database unless it's the first database added.
• You no longer get an error in the Configuration Utility when you select an empty database.

Localization

• Typos in some Dutch translations were fixed.
• The title for the Reports Explorer window and the "Available to" setting on the Information page are now localizable.
• The Advanced button in the report wizards now sizes itself properly when localized.

Stonefield Query Version 3.2 Update

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

All Versions

• Thanks to Boudewijn Lutgerink, the Dutch version of Stonefield Query is now current with version 3.2.
• An error that occurred if you ran a report that uses the RunSQL function in the Advanced Report Designer was fixed. Also, editing a field using that function no longer executes the function.
• A bug that caused an error if you created a new report then right-clicked in the Reports Explorer on a folder was fixed.
• A bug that caused the Favorites folder to appear as "Favorites" in some dialogs rather than the localized equivalent when the language is set to something other than English was fixed.
• Stonefield Query now has consistent behavior if you try closing it by clicking the Close button, right-clicking and choosing Exit, or choosing Exit from the File menu when a report wizard is open.
• You no longer get an error if you press Esc during the "processing records" step when running a long report.

Sage Pro

• The Analyze Database function now works correctly with Sage Pro databases.
• The Pay Priority field in the Accounts Payable Master File in the Sage Pro version now supports a value of 0 for Payment Hold.
• Stonefield Query now supports the case where you've customized the SQL statement for a report and used code that includes multiple SQL statements.
• It now does a better job when updating from an older ACCPAC Query version.

Sage Accpac

• Date fields in the Sage Accpac version now display as true date values rather than numeric values.
• Some joins were optimized in the Sage Accpac data dictionary, making some reports (especially those involving Accounts Receivable Items) faster.
• A bug that prevented reports involving Order Entry Invoices and Invoice Details from working correctly when you have more than one data dictionary was fixed.
• It now does a better job when updating from an older ACCPAC Query version.

SDK

• A bug that gave an error when creating help files for a project when you're running the Configuration Utility on Windows Vista or Windows XP using a limited user account was fixed. Also, it now gives a warning rather than an unexpected error if the folder for the help files is read-only.
• The update process now displays the correct name of the updater application if you've changed it when the user runs the Check for Updates function and an update is available on a Windows Vista system.
• Stonefield Query now supports updating project components, such as the data dictionary files.

Stonefield Query for Sage Pro ERP Version 3.2 Released

Stonefield Software Inc. is pleased to announce the release of version 3.2 of Stonefield Query for Sage Pro ERP.

Stonefield Query is a user-friendly report writing, business intelligence, query, and data mining tool. With its intuitive wizard driven design, Stonefield Query makes report writing a snap for even the most novice user. Elegant and persuasive reports can be created in minutes with little or no technical knowledge required.

Stonefield Query for Sage Pro ERP was formerly available from Sage Software as ACCPAC Query. Since ACCPAC Query was returned to us, we've rebranded it as Stonefield Query and synchronized the version number with our other customized versions.

Some of the new features in this release are:

• Charting: you can now quickly create charts and graphs such as these sales by salesperson charts in just a few mouse clicks:

 

• Drilldowns: you can, for example, create a report that when you click on the customer name drills down to show the orders for that customer.
• Exclusion filters: this allows you to query on the absence of data, such as "give me a list of customers I didn't contact last year" or "give me a list of everyone who did not buy product X". Behind the scenes, Stonefield Query generates a NOT IN subquery, but from the end-user's perspective, it's just as easy as creating a normal filter.
• Modeless report wizards: the report wizards are no longer modal, so you can modify more than one report at a time.
• Single ask-at-runtime dialog: all ask-at-runtime filter conditions now appear in a single, wizard-like dialog rather than one dialog per condition.
• Workstation installer: Stonefield Query now has a separate WSSetup.EXE installer, installed by Setup.EXE, that does workstation installations with the minimum of user interaction.
• More grouping options: you can now specify that a non-grouped field should be included in the group header for another field, you can have all fields in the group header on a single line rather than stacked on separate lines, and all fields in the group header now appear in summary reports.
• Multi-line headings: many fields now have multi-line headings so they don't take up as much room in a report, and you can change field headings to use multiple lines.
• Eliminate tables and fields you don't use: unless you use every single feature in Accpac (and who does?), you see a lot of tables and fields in Stonefield Query that don't contain meaningful data. Use the Analyze Database function to hide things you don't care about.
• Customize Report Wizard for all report types: all report wizards now have an Advanced button, allowing access to TOP N, custom SQL statements, and other features.

There are dozens of other new features and bug fixes. For more information about version 3.2, please see the What's New in This Version topic in the online documentation.