Stonefield Query for Sage Pro ERP Version 5.1

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

• The performance of Stonefield Query has been improved, in some cases quite dramatically.
• Several changes were made to gauges. First, the gauge has a new, more attractive appearance, including an option to show the current value using "digital" digits like a speedometer. Second, the performance of gauges on some systems was dramatically improved. Third, there are several new settings: you can specify that the goal comes from a specific value rather than a field, saving you having to create a formula for this purpose; you can specify the font and color of the gauge labels; you can specify band end values as amounts instead of percentages; and you can specify at what percentage the goal value appears. Also, the "distinct" setting in the Customize Wizard is now available.
• You can now edit labels using the Advanced Report Designer, allowing you to move fields around or tweak the layout.
• The new Grid Bars option for charts draws alternating horizontal bars in a chart, making a more attractive chart.
• The new Scheduled Tasks function in the Tools menu allows you to display the status of your scheduled reports, including last run date and result, and edit or delete schedules.
• The new Find Reports Using a Field and Find Reports by Name functions allow you to see which reports include a particular field, either in the report itself or in the filter conditions for the report, or to find reports containing text you specify in the name.
• A new Summary option for grouping formulas, Combine, allows you to combine the values of a field from several records. This is useful, for example, to create a formula that lists all of the products purchased by a customers in a single record rather than one record per purchase.
• The Formula Editor has a new code editor button. This button allows you to create and edit custom functions without having to put code in RepProcs.prg and without having to exit Stonefield Query every time you make a change to the code.
• The Formula Editor also has a new security button. This button allows you to indicate which user groups can see the formula and which can modify or delete the formula. The Formulas dialog now only displays formulas the user has access to and only enabled the Edit and Remove buttons if the user is in a group that has those permissions.
• The Formula Editor also has a Default Summary setting. This allows you to determine if numeric fields are automatically summed, averaged, or summarized in some other way by default.
• The Formulas dialog can now filter the list of formulas to only those in a certain table or Module. It also has a new Reports button showing you which reports use the selected formula.
• The report wizards have a new Save As button that allows you to save the report as a new report rather than having to first choose Copy and then edit the copy.
• You can now turn on the Ask at runtime setting for a filter condition using an expression as the value to compare to. In that case, the evaluated value of the expression is displayed as the default value for the condition in the ask-at-runtime dialog.
• The Filter Condition dialog no longer has a More button; the dialog is always expanded so the options in it are more discoverable. Also, there are new Ends With and Does Not End With operators and a new Custom Description setting.
• You can now select the direction (ascending or descending) of the sort of a group in the Advanced Report Designer.
• You can now indicate whether a header row of field names is included when outputting a report to Microsoft Excel data-only, comma-delimited, or text files.
• You can now specify the starting month of a fiscal year in the Options dialog so dates displayed as quarter use the correct quarter number for your fiscal year.
• The Maintain Users and Groups dialog now has a spot for the email address for each user. This address is used to send emails to technical support if the user hasn't filled in the settings in the Email tab of the Options dialog.
• You now get a warning when running a dashboard if any of the reports are missing.
• Temporary files more than a week old are now deleted when you close Stonefield Query to keep your temporary files folder from getting too full.
• You can no longer enter an email address formatted as "Some Name <somename@somecompany.com>" because that causes a scheduled report to fail.
• The Automatically sum numeric fields setting in the Options dialog was removed, as it's now controlled on a field-by-field basis.
• You now get a warning if you schedule a report to output to or email a file on a mapped drive, since drive mappings don't work well in scheduled tasks.
• Step 2 of the report wizards no longer has Add All and Remove All buttons, since those were were rarely used and accidentally using them causes changes you likely didn't expect.
• Turn on the new No message if no records setting for dashboards to not display a warning if there are no records for a particular chart in the dashboard.
• Importing a report containing an updated formula is now easier. In previous versions, if a formula was edited (for example, the expression was changed) at another site and a report containing that formula was exported and sent to you, importing the report didn't update the formula at your site. Instead, you had to delete the formula before importing the report. Now, when you import a report containing an existing formula that's different than your copy of the formula, you're prompted if you want to replace your formula with the one in the report.
• Importing an update to an existing report is also now easier: you are prompted if you want to replace the existing report with the one being imported. If you choose No, the imported report is given a new name (the same behavior as in previous versions).
• You now get a printer dialog when clicking the Print dialog in the Preview window for a chart.
• The dialog displayed when you click the Email button in the Preview window now has the same options as the Output page of the Reports Explorer.
• If another user is logged in with the same name, the application doesn't terminate but instead redisplays the Login dialog.
• You can now specify a width greater than 1000 for a chart, which is helpful if it's output in landscape.
• The Schedule Wizard now remembers the user name and password in the last step so you don't have to enter them every time you create a schedule.
• The size and position of the Select Email Addresses dialog is now remembered.
• If you use an ask-at-runtime filter condition for a grouping formula, the ask-at-runtime dialog now displays the name of the grouping formula so you can tell which formula the dialog is asking for values.
• You can now choose "Expression" for the Compare to setting for a filter condition using a formula.

