Stonefield Query for Sage 300 Version 6.1.6467.

This version adds compatibility with Sage 300 2018 and is also a maintenance release. It has the following new features:

• The performance of outputting a report with a large number of records (tens of thousands) to Microsoft Excel Data-Only (Fast) was greatly improved (90% faster).
• The Order Entry Sales History (OESHHD) and Sales History Details (OESHDT) tables now have Net Amount (Functional) and Net Amount (Source) fields (FNETAMT and SNETAMT, respectively), which are sales amount less returns amount.
• There's now a relationship between the GL Posted Transactions table (GLPOST) and the AP Posting Journal Entries table (APPJH) so you can drilldown from a GL entry to the matching AP entry.
  

Stonefield Query for Sage 300 Version 5.2

We are please to announce the release of version 5.2 of Stonefield Query for Sage 300. There are lots of new features in this release.

Report Designer

• There are two new settings on the Grouping page of the Properties dialog for fields in step 2 of the Quick Report Wizard: Retrieve all records and Number of records per group. Turn off Retrieve all records and enter a value for Number of records per group to limit the number of records per group. You can combine this with sorting on one of the fields in the detail for the group to see the oldest or newest record. For example, suppose you want to see only the most recent activity for a customer. To do that, group on customer name, set Number of records per group to 1, and sort on the activity date in descending order.
• There's a new setting for formulas: Calculate value at group level. This setting allows certain formulas to be re-calculated at the group level rather than just being summed, such as profit margin or other types of ratio calculations for which summing and averaging don't work. To indicate that the formula should show group values in a report, set the Summary option for the formula in the report to a new choice: Group Calculation.
• A new type of filter operator is available for date fields: Is. When you choose this operator, you can select a date type to compare the field to, such as This Week, Last Year, or This Month to Date, from a drop-down list.
• Dashboards can now include all types of reports, not just charts and gauges. Note, however, that the dashboard window doesn't scroll so it's really only appropriate for short, informative reports.
• Step 2 of the report wizards now shows information about the field under the mouse pointer in a "balloon" tip. For the Available fields list, the balloon shows any comments about the field. For the Selected fields list in a quick report or label, the balloon shows how the field is formatted in the report: whether it's grouped or summed, what numeric or date format is used, and so on. This allows you to see at a glance information about the field without having to click the Properties button.
• You can now select more than one grouping field for a grouping formula. This is necessary for a formula that, for example, show sales for a certain date range by customer and product.
• A new type of output is available: Microsoft Excel - Data Only (Fast). This is similar to Microsoft Excel - Data Only but is faster and doesn't require that Excel is installed on the system. This format works better that Microsoft Excel - Data Only with scheduled reports because Excel doesn't always work correctly when run from a scheduled task.
• You can now specify whether category and series fields are sorted in ascending or descending order in a chart.
• You can now specify a background color for fields in a report in the Properties dialog. Sample text shows what the colors of the field will look like in the report. You can also revert to the default color for the field by turning on the new Use default color setting.
• You can now export and email a report layout to someone in one step: right-click the report in the Reports Explorer and choose Email Report File, enter their email address, and click Send.
• The Filter page in the Reports Explorer and report wizards has a new setting: Warning if no filter when report is run. This used to be a global option in the Options dialog but is now available on a report-by-report basis. The setting in the Options dialog was changed to Default warning if no filter when a report is run and it now determines what the default is for that setting for new reports.
• You can now specify the font for X-axis, Y-axis, and point labels in charts.
• When you output a cross-tab to a Microsoft Excel PivotTable, the PivotTable now supports the Show percentage of total, Show difference from previous column, Show percentage change, Column totals, Row totals, Display date as, and numeric format settings for the cross-tab report.
• The Options dialog no longer has a Date Format setting; the application now always uses the date format specified in the Windows Control Panel.
• If no output settings are specified when a report is run from the command line, the saved output settings for the report are used if there are any.
• If an advanced layout report has any images, such as a company logo, those images are included in the SFX file for the report. If you export the report and send the SFX file to someone but forget to send them the image files used by the report, the Import process they use to add the report to their reports list creates a new folder called Images and puts the images imbedded in the SFX file there. The benefit of this is that you no longer get an error about a missing image in this case.
• Some settings in the Options dialog now affect all users on a network: Create and display report snapshots, Send summary queries to database engine, Week starts on, and Year starts in. These settings are saved in Data.ini in the Data subdirectory of the program folder rather than in the Windows Registry where other settings are saved.
• The Show totals in group headers setting for quick reports is now available for advanced layout reports.
• The positions of the Advanced Layout, Chart, and Edit buttons in step 5 of the Quick Report and Cross-Tab Wizards was changed a little to accommodate other controls.
• The Alignment setting in the Properties dialogs for fields now displays icons for left, right, and center alignment.
• The new sample report Inventory Valuation in the Sample Report\IC Reports folder shows the inventory valuation by item as of a specific date without regard to location.
• You can now report on the Certified Payroll Report Jobs (CPCPRJ) table in the Canadian Payroll module.

Stonefield Query for Sage 300 ERP Version 5.0

Stonefield Software Inc. is pleased to announce the release of version 5.0 of Stonefield Query for Sage 300 ERP. This release has numerous new features.