Stonefield Query for Sage 300 ERP Version 5.1

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

• Stonefield Query can now handle optional fields when used in a multi-database report.
• There's a new Item Description field in the Inventory Control Assemblies table that gives the description of the item.
• Two new tables are available in Accounts Payable: Adjustments and Payments. They're really just specific subsets of the Payment and Adjustment Batches table.
• Several changes were made to gauges. First, the gauge has a new, more attractive appearance, including an option to show the current value using "digital" digits like a speedometer. Second, the performance of gauges on some systems was dramatically improved. Third, there are several new settings: you can specify that the goal comes from a specific value rather than a field, saving you having to create a formula for this purpose; you can specify the font and color of the gauge labels; you can specify band end values as amounts instead of percentages; and you can specify at what percentage the goal value appears. Also, the "distinct" setting in the Customize Wizard is now available.
• You can now edit labels using the Advanced Report Designer, allowing you to move fields around or tweak the layout.
• The new Grid Bars option for charts draws alternating horizontal bars in a chart, making a more attractive chart.
• The new Scheduled Tasks function in the Tools menu allows you to display the status of your scheduled reports, including last run date and result, and edit or delete schedules.
• The new Find Reports Using a Field and Find Reports by Name functions allow you to see which reports include a particular field, either in the report itself or in the filter conditions for the report, or to find reports containing text you specify in the name.
• A new Summary option for grouping formulas, Combine, allows you to combine the values of a field from several records. This is useful, for example, to create a formula that lists all of the products purchased by a customers in a single record rather than one record per purchase.
• The Formula Editor has a new code editor button. This button allows you to create and edit custom functions without having to put code in RepProcs.prg and without having to exit Stonefield Query every time you make a change to the code.
• The Formula Editor also has a new security button. This button allows you to indicate which user groups can see the formula and which can modify or delete the formula. The Formulas dialog now only displays formulas the user has access to and only enabled the Edit and Remove buttons if the user is in a group that has those permissions.
• The Formula Editor also has a Default Summary setting. This allows you to determine if numeric fields are automatically summed, averaged, or summarized in some other way by default.
• The Formulas dialog can now filter the list of formulas to only those in a certain table or Module. It also has a new Reports button showing you which reports use the selected formula.
• The report wizards have a new Save As button that allows you to save the report as a new report rather than having to first choose Copy and then edit the copy.
• You can now turn on the Ask at runtime setting for a filter condition using an expression as the value to compare to. In that case, the evaluated value of the expression is displayed as the default value for the condition in the ask-at-runtime dialog.
• The Filter Condition dialog no longer has a More button; the dialog is always expanded so the options in it are more discoverable. Also, there are new Ends With and Does Not End With operators and a new Custom Description setting.
• You can now select the direction (ascending or descending) of the sort of a group in the Advanced Report Designer.
• You can now indicate whether a header row of field names is included when outputting a report to Microsoft Excel data-only, comma-delimited, or text files.
• You can now specify the starting month of a fiscal year in the Options dialog so dates displayed as quarter use the correct quarter number for your fiscal year.
• The Maintain Users and Groups dialog now has a spot for the email address for each user. This address is used to send emails to technical support if the user hasn't filled in the settings in the Email tab of the Options dialog.
• You now get a warning when running a dashboard if any of the reports are missing.
• Temporary files more than a week old are now deleted when you close Stonefield Query to keep your temporary files folder from getting too full.
• You can no longer enter an email address formatted as "Some Name <somename@somecompany.com>" because that causes a scheduled report to fail.
• The Automatically sum numeric fields setting in the Options dialog was removed, as it's now controlled on a field-by-field basis.
• You now get a warning if you schedule a report to output to or email a file on a mapped drive, since drive mappings don't work well in scheduled tasks.
• Step 2 of the report wizards no longer has Add All and Remove All buttons, since those were were rarely used and accidentally using them causes changes you likely didn't expect.
• Turn on the new No message if no records setting for dashboards to not display a warning if there are no records for a particular chart in the dashboard.
• Importing a report containing an updated formula is now easier. In previous versions, if a formula was edited (for example, the expression was changed) at another site and a report containing that formula was exported and sent to you, importing the report didn't update the formula at your site. Instead, you had to delete the formula before importing the report. Now, when you import a report containing an existing formula that's different than your copy of the formula, you're prompted if you want to replace your formula with the one in the report.
• Importing an update to an existing report is also now easier: you are prompted if you want to replace the existing report with the one being imported. If you choose No, the imported report is given a new name (the same behavior as in previous versions).
• You now get a printer dialog when clicking the Print dialog in the Preview window for a chart.
• The dialog displayed when you click the Email button in the Preview window now has the same options as the Output page of the Reports Explorer.
• If another user is logged in with the same name, the application doesn't terminate but instead redisplays the Login dialog.
• You can now specify a width greater than 1000 for a chart, which is helpful if it's output in landscape.
• The Schedule Wizard now remembers the user name and password in the last step so you don't have to enter them every time you create a schedule.
• The size and position of the Select Email Addresses dialog is now remembered.
• If you use an ask-at-runtime filter condition for a grouping formula, the ask-at-runtime dialog now displays the name of the grouping formula so you can tell which formula the dialog is asking for values.
• You can now choose "Expression" for the Compare to setting for a filter condition using a formula.