• Stonefield Query has a new report type: gauge report. This allows you to create a gauge showing the current value of something compared to a target or goal value.
• You can now create a "grouping" formula. A grouping formula is a powerful tool that allows you to create filtered summary values. For example, suppose you want columns in a report that show the sales for last year and the current year-to-date for each customer. Those aren't single values retrieved from the database. Instead, for the first column, use a grouping formula that sums up the sales records for each customer for last year, and use a similar one but for the current year for the second column.
• The Inventory Control Location Item Details table now also appears in the Purchase Orders module.
• The Inventory Items table now has a Total Quantity on Hand field that contains the total quantity on hand from all locations.
• The Inventory Shipments related tables no longer appear in the Order Entry or Purchase Orders modules to avoid confusion with the shipments tables already in those modules.
• You can now password protect the file created when a report is output to file or email.
• You can now print to Dymo LabelWriter labels.
• The Schedule Report function is now available only to advanced users.
• A new output type is now available: Microsoft Excel - Full Format (*.xlsx), which creates Excel 2007 or later files. The former Microsoft Excel (*.xls) output type was renamed to Microsoft Excel 97-2003 - Full Format (*.xls) so you can tell them apart. The new type does not require Microsoft Excel to be installed to create the file.
• The "red" error dialog that appears when something goes wrong now has a Help button that brings up the help with information about that dialog.
• The Locations page in the Options dialog is now available only to administrative users.
• The Subscription Expired dialog, which appears when you have subscription licenses and your subscription has expired, now has a "Don't remind me again" option; turn that option on to prevent the dialog from appearing again. Also, those licenses are now deactivated rather than being deleted, so if you renew your subscription, they are reactivated.

Stonefield Query for Sage 300 ERP Version 4.3.5242

There are numerous new features in this release.

• Stonefield Query for Sage 300 now works with Sage 300 version 6.2 and earlier.
• A new chart type is now available: Donut, which is similar to Pie but with a hole in the middle.
• The default settings for a new chart give it a more modern appearance, such as flat rather than 3-D and better colors.
• You can now define a chart with a second values field; this field is charted as a second series against the right axis.
• The Output each group as a separate file and Output each group as a separate email options now support charts on quick reports. In that case, each file has its own chart related to the group the file is for.
• The Filter Condition dialog now uses a more modern date picker control.
• The Add timestamp to file name setting is now available even when Erase file after email sent setting is turned on in the Output page of the Reports Explorer.
• A new output type is now available: Microsoft Word (*.docx), which creates Word 2007 or later files. The former Microsoft Word (*.doc) output type was renamed to Microsoft Word 97-2003 (*.doc) so you can tell them apart. The docx format is preferred as it's more accurate.
• The Check for Updates function in the Help menu is now only available if you are logged into Stonefield Query as an administrator.
• You can now change settings in the Format page of the Field Properties dialog for numeric fields in columns of cross-tab reports.
• The prompts in the Email page of the Options dialog have been changed to reflect the fact that SMTP is the preferred setting since MAPI can be unreliable, especially when emailing a scheduled report.
• There are now relationships between the Purchase Orders Invoices and Accounts Payable Vendors tables, between the Accounts Receivable Receipts/Adjustments and Adjustment G/L Distributions tables, and between the Return Material Authorization Details and RMA Kitting Details tables.
• You can now report on the Unit Price and Quantity fields in the Accounts Receivable Recurring Charge Details table.
• You can now edit a formula in step 2 of the report wizards when Application View is used.

Stonefield Query for Sage 300 ERP Version 4.3

We are pleased to announce the release of version 4.3 of Stonefield Query for Sage 300 ERP (formerly known as Sage Accpac ERP). Here are the new features in this version.

• You can now create batch reports. A batch report automatically runs several reports, outputting each to the desired destination. This is handy if, for example, you normally run the same three reports at the end of every week. Rather than choosing them one at a time, you can create a batch report that combines those three reports and just run it. You can even schedule batch reports.
• You can now append the output of a report to an existing Microsoft Excel output file. In that case, the report appears in a new worksheet (tab) within the spreadsheet. This even works if the file is already open in Excel. You can also now choose XLSX or XLS as the extension for a Microsoft Excel output file.
• Turning on the new Start each group on a new page option for the first row field in a cross-tab report causes each group of records to appear on its own page. You can also now turn on the Output each group as a separate file and Output each group as a separate email settings so each group is output to its own file or email.
• The new Delimited - Microsoft Excel Format output type outputs to a comma-separated values (CSV) file but uses Excel formatting so things like leading zeros are preserved when the file is opened in Excel.
• Formulas are now stored in the same folder as reports (in a file named CalcFields.DBF) so if the program runs on a workstation but reports are shared on a server, so are the formulas. For backward compatibility, the program will still look for CalcFields in the Data subdirectory of the program folder so you won't lose your formulas, so this works better for new installations rather than existing ones.
• You can now enter a formatted SQL statement in the SQL Statement step of the Customize Report dialog. For example, carriage returns and tabs are now permitted. This makes it easier to take a SQL statement from another application and paste it into a report's SQL statement. Note that this change now requires you to prefix procedural code with "*-" if you want to use code rather than a SQL statement (a rare occurrence).
• Microsoft Excel full-format output works better now: the output has a single page header at the top of the document and a single page footer at the bottom rather than header/footer sections where the pages would normally break, and the use of empty columns and rows for positioning is minimized.
• Stonefield Query now handles font scripts other than Western, such as Cyrillic or Greek. You can select the desired script in the Font dialog that appears when you click the font button in the Field Properties dialog of a report wizard or in the Advanced Report Designer.
• Clicking the Add All or Remove All buttons in Step 2 of the report wizards now display a prompt asking you to confirm if you want to do that to prevent inadvertently adding all fields in a table to a report or removing all fields from a report.
• The application no longer erases the diagnostic log (Diagnostic.txt in the Data subdirectory of the program folder) if KeepLog=Y is in Data.INI. This is useful for checking diagnostics over multiple runs, such as for scheduled reports.
• You can now resize dashboard windows to a much smaller size than before.
• You can now edit a chart that's included in a quick report with an advanced layout. The Size page in Step 4 of the Chart Wizard is now disabled in that case, since you cannot change the chart size.
• You can now click the serial number in the About dialog (available from the Help menu) to copy your serial number to the clipboard. This makes it easier to enter the serial number when creating a ticket in our online support ticket system.