Stonefield Query SDK Version 5.1

We are please to announce the release of version 5.1 of the Stonefield Query SDK. There are lots of new features in this release.

Report Designer

• Several changes were made to gauges. First, the gauge has a new, more attractive appearance, including an option to show the current value using "digital" digits like a speedometer. Second, the performance of gauges on some systems was dramatically improved. Third, there are several new settings: you can specify that the goal comes from a specific value rather than a field, saving you having to create a formula for this purpose; you can specify the font and color of the gauge labels; you can specify band end values as amounts instead of percentages; and you can specify at what percentage the goal value appears. Also, the "distinct" setting in the Customize Wizard is now available.
• You can now edit labels using the Advanced Report Designer, allowing you to move fields around or tweak the layout.
• The new Grid Bars option for charts draws alternating horizontal bars in a chart, making a more attractive chart.
• The new Scheduled Tasks function in the Tools menu allows you to display the status of your scheduled reports, including last run date and result, and edit or delete schedules.
• The new Find Reports Using a Field and Find Reports by Name functions allow you to see which reports include a particular field, either in the report itself or in the filter conditions for the report, or to find reports containing text you specify in the name.
• A new Summary option for grouping formulas, Combine, allows you to combine the values of a field from several records. This is useful, for example, to create a formula that lists all of the products purchased by a customers in a single record rather than one record per purchase.
• The Formula Editor has a new code editor button. This button allows you to create and edit custom functions without having to put code in RepProcs.prg and without having to exit Stonefield Query every time you make a change to the code.
• The Formula Editor also has a new security button. This button allows you to indicate which user groups can see the formula and which can modify or delete the formula. The Formulas dialog now only displays formulas the user has access to and only enabled the Edit and Remove buttons if the user is in a group that has those permissions.
• The Formula Editor also has a Default Summary setting. This allows you to determine if numeric fields are automatically summed, averaged, or summarized in some other way by default.
• The Formulas dialog can now filter the list of formulas to only those in a certain table or Data group. It also has a new Reports button showing you which reports use the selected formula.
• The report wizards have a new Save As button that allows you to save the report as a new report rather than having to first choose Copy and then edit the copy.
• You can now turn on the Ask at runtime setting for a filter condition using an expression as the value to compare to. In that case, the evaluated value of the expression is displayed as the default value for the condition in the ask-at-runtime dialog.
• The Filter Condition dialog no longer has a More button; the dialog is always expanded so the options in it are more discoverable. Also, there are new Ends With and Does Not End With operators and a new Custom Description setting.
• You can now select the direction (ascending or descending) of the sort of a group in the Advanced Report Designer.
• You can now indicate whether a header row of field names is included when outputting a report to Microsoft Excel data-only, comma-delimited, or text files.
• You can now specify the starting month of a fiscal year in the Options dialog so dates displayed as quarter use the correct quarter number for your fiscal year.
• The Maintain Users and Groups dialog now has a spot for the email address for each user. This address is used to send emails to technical support if the user hasn't filled in the settings in the Email tab of the Options dialog.
• You now get a warning when running a dashboard if any of the reports are missing.
• Temporary files more than a week old are now deleted when you close Stonefield Query to keep your temporary files folder from getting too full.
• You can no longer enter an email address formatted as "Some Name <somename@somecompany.com>" because that causes a scheduled report to fail.
• The Automatically sum numeric fields setting in the Options dialog was removed, as it's now controlled on a field-by-field basis.
• You now get a warning if you schedule a report to output to or email a file on a mapped drive, since drive mappings don't work well in scheduled tasks.
• Step 2 of the report wizards no longer has Add All and Remove All buttons, since those were were rarely used and accidentally using them causes changes you likely didn't expect.
• Turn on the new No message if no records setting for dashboards to not display a warning if there are no records for a particular chart in the dashboard.
• Importing a report containing an updated formula is now easier. In previous versions, if a formula was edited (for example, the expression was changed) at another site and a report containing that formula was exported and sent to you, importing the report didn't update the formula at your site. Instead, you had to delete the formula before importing the report. Now, when you import a report containing an existing formula that's different than your copy of the formula, you're prompted if you want to replace your formula with the one in the report.
• Importing an update to an existing report is also now easier: you are prompted if you want to replace the existing report with the one being imported. If you choose No, the imported report is given a new name (the same behavior as in previous versions).
• You now get a printer dialog when clicking the Print dialog in the Preview window for a chart.
• The dialog displayed when you click the Email button in the Preview window now has the same options as the Output page of the Reports Explorer.
• If another user is logged in with the same name, the application doesn't terminate but instead redisplays the Login dialog.
• You can now specify a width greater than 1000 for a chart, which is helpful if it's output in landscape.
• The Schedule Wizard now remembers the user name and password in the last step so you don't have to enter them every time you create a schedule.
• The size and position of the Select Email Addresses dialog is now remembered.
• If you use an ask-at-runtime filter condition for a grouping formula, the ask-at-runtime dialog now displays the name of the grouping formula so you can tell which formula the dialog is asking for values.
• You can now choose "Expression" for the Compare to setting for a filter condition using a formula.

Stonefield Query Studio

• The new Default summary setting controls how the Summary setting for field is automatically set when the field is added to a quick report.
• The new Allow Access to Reports When No Access to Fields configuration setting allows you to specify whether the user cannot see reports containing fields they don't have access to (the former behavior and the default behavior now) or they can see and run those reports but they act like the fields aren't in the report. The benefit of this is that if you have a report containing mostly regular fields but also one field only certain users can see, you don't have to create separate reports, one without the field for most users and one with the field for those certain users.
• The Find Field and Find Next Field functions in the Edit menu and the toolbar allow you to find a field having some text in the Name or Caption properties.
• The new Application.BeforeShutdown event allows you to determine whether the user can close Stonefield Query.
• The User object has a new Email property.
• You can no longer edit the Name property of a real field or table since the correct way to update a real table is using the Refresh function. We've had several cases where someone changed the name of a table or field when intending to change the caption, which resulted in lots of problems. However, if you really need to change the name and don't want to refresh, double-click the "Name" label to the left of the Name control to allow editing it. You also can't edit the Name property of a database.
• Studio now prompts you for a "previous version number" when it encounters fields in the data dictionary that no longer exist in the database when refreshing. This allows you to refresh against a new version of the database without removing fields from an older one that are supposed to stay in the data dictionary. It also does the same for tables that no longer exist.
• The Application object has a new RegisterCursorToCloseAfterRun method you can use to tell Stonefield Query to close a cached result set after a report run.
• The Report object has a new Linked property that's set to true if the report is being run as a linked report from another report.
• Three new DLLs have to be deployed with Stonefield Query: ClrHost.dll, Gauge.dll, and SMTPLibrary.dll.
• You can now specify a path for the files to download when updating the application.