Stonefield Query for Sage Accpac/300 ERP Version 4.2.4603

Version 4.2.4603 has several great new features.

• Stonefield Query for Sage Accpac ERP has been renamed to Stonefield Query for Sage 300 ERP because Sage renamed Sage Accpac ERP to Sage 300 ERP. Please note that regardless of the name, Stonefield Query works with Sage 300 ERP and Sage Accpac ERP versions 6.0 and earlier.
• The new Auto-fit to page setting for quick reports automatically reduces the font when fields would normally be moved to a second row because there are too many to fit on one. This makes it possible for the fields to fit on a single row.
• The new Find Field button in step 2 of the report wizards makes it easier to find a field by part of the caption.
• Reports can be combined into a single PDF output by specifying the same file name for each report and turning on the new Append to existing file setting.
• The Dashboard window now supports outputting all charts to a single PDF file.
• You can now sort a chart by values rather than the default alphabetically by category by setting the Sort setting in the Field Properties dialog to Ascending or Descending Order.
• Stonefield Query now supports printer custom forms specified in templates. This allows you to, for example, create a template that's very wide (using Tabloid size paper) so fields don't wrap onto a second row when there are lots of fields.
• You can now define multi-line point labels for charts in Windows XP (it was only available in previous versions for Windows Vista and 7).
• You now get a warning if you copy an expression from the Expression Builder and paste it into a place where real table and field names are required, such as the expression for a formula or a field in the Advanced Report Designer. Previously, you would get an error when running the report.
• Relationships were added between the Accounts Receivable Receipt/Adjustments (ARTCR) and Invoices (ARIBH) tables, the Accounts Receivable Applied Receipt/Adjustments (ARTCP) and Order Entry Invoices (OEINVH) tables, and the Accounts Receivable Invoices (ARIBH) and Order Entry Invoices (OEINVH) tables.
• The Applied Receipt/Adjustments (ARTCP) table now appears in the Order Entry module as well as Accounts Receivable.
• The Payroll Fields settings in the Options dialog can now accept longer lists of code values.

Stonefield Query for Sage Accpac ERP version 4.2.4526

Version 4.2.4526 is mostly a maintenance release, but there are a few new features:

• There are now relationships between Purchase Order Requisitions (PORQNH1) and Requisition Vendors (PORQNLV), Accounts Receivable Receipts/Adjustments (ARTCR) and Account Sets (ARRAS), Receipts/Adjustments (ARTCR) and Documents (AROBL), and Applied Receipts/Adjustments (ARTCP) and Documents (AROBL).
• The Payroll Check Advice report now includes the same information the Sage Accpac report does, including balances and accruals.
• There is now an Earning/Deduction Balance field in the Payroll Check Details table and a Net Balance field in the Employee Earnings/Deductions table.
• You can now define user group names up to 60 characters (the previous limit was 20).
• You can now define report names up to 100 characters (the previous limit was 60).
• You can now create an advanced layout report from a report with Summary turned on.

Stonefield Query for Sage Accpac ERP version 4.2

We are pleased to announce the immediate release of Stonefield Query for Sage Accpac ERP version 4.2. There are lots of new and improved features in version 4.2.

• You can now edit formulas from Step 2 in the report wizards. When a formula is selected, click the Edit Formula button to edit it. Otherwise, that button appears as "New Formula", so clicking it creates a new formula.
• Formulas now appear in report wizards with "[Formula]" after the name so you can distinguish them from real fields.
• You can now specify the order that a formula appears in its table when fields are displayed in table order. This is useful if you want a formula to appear somewhere other than at the end of the list of fields.
• You can now specify the number of rows a dashboard has. For example, if there are four charts in a dashboard and you specify two rows, the dashboard will have two rows of two charts each.
• Two new templates are available:
  • Modern: this template has alternating light and dark rounded rectangles in the detail band and a blue rounded rectangle for column headers.
  • Modern with Title: this template is similar to Modern but with a title page showing the report name, comment, user, date, and filter.

  To add these to the list of available templates, choose Template Editor from the Tools menu, click Import, and select these templates from the Sample Templates folder.

• Objects in templates have a new Center option, which is useful for images such as logos that you want to be centered horizontally on the page.
• Stonefield Query can now automatically sum numeric fields when you add them to a report. Turn on the Automatically sum numeric fields setting in the Options dialog to use this feature.
• The Format page of the Field Properties dialog of the Advanced Report Designer now has a Display date only setting that works like the same setting in the Properties dialog of the report wizards: turn this setting on to display the date only or turn it off to display the date and time.
• You can now specify whether missing date values are included in a chart when a date field is used in as the category (X-axis) field.
• The License Manager now includes a Copy button to copy the selected serial number to the Clipboard. This makes it easier to specify your serial number when requesting technical support.
• You now get a warning if you try to schedule a report that has ask-at-runtime filter conditions, since that prevents a scheduled report from running.
• Because exclude filter conditions are often erroneously used in place of a normal "not equals" filter (for example, you want a list of customers who aren't in Germany and use an exclude condition of Country equals Germany rather than the correct way, with a normal filter condition of Country does not equal Germany), you now get a warning if you try to create an exclude condition of this type, although that doesn't prevent you from using it.
• Most cross-tab reports run faster now.
• You can now output cross-tab reports that have Show Change or Show % Change turned on to Microsoft Excel, CVS, or PivotTable files even when the report can't be previewed or printed because it has too many columns.
• Stonefield Query starts up faster than before.
• The setting of the "display only directly related tables" button is no longer remembered because it was easy to miss that this option was turned on and then wonder why all of the tables weren't displayed.
• Turning on Use same options next time report is run on the Output page of the Reports Explorer now saves the selected printer if it isn't the default printer. This allows you to always print the report to the same printer, such as sending mailing labels to a label printer.
• You can now specify the name of a report to edit or that a report should be created with the New Report Wizard with command-line parameters.

Stonefield Query for Sage Accpac ERP version 4.1.4246

We have released version 4.1.4246 of Stonefield Query for Sage Accpac ERP. This release has several new features:

• You can now report on Orion Point of Sale (POS) data (http://www.odiglobal.com).
• You can now define security for individual databases in the Maintain Users and Groups dialog so only certain users can access certain databases.
• A new AR Combined Documents & Adjustments table combines records from AR Documents (AROBL) and Receipts and Adjustments (ARTCR), making it easy to create a listing of all AR transactions for a customer.
• A new Earning/Deduction YTD Amount field is now available in the Canadian Payroll Cheque Details (CPCHKD.YTDAMT) table. New YTD Earnings, YTD Net Pay, Current Earnings, Current Deductions, Current Taxes, and Spelled Out Amount fields are now available in the Cheque Header table. These fields were also added to the U.S. Payroll Check Details and Check Header tables. These fields make it easy to create a payroll advise report (see the next point).
• New Cheque Advice (Canadian Payroll) and Check Advice (U.S. Payroll) sample reports make it easy to email payroll advices for a specific pay period to employees. These reports require Sage Accpac ERP version 5.5 or later.
• New Total for Year calculated fields, such as Total Quantity for Year and Total Cost for Year, were added to the Project Costing Budget Detail (PMBUDD) table because the existing Accpac totals fields are always zero.
• New Unisales Combined Invoices & Credit/Debit Notes (UXINVHCRDH) and Combined Invoice & Credit/Debit Note Details (UXINVDCRDD) tables are available, making it easier to show invoices, credit notes, and debit notes from Unisales on one report.
• Lot tracking tables are no longer available if you're using Accpac 5.6 or later since Sage removed those tables. Unfortunately, that means that reports using those tables have to be recreated from new lot tracking tables in Inventory Control.
• A new Certified Payroll Report Jobs (UPCPRJ) table is now available in U.S. Payroll.
• Performance was improved for some types of calculated fields.
• You can now turn on the "Suppress repeating values" setting for the last row field in a cross-tab report if desired.
• You can now specify CC and BCC email addresses when emailing different pages in a report to separate addresses (which you do by turning on "Output each group as a separate file" and setting "Get email address from" to a field).
• Date/time fields with the "Display date only" setting turned off can now be used in the row of cross-tab reports.

If you are using an earlier version 4.1 build, you can install the update using the Check for Updates function in the Tools menu of the Report Designer.

Stonefield Query for Sage Accpac ERP 4.1 Released

We are pleased to announce the immediate release of Stonefield Query for Sage Accpac ERP version 4.1. There are lots of new and improved features in version 4.1.

New Features

• You can now define dashboards using the new Dashboard Wizard. A dashboard is a set of chart reports that displays on your desktop in its own window. These charts automatically refresh at an interval you specify so you can always see the most current data.
• Sage Accpac ERP 6.0 is now supported.
• The US Payroll Check Details and Canadian Payroll Cheque Details tables have new user-definable calculated fields that contain the amounts for certain earning and deduction types. For example, the Earnings Gross field contains the gross earnings amount and the Pension Employer field contains the employer portion of the pension deduction. These fields make creating payroll reports much easier, especially summary reports showing employee total amounts by deduction type. You can define the fields you want to use in the Options dialog.
• The AR Documents table has a new Starting Balance field that shows the starting balance for the fiscal period the record is in.
• Stonefield Query now supports Windows 7:
  • When you run a report, the taskbar button shows a moving green bar indicating something is happening.
  • You can now right-click a report and choose Pin to Taskbar to pin it to the taskbar; choosing that item in the taskbar launches Stonefield Query and previews that report (this obviously works best when Stonefield Query itself is pinned to the taskbar).
  • Stonefield Query report files, which have an SFX extension, now display a nice report icon in Windows Explorer. Double-clicking an SFX file launches Stonefield Query and previews that report.
• You can now create a desktop shortcut for a report. Right-click any report in the Reports Explorer and choose Create Desktop Shortcut. This will create a shortcut on your desktop with the same name as the report. Double-clicking this shortcut previews the report without opening Stonefield Query, which is handy if you just want to run one report without having to find it in the Reports Explorer.
• Usage statistics are now maintained for reports, and the new Usage Reporting function in the Tools menu allows you to report on these statistics. This allows you to determine which reports are actually being used, how often, and by whom.
• A new type of display for date fields is now available in the Field Properties dialog: "Month/Year". This displays the month and year part of the date, which the "Month" option formerly did. "Month" now outputs only the month part of the date, which is handy, for example, in cross-tab reports where you want to show the date in the rows and the year in the columns to compare sales by month and year. Reports you created prior to version 4.1 using "Month" are automatically changed to use "Month/Year" so they work as they did before.
• When you install a new version of Stonefield Query over an existing one, if there are any new or modified sample reports, a dialog appears telling you that and allowing you to select which of those to import. You can also choose the Check New or Updated Sample Reports function in the Tools menu.
• You can now configure how much memory Stonefield Query reserves for itself at startup. This is especially useful in Windows Terminal Server environments where multiple users run Stonefield Query on the same server at the same time.

Charts

• Multi-page charts are now supported. In the new Size/Paging page of the Chart Wizard, turn on Paging and set the number of points you want on each page.
• Linking, such as drilldowns, from charts is now supported. The Properties dialog for the Category (X-axis) field has a Link page that works the same as other report types. This allows you, for example, to display a chart showing sales by salesperson and click a certain salesperson to drill down to a report showing individual sales for that salesperson. Linking even works in charts displayed in a dashboard. To support this feature, a different preview window is used for charts. It has the same features as the preview window used for other report types but cannot used tabbed pages showing other report types, only other charts.
• Charts now use the Segoe UI font for text in Windows Vista or later because it looks better, especially when rotated.

Cross-Tab Reports

• Cross-tab reports now support displaying the amount and/or percentage change between columns. The Display page of the Properties dialog for a data field has new "Show difference from previous column" and "Show percentage change" options. You can turn on one or both of these.
• You can now specify whether cross-tab reports include row and column totals using new options in Step 5.
• The Select only top option in a cross-tab report now applies to the totals for the first row field rather than the row field itself. For example, setting "Select only top" to 10 now displays the top 10 values rather than the top 10 alphabetically.

Other Features

• Emailing now supports SSL/TSL for SMTP, which means you can now email using Gmail or Hotmail accounts or other SMTP servers that require SSL/TSL. Also, emailing using MAPI with Microsoft Outlook as your email program no longer displays the dreaded Outlook security dialog.
• Certain characters that weren't allowed in the name of a report, such as a period, comma, question mark, slash, and so on, are now allowed. The only character you can't use is a backslash ("\").
• Certain characters that weren't supported in the body or subject of an email, such as "&" or ">", are now allowed.
• You no longer have to contact us if you have to activate the program on a different computer than it was originally activated on. Online activation will automatically activate this computer and deactivate the license on the other one.
• There's better support for adding images stored in your database to reports.
• Output to text, comma-separated (CSV), table, and XML files is now faster.
• Exclusion conditions now work faster than before.
• The Preview window now remembers the setting of the zoom option. If you run a report and change the zoom to 200%, the next time you preview a report, it'll be at 200%.
• The Ask At Runtime filter and report progress dialogs now always appear on top of any other windows so they can't get lost behind something else if you click on a different window.
• The Setup dialog has a new page (page 2) for contact information.
• The activation process is now simpler. Clicking the Click here to activate the program now link in the Welcome dialog (displayed when you're running an unactivated version) now displays the License Manager dialog where you can add as many licenses as you need rather than bringing up a Registration dialog that only allows one license to be activated.
• New templates are automatically imported when you run Stonefield Query.
• The OnSelect script for a report can now access the report using the Report object. This is useful if, for example, you want to change the default filter for a report when it's selected.
• You can now use the built-in RunSQL function in formulas.
• It's easier to work with the Dynamics tab in the properties dialog for Pictures and Rectangles in the Advanced Report Designer. You no longer have to enter values in 960th's of a inch; instead, you can enter value in inches or centimeters, depending on how your system is configured.
• File dialogs, such as those displayed when you output to file or export a report, now support Windows Vista and later features, such as search and recent places.
• The ask-at-runtime filter dialog now appears on top of the report progress window so it doesn't get hidden behind other windows.
• The new GetValueForParameter function is like the existing GetValueForField but allows you to specify the caption, data type, and size of the parameter being requested from the user.
• You can now filter on the Source Database field. This allows a report to drill down based on a data source. For example, suppose you have a cross-tab report that retrieves data from multiple data sources and it has Source Database as the column field. Clicking a specific data source should drilldown and run a detail report for that data source only, so the linked report has a filter on Source Database.
• The progress dialog that appears when running a report that retrieves data from multiple databases now shows the name of the database the data is currently being retrieved from and the number of records retrieved.

Stonefield Query for Sage Accpac ERP 4.0

We are pleased to announce the immediate release of Stonefield Query for Sage Accpac ERP version 4.0. There are lots of new and improved features in version 4.0.

We have a video covering some of the new features in the Report Designer.

Report Wizards

• You can now customize the relationships between tables in a report without having to edit the SQL statement for the report.
• Support for Database View versus Application View in Step 2 of the report wizards. The difference between the two is that Database View displays a database-centric view of the data in your application--that is, organized the way the tables and fields are in the database--while Application View shows fields the way your application does in its various dialogs. Database View is useful if you are familiar with the structure of your application's database while Application View is better suited to those familiar with the user interface of the application
• You can now turn on the Show percentage of total option when you use the Count and Count Distinct summary types in Quick and Cross-Tab reports.
• You can now use the Highest and Lowest summary types on all fields, not just numeric ones.
• You can now create a filter condition that compares a field in one table to a field in another table by using an expression condition.
• Creating a formula by clicking the Formula button in Step 2 of the report wizards now automatically adds the new formula to the report.
• The list displayed when you click the Values button in Step 2 of the report wizards or the Expression Builder now displays two columns of information for certain fields: the value displayed in reports and the actual value stored in the database.
• If Stonefield Query closed due to a problem and you were in the middle of editing a report in a report wizard, the next time you start the program, it prompts if you want to recover and continue editing that report. Choosing No abandons the recovered report so the changes are lost.
• You now have finer control over the placement of a report's filter in a custom SQL statement.

Quick Reports

• You can now group on a field even if the Display this field in the report option is turned off. This is useful, for example, if you want to group on customer ID but display customer name in the group header.
• You can now create bookmarks on fields that are included in the group header of another field.
• You now get a warning in the Field Properties dialog if you turn on the Include this field in group header for option and select a grouped field in a different table than the current field.

Charts

• New options in the Chart Wizard allow you to stack series in area, bar, and column charts and combine smaller slices into one "other" slice in pie charts.
• You can now specify whether a chart added to a quick or cross-tab report appears at the top or bottom of the report using the new Position on report option.
• The Total type option in the Field Properties dialog for the Chart Wizard has a new Count Distinct setting.
• The Summary report option in Step 4 of the Chart Wizard has been removed. Instead, set Total type in the Field Properties dialog to None, meaning you want individual values plotted rather than summarized values.
• The Multi-line option in the Labels page of Step 4 in the Chart Wizard is no longer available for Windows XP since that option is only supported in Windows Vista and later versions.
• You now receive a warning if you create a quick or cross-tab report, add a chart to it, then remove one or more of the fields used in the chart.
• Outputting a chart report to an image (such as GIF) now outputs just the chart, not an entire page with the template.

Cross-Tab Reports

• You can now specify how a date field used in the row of a cross-tab report is formatted: as a date, hour, week, month, quarter, or year.
• You can now specify the width and what to do if the width is too narrow in row and data fields in the Cross Tab Wizard.
• The Field Properties dialog now has a Suppress repeating values option for row fields. If this option is turned on (which it is by default) and several records in a row have the same value in this field, only the first occurrence is printed; the field in subsequent records appears blank.
• A new _TOTAL value is True only for the grand totals line in a cross-tab report, so you can use that in, for example, the Print When expression of an object in a template to output that object only for the totals line. Similarly, _SUBTOTAL can be used to output an object only in a subtotals line.

Preview and Output

• A new file output type is available: Microsoft Excel PivotTable. This type, which is only available for cross-tab reports, creates an Excel PivotTable formatted the same as the cross-tab report, but you can use Excel to change or pivot the report as necessary.
• You can now output cross-tabulation and advanced layout reports to more file types, including Microsoft Excel data-only and delimited.
• The Embed fonts in the file option on the Output page allows you to embed the fonts used in the report, such as a barcode font, in a PDF file.
• The Preview window now supports the same types of output types as the Output page of the Reports Explorer.
• When you run a report, the Reports Explorer and any open report wizard windows become dim and a progress window appears, showing you the progress of the report process and allowing you to cancel the process before it finishes. You can turn this off if you wish by turning off the Display progress during report runs setting in the Options dialog.
• You can now configure Stonefield Query to retrieve records from the database in batches (also known as asynchronously). This gives you the ability to stop a long-running report while records are retrieved rather than having to wait a long time for retrieval to be complete.

Advanced Report Designer

• Several of the object property dialogs in the Advanced Report Designer have some changes. The Rotation option no longer appears on the General page but on its own Rotation page because it now shows a sample of how the rotation will look. The properties dialogs for field, picture, rectangle, and line objects have a new Dynamics page that allows you to conditionally alter the appearance of the object. Because the conditions on the Dynamics page are more flexible than dynamic font and color settings, those two items have been removed from the Style page for field objects.
• You can now use gradient colors for rectangles in the Advanced Report Designer, both for reports and templates. A couple of new sample templates, Gradient Template and Valencia Orange, show how these can be used to produce nice, professional effects in your reports.
• If you use Windows Vista or later, you can now change the printer used for an advanced layout report in the Page Layout page of the Report Properties dialog (this option was previously and is still available for Windows XP and earlier).

Other Features

• The performance of some types of reports has been improved. This specifically applies to those where you're reporting on two or more tables, one of which has relatively few records and one that has a lot, and you're filtering on the few-records table but not the many-records one (e.g. customers and invoices with a filter on customers).
• If you know you won't need access to Accpac optional fields, you can make Stonefield Query start up faster by adding a line with "CustomMeta=N" to the [Meta Data] section of SFQuery.INI.
• The Item Pricing (ICPRIC) and Price List Codes (ICPCOD) tables now also appear in the UniSales module. Also, a relationship now exists between Added Item Pricing (ICPRIC) and Sales Analysis Details (UXDETAIL).
• The caption for OEORDD was changed to Sales Order Details, the caption for OEORDH was changed to Sales Orders (Part 1), and the caption for OECOINO was changed to Sales Order Comments/Instructions.
• The caption for ICITEM.FMTITEMNO was changed to Item Number (Formatted).
• You can now use fields from tables involved in the report for exclusion conditions. This means you can create a report that, for example, shows all customer who bought something last year (using a filter condition of sales date is between January 1 and December 31 of last year) but didn't this year (using an exclude condition of sales date is between January 1 and December 31 of this year).
• The performance of certain types of formulas was improved.
• The Formulas dialog now has a Copy function to make it easier to create a formula similar to an existing one.
• The Formula Editor now has a Data group option so you can select the data group the tables in the Tables list are from.
• The default name when you copy a report is now the existing report name with a "1" suffix; in previous versions, the default name was blank.
• The login dialog now has a link to the Setup dialog. This is useful if you need to change something about the Stonefield Query setup before logging in.
• You cannot schedule reports if you are running Stonefield Query on Windows Terminal Server or Citrix since those environments don't permit it.
• The version number now displays in the Reports Explorer title bar so you can quickly see which version you are running.
• You can now specify carbon copy (CC) addresses when sending emails.
• If you're using Microsoft Outlook or some other MAPI email program, you can now enter the name of a distribution list in the To, CC, and BCC options to send the email to a group of people.
• Importing several reports that use formulas at one time is now significantly faster than before.
• Specifying "ALLOWDIALOGS" when running Stonefield Query from another application or the command line now causes a progress dialog to display as the report runs.
• Diagnostic logging is now always performed. A file called Diagnostic.TXT is created in the Data subdirectory of the program folder and is overwritten every time you start Stonefield Query. This file is very useful for tracking down errors or performance issues, and previously was only created if a file named Log.TXT existed.
• Stonefield Query now displays a message that it's retrieving news items while it does that task so you can see what's going on.

Stonefield Query for Sage Accpac ERP Version 3.3 Build 3756

We released an update to Stonefield Query for Sage Accpac ERP version 3.3 today. It has the following new features:

• The performance of optional fields containing character values was dramatically improved. For example, a report that used optional fields extensively used to take 2.5 hours to run but now takes 2.5 seconds!
• You can now report on the Inventory Control Units of Measure table.
• The relationships for the Order Entry Comments/Instructions tables (for example, Invoice Comments/Instructions) were changed so records from the other table appear regardless of whether there are Comments/Instructions records or not (in technical terms, they now use an outer join). Also, there's now a relationship between Cred. Note Comments/Instructions and Combined Invoice & Credit Note Details.
• A relationship was added between Order Entry Posting Journals - Details and Sales History Details.
• UniSales budget fields are handled better.
• The Field Properties dialog now has a Suppress repeating values option for row fields. If this option is turned on (which it is by default) and several records in a row have the same value in this field, only the first occurrence is printed; the field in subsequent records appears blank.
• Fields with drop-down lists in the Filter Condition dialog now have a blank value as an option. This allows you to blank previously entered values.

Stonefield Query for Sage Accpac ERP Version 3.3 Build 3713

We released an update to Stonefield Query for Sage Accpac ERP version 3.3 today. It has the following new features:

• There are now relationships between the Order Entry Sales History Details table and the Credit/Debit Notes, Invoices, and Combined Invoices & Credit Notes tables, and the Accounts Receivable Document Details table and Item Pricing table.
• There are a couple of new fields available in the Accounts Receivable Documents table: Document Amt (Functional) and Document Amt (Customer). These fields contains the document amount with taxes removed and retainage (if any) included.
• There are three new fields available in the Accounts Receivable Document Details table: Extended Amt (Customer), Amt / Tax Base (Customer), and Amt / Tax Base (Functional). The latter two contain the detail line amount not including any taxes, and is also the amount the tax amounts are based on.
• There are a couple of new fields available in the Accounts Payable Documents table: Document Amt (Functional) and Document Amt (Vendor). These fields contains the document amount with taxes removed and retainage (if any) included.
• There are a couple of new fields available in the Accounts Payable Document Details table: Amt / Tax Base (Vendor), and Amt / Tax Base (Functional). These fields contain the detail line amount not including any taxes, and is also the amount the tax amounts are based on.

Stonefield Query for Sage Accpac ERP Version 3.3 Build 3667

We released an update to Stonefield Query for Sage Accpac ERP version 3.3 today. It has the following new features:

• Stonefield Query now supports optional fields in the Return Material Authorizations and Return Material Authorization Details tables.
• There is now a relationship between the AR Ship-To Locations and OE Invoices, Credit/Debit Notes, and Combined Invoices & Credit Notes tables.
• Each Uni Sales Analysis budget now appears as its own table named "Budget" followed by the budget name, and these tables are linked to the Sales Analysis Details table so you can include both budget and actual amounts on the same report.
• In the Field Properties dialog, the Summary option has a new choice: Count Distinct. The difference between Count and Count Distinct is that Count counts the number of records while Count Distinct counts the number of records having unique values on this field. For example, if there are 100 order records but all orders were placed this week, using Count on the Order Date field would display 100 but Count Distinct would display 7 (assuming at least one order was placed every day this week). Also, the Summary option is now available for all field types, not just numeric fields.
• The Display date as option in the Field Properties dialog has a new choice: Hour, which displays the hour portion of the datetime value.
• If you use a date or datetime field in the X-axis, missing values are automatically added to the chart, and the range of those values respects any date filter you use. For example, in the following chart, the only dates that had orders were 07/04, 07/05, and 07/08, but the filter condition was Order Date is between 07/01/1996 and 07/08/1996, so all days between those dates appear whether they had any orders or not:
• The Chart Type page in Step 4 of the Chart Wizard has a new Summary report option, which is turned on by default. Turn this option off if, instead of displaying summarized values, you want individual values charted.
• The Formula Editor has a new Send formula to database engine option that allows you to specify whether the database engine evaluates the formula or whether Stonefield Query requests any fields used in the formula from the database engine and then evaluates the formula itself.
• If you use an existing formula in a new formula, the existing formula is no longer expanded to show its definition in the new formula; instead, just its name appears, as it did when you created the new formula. For example, suppose a formula called Total Price is Unit Price * Quantity. If you create another formula called Commission, which is Total Price * 0.05, the formula displays as Total Price * 0.05 rather than Unit Price * Quantity * 0.05 as it used to in previous versions.
• Cross-tab reports have a new "Show percentage of total" option.
• Blank column headings now appear as "(None)" in cross-tab reports.
• In a multiple database report, if the query for one database fails for a reason other than a connection problem, the report continues to run if the Continue multi-database report if cannot connect to one database setting in the Options dialog is turned on.

Stonefield Query for Sage Accpac ERP Version 3.3 Build 3587

We released an update to Stonefield Query for Sage Accpac ERP version 3.3 today. In addition to the general changes made to Stonefield Query, it has the following new features:

• Stonefield Query now supports Sage Accpac ERP version 5.6.
• The Formula Editor now works with Accpac optional fields.
• Stonefield Query now has better support for the Pervasive Client ODBC driver.
  

Stonefield Query for Sage Accpac ERP 3.3 Build 3497

We released an update to Stonefield Query for Sage Accpac ERP version 3.3 today. In addition to the general changes made to Stonefield Query, it has the following new features and bug fixes.

• Stonefield Query now supports the Return Material Authorization module and Norming Fixed Assets.
• New fields in Account Fiscal Sets (General Ledger) allow you to report on the balance for each period.
• You can now report on the Fiscal Calendars table (General Ledger).
• The new built-in AccpacDateToRealDate function converts Accpac-formatted dates (which are stored as numeric values, such as 20090625 for 06/25/2009) into real date values. This function is useful when you create your own formulas that perform date math.
• Relationships between for Location Item Details (Inventory Control) and Purchase History Detail (Purchase Orders), Contract Resource (Project and Job Costing), and Contract Resource Category (Project and Job Costing) were improved.
• The relationship between Posting Journal Details and Posting Journal Entries (Accounts Payable) was corrected.
• Optional fields now work for tables in the Project and Job Costing Module.
• Formulas using fields from the Order Entry Combined Invoice & Credit Note Details table now work correctly, as do formulas using fields with the name "DESC."
• A problem with the Extended Amount Less Discount field in the Order Entry Combined Invoice & Credit Note Details table was fixed.
• The descriptions for the APOBLJ and AROBLJ tables were changed to "Document Details" to be more consistent with other table descriptions.

Stonefield Query for Sage Accpac ERP Version 3.3

We released version 3.3 of Stonefield Query for Sage Accpac ERP today. The list of new and enhanced features in version 3.3 is extensive; see the online help for complete details. Here are the major new features.

Report Designer

• The new Formula Editor allows you to create your own formulas without using the Advanced Report Designer or customizing the data dictionary.
• The new Show percentage of total option in the Format page of the Field Properties dialog automatically adds a new column to the report showing the percentage that the field is of the subtotal in detail lines, of the next level of grouping in subgroup footers, and of the grand total for group footers.
• Turn on the new Show totals in group headers option in Step 5 of the Quick Report Wizard and the Options page of the Reports Explorer to display group totals in the group header rather than the group footer.
• Stonefield Query now supports Crystal Reports reports. You can add them to Stonefield Query using the External Report Wizard.
• The Preview window is no longer modal, meaning you can open multiple instances. Also, the new Preview reports in tabbed pages setting in the Options dialog allows you to determine whether different preview windows are used when you run more than one report or whether the reports appear as different tabs in the same preview window.
• There are several new output options available. You can specify that a timestamp is added to the file created to prevent overwriting existing files, that the file emailed to someone shouldn't be deleted after the email is sent, and what filename to use for the emailed file.
• Previous versions of Stonefield Query create a report as a PDF file when emailing the report to someone. You can now specify the type of file used as an attachment.
• You can now specify that each group in a report should be output to a separate file or email and that the email address should come from a field in the report itself. This can be used, for example, to email invoices to customers; the report is grouped by customer name and includes the customer's email address, so each customer's records are emailed to them.
• You can now sort fields in database order or alphabetical order in Step 2 of the report wizards.
• You can now tell Stonefield Query to display in the Tables drop-down list only those tables related to tables already appearing in the selected list.
• Reports are now stored as export files with an SFX extension in a folder structure that exactly matches the folders in the Reports Explorer.
• The new Send summary queries to database engine setting in the Options dialog allows you to specify whether Stonefield Query or the database engine does data summarization. This may significantly improve the performance of certain types of reports.
• The new Run report with no records option in Step 5 of the report wizards and the Options page in the Reports Explorer allows you to run a report when there are no records that match your conditions.

Sage Accpac Specific

• New fields in Account Fiscal Sets (General Ledger) allow you to report on the balance for each period.
• You can now report on the Fiscal Calendars table (General Ledger).

Stonefield Query for Sage Accpac ERP Version 3.2 Build 3362

We posted an updated version of Stonefield Query for Sage Accpac ERP today with the following changes.

• A new Extended Amount Less Discount field in Order Entry Invoice Details, Credit Note Details, Combined Invoice & Credit Note Details, and Order Details shows the net amount of the transaction.
• There are now direct relationships between Accounts Receivable Documents and Order Entry Invoices, Credit Notes, and Combined Invoices & Credit Notes, so reporting on these tables works better.
• A bug that caused an error when reporting on both Invoices and Invoice Details in Order Entry was fixed.
• A bug that caused an error when reporting on Combined Invoices & Credit Notes and Combined Invoice & Credit Note Details tables in Order Entry was fixed.
• A bug that prevented exclusion filters from working under some unique circumstances was fixed.
• A bug that prevented GROUP BY and ORDER BY clauses from being handled properly in custom SQL statements was fixed.
• A bug that caused numeric values to appear without any decimals when the first value in the result set is null was fixed.
• A bug that caused an error when opening or closing the preview window under rare circumstances was fixed.

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 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.