Creating an Aging Report Using Stonefield Query

An aging report is one that shows totals broken down by age range. The most common example is an accounts receivable aging report, which shows how much each customer owes by period, typically within the past 30 days, 31 – 60 days, 61 – 90 days, and over 90 days. This type of report allows you to make business decisions, such as which customers to increase collection efforts with and which to cut off sales until they pay up (no point in continuing to sell products or do work for customers who won’t pay for them).

You’ve always been able to create this type of report in Stonefield Query, but until version 5.0, it was a bit of work. There were two ways to do it:

• Create a formula for each aging range that decides whether to include the amount or not using the IIF function that checks the date. For example, the expression IIF(BETWEEN(Invoices.DateDue, DATE(), DATE() – 30), Invoices.OutstandingAmount, 0) checks whether the due date for the invoice is between the current date and 30 days ago. If so, it takes the unpaid amount; if not, it takes 0. The report is grouped on customer and the formula fields are summed.
• Using the Advanced Report Designer, add fields to the report that do similar calculations to the formula approach.

A better way

A new feature added in version 5.0 called grouping formulas makes this a much simpler task. Here are the steps to creating an aging report using the sample data that comes with the Stonefield Query SDK; for other versions, such as Stonefield Query for Sage 300 ERP, the steps are very similar but you use the appropriate tables and fields instead of the ones mentioned here.

The first step is to create a quick report. Add the Customer Name field to the report, then click the New Formula button. Select the Customers table because that’s where we want the formula to go. Enter “0 – 30” for the Name and Heading since this first formula is for the first aging. Click the Expression Builder button (the one with the three dots) and select the Total Price field from the Order Details table because that’s the field we want to sum up.

Now here’s the key: we want this to be a grouping formula. A grouping formula is similar to a regular formula but it automatically summarizes the values, grouping on a certain field, and optionally for only a certain range of records. (Those of you who are technically oriented may guess that a grouping formula results in a SQL statement like SELECT SUM(SomeField) FROM SomeTable WHERE SomeConditions GROUP BY SomeGroupField.) In our case, we want to sum the total price, group the sums by customer, and only for records that are 30 or fewer days old. (Since the sample database doesn't have payments, we'll just assume that all orders are outstanding for demo purposes.)

To make this a grouping formula, click the Grouping button. Select Sum for Summary and Customer ID for Grouping Field. Click the Filter button, add a condition, select the Order Date field from the Orders table, and choose “is between” for the operator. The values to use are a little tricky: we don’t want to hard-code them to something like 05/01/2015 and 05/31/2015 because every time we run the report, we’d have to edit the formula and change the date range. We could turn on Ask at Runtime to prompt for the date range, but since we’ll ultimately have four formulas (one for each aging range), we don’t want to be asked for four different ranges of values; we really just want to put in a single value, which is the “as of” date for the aging calculations. To do that, click the More button, change Compare To to “Expression”, and enter the following expressions for the two values:

GetValueForParameter('As of date', 'D') – 30
(for the first value)

GetValueForParameter('As of date', 'D')
(for the second one)

GetValueForParameter is a built-in function that asks the user running the report for a value. The first parameter for the function is the text to display (“As of date” in this case) and the second is the data type for the value (“D” means we want a date; see the documentation for GetValueForParameter for what codes to use for different data types). The first expression tells Stonefield Query to ask the user for a date value and then subtract 30 days from that value. You may think from the second expression that Stonefield Query asks the user a second time, but GetValueForParameter has a nice feature: if the prompt is the same as a previous instance, the function doesn’t ask the user a second time but instead just returns the value they entered when they were asked. Since the two expressions both use “As of date” for the prompt, the user is asked only once. In fact, you’ll use a similar expression for the other formulas, such as 31 – 60, but subtract a different number of days, and since all of them use “As of date” for the prompt, the user is still only asked one time for the value that all expressions use.

Once you’ve saved the filter condition, the Grouping Formula Properties dialog should look like this:

Specify the formatting for the formula (I turned on Display $ and set Decimal Places to 2) and save the formula.

Finishing the report

Repeat these steps to create the 31 – 60, 61 – 90, and More Than 90 formulas. The only difference is the expressions used for the filter condition. For example:

GetValueForParameter('As of date', 'D') – 60
(first value for 31 – 60 formula)

GetValueForParameter('As of date', 'D') – 31
(second value for 31 – 60 formula)

(The expressions to use for the other two formulas are left as an exercise for the reader, but should be pretty obvious).

That’s it! Your report should now include Customer Name and the four formulas. No need to group the report on Customer Name or turn on Summary Report. When you run the report, you’re asked for the date to use for the aging calculations. The result should look like this:

Summary

Grouping formulas are a very powerful new feature added in version 5.0 of Stonefield Query. There are tons of uses for them, one of them making it very easy to create a simple aging report.

Finding the Fields You Need for a Report

If there are a lot of fields in your database, it can be challenging to find the one you're looking for. Here are some tips that may help:

• If you're not sure what a field contains, click the Values button in step 2 of the report wizards. The different values displayed in the dialog often give a clue as to what the field is used for.
• Fields are displayed by default in alphabetical order. This makes it easy to find a field in the list. However, sometimes it makes more sense to display fields in the order they appear in the table. Click the button to display fields in table order.
• A long list of fields can make it difficult to find the ones you're interested in. If you know part of the field caption you're looking for (such as a field with "tax" somewhere in the caption), click the button, enter the text, and click OK. If there are any fields that match, the first one is selected. Press F3 to find the next one containing the same text.
• A long list of tables can make it difficult to find the ones you're interested in. Once you've selected fields from one table, chances are you're next going to select fields from a directly-related table. For example, if you chose a field from Customers, you are more likely to choose a field from Orders than from Products. Click the button to reduce the list of tables to only those directly related to ones you've already selected fields from.
• If you're familiar with the name of your application's tables and fields, you might find it easier to locate the tables and fields you want by their real names. Turn on the Display real table and field names option in the Options dialog to display the real name followed by the descriptive name in parentheses.
• Sometimes the fields in an application are organized into pairs of "header-detail" tables; invoices and invoice lines is a classic example. These are called "header-detail" pairs because the information displayed in the header or top of an invoice goes in the invoices table and the line items go in the invoice lines table. When looking for a particular field, ask yourself if there's only one of these things in the invoice (invoice number, invoice date, customer number, and so on) or if there can be more than one (product description, quantity sold, etc.). If there's only one, you'll find the field in the header (in this case, invoice) table. If there's more than one, it'll be in the detail (invoice lines in this example) table.
  
• Application view shows fields the way your application does in its various dialogs. However, not all fields may appear when Application view is turned on in step 2. If you can't find a particular field, try turning on Database view. (Your version of Stonefield Query may not support application view.)
  
• A lot of fields may contain default or even no data; for example, the fields may be there to support certain features in the program, but you may not use those features. To hide those fields, choose Analyze Database from the Tools menu. This function goes through your database, looking at all fields in all tables for fields containing meaningful data (that is, more than a single unique value), and marking those as "favorite." After you've run this function, step 2 in the report wizards displays a button. If you turn this on, the table and field lists show only those tables and fields marked as "favorites." (Your version of Stonefield Query may not support favorites.)