Monday, August 15, 2016

Stonefield Query Available for Sage 300 2017

We released a patch for Stonefield Query for Sage 300 that works with the newly released Sage 300 2017. Download http://www.stonefieldquery.com/Downloads/SageAccpacERP/Interim/Sage2017Patch.zip and extract the files into the Stonefield Query program folder to overwrite Settings.exe in the program folder and RepMeta.* in the Data subdirectory,

Wednesday, April 13, 2016

Customer Portal Now Has Download Links

Last year, we implemented a customer portal that allows you to manage your Stonefield Query licenses. We’ve updated the portal to provide a download link for the version of Stonefield Query you’re using. It currently lists the latest version of Stonefield Query but will later list earlier versions as well in case you need to install an older version for some reason.

So, anytime you need to download the latest version of Stonefield Query, simply log in to the portal and click the link.

Wednesday, March 30, 2016

Stonefield Query Enterprise Web Version 3.0 Released

Stonefield Software is pleased to announce the release of version 3.0 of Stonefield Query Enterprise Web. There are dozens of new features in this release, the major ones being listed below. See the online help for both Stonefield Query and Stonefield Query Studio for a complete list of new features.

For developers, note that version 3.0 has a breaking change if you’ve created your own value converters, as the interface for them has changed. You’ll need to update and recompile your plugin code to work with version 3.0.

Stonefield Query
  • The Reports Explorer now has a toolbar at the top with buttons for the selected report rather than sets of buttons for each report. This makes for a more attractive display and dramatically improves performance in Internet Explorer.

  • Cross-tab reports and charts now support subreports.

  • Outputting a cross-tab report to XLSX Full Format now outputs to a Microsoft Excel PivotTable.

  • The Advanced Report Designer is now available within the browser.

  • Export options for a report now appear in a separate dialog and are also available from the Preview window. Also, a new output destination is available: FTP.

Stonefield Query Studio
  • Fields have a couple of new properties: Appears in and Output type.
  • New value converters are available: LookupFieldConverter, StringToDateTimeValueConverter, ExcelDateToDateTimeValueConverter, and NumericToBoolValueConverter.
  • The new User can manage connection string value for a database's Connection type property allows you to define the connection string for the database at runtime (in the Setup Wizard) without having to create a DataEngine plugin to set the connection string.
  • Some value converters and virtual table plugins now have builders to assist with editing the data for the plugin.

  • Two new types of virtual tables are available. The SQLStatementPlugin virtual table plugin uses the SQL statement and parameters specified in the plugin data to generate the virtual table; this is handy if you don't want to or can't create a view in the database. The ExcelToTablePlugin virtual table plugin reads from a Microsoft Excel document so you can query on Excel documents as easily as a database.

  • Studio has a new Export to Microsoft Excel function.

  • The Publish process now shuts down the application pool on the web server before copying files so you don't have to do it manually.

Monday, February 8, 2016

Stonefield Query for Act! Version 5.2.5868

We are pleased to announce the release of version 5.2.5868 of Stonefield Query for Act!. There are several new features in this release.
  • A new type of output is available: Microsoft Word Mail Merge. This allows you to perform a mail merge from a report to create form letters.
  • A formula can now use an expression that doesn't retrieve data from the database, such as " ", which allows you to add a blank column to a report.
  • The SQLookup function can now return an expression instead of just a field. The expression must be one the database engine can understand rather than a Stonefield Query expression.
  • The GetConditionValue function now accepts a parameter indicating which value for "is between" and "is one of" conditions is returned.
  • You can now turn off usage reporting by adding Usage=N to the [Options] section of Data.ini.
  • If none of your data fields contain null values, you can make processing slightly faster by adding HandleNull=N to the [Options] section of Data.INI.
  • Grouping formulas can now be used as data fields in a cross-tab report.
  • Cross-tab reports now use the text for the Summary Total Template field in the template if it exists; this allows you to customize the text displayed for the totals line by changing the text in the template used by the report.
  • Help now displays correctly even when running from a server without having to install the help file on the workstation.
  • The Calculate Value at Group Level setting in the Formula Editor is now disabled if the field is a grouping formula since a formula can't be both.
  • The GetConditionValue, GetValueForField, GetValueForParameter, GoMonthDay, SQLookup, STOD, and RunSQL functions now appear in the Built-in Functions list in the Expression Builder.
  • The Print button in the Preview window is now disabled for a label report using a Dymo label size since it works better to print those labels directly rather than previewing first.
  • You can now specify "login" on the command line to prompt the user to log in when running a dashboard from a shortcut. Also, theDashboard Wizard now has a "Require login" setting in step 2.

Wednesday, January 27, 2016

Stonefield Query for Sage 300 Version 5.2.5868

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

  • Stonefield Query now supports Sage 300 2016 (version 6.3).

  • A new type of output is available: Microsoft Word Mail Merge. This allows you to perform a mail merge from a report to create form letters.

  • The Maintain Databases dialog now shows a checkmark in front of data sources used with Stonefield Query in the data source list so you can tell at a glance which ones are used.

  • A formula can now use an expression that doesn't retrieve data from the database, such as " ", which allows you to add a blank column to a report.

  • The SQLookup function can now return an expression instead of just a field. The expression must be one the database engine can understand rather than a Stonefield Query expression.

  • The GetConditionValue function now accepts a parameter indicating which value for "is between" and "is one of" conditions is returned.

  • You can now turn off usage reporting by adding Usage=N to the [Options] section of Data.ini.

  • If none of your data fields contain null values, you can make processing slightly faster by adding HandleNull=N to the [Options] section of Data.INI.

  • Grouping formulas can now be used as data fields in a cross-tab report.

  • Cross-tab reports now use the text for the Summary Total Template field in the template if it exists; this allows you to customize the text displayed for the totals line by changing the text in the template used by the report.

  • Help now displays correctly even when running from a server without having to install the help file on the workstation.

  • The Calculate Value at Group Level setting in the Formula Editor is now disabled if the field is a grouping formula since a formula can't be both.

  • The GetConditionValue, GetValueForField, GetValueForParameter, GoMonthDay, SQLookup, STOD, and RunSQL functions now appear in the Built-in Functions list in the Expression Builder.

  • The Print button in the Preview window is now disabled for a label report using a Dymo label size since it works better to print those labels directly rather than previewing first.

  • You can now specify "login" on the command line to prompt the user to log in when running a dashboard from a shortcut. Also, theDashboard Wizard now has a "Require login" setting in step 2.

Stonefield Query SDK Version 5.2.5868

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

Report Designer

  • A new type of output is available: Microsoft Word Mail Merge. This allows you to perform a mail merge from a report to create form letters.

  • A formula can now use an expression that doesn't retrieve data from the database, such as " ", which allows you to add a blank column to a report.

  • The SQLookup function can now return an expression instead of just a field. The expression must be one the database engine can understand rather than a Stonefield Query expression.

  • The GetConditionValue function now accepts a parameter indicating which value for "is between" and "is one of" conditions is returned.

  • You can now turn off usage reporting by adding Usage=N to the [Options] section of Data.ini.

  • If none of your data fields contain null values, you can make processing slightly faster by adding HandleNull=N to the [Options] section of Data.INI.

  • Grouping formulas can now be used as data fields in a cross-tab report.

  • Cross-tab reports now use the text for the Summary Total Template field in the template if it exists; this allows you to customize the text displayed for the totals line by changing the text in the template used by the report.

  • Help now displays correctly even when running from a server without having to install the help file on the workstation.

  • The Calculate Value at Group Level setting in the Formula Editor is now disabled if the field is a grouping formula since a formula can't be both.

  • The GetConditionValue, GetValueForField, GetValueForParameter, GoMonthDay, SQLookup, STOD, and RunSQL functions now appear in the Built-in Functions list in the Expression Builder.

  • The Print button in the Preview window is now disabled for a label report using a Dymo label size since it works better to print those labels directly rather than previewing first.

  • You can now specify "login" on the command line to prompt the user to log in when running a dashboard from a shortcut. Also, theDashboard Wizard now has a "Require login" setting in step 2.

Stonefield Query Studio

  • The new ReportEngine.BeforeSendEmail script allows you to change the settings of a report before it's emailed to someone.

  • You can now customize what happens when refreshing the data dictionary for a database that has tables or fields removed. For example, you can use this to not remove tables or fields from modules that may not be in the database being refreshed from.

  • You are now prompted before Stonefield Query Studio removes tables from the data dictionary after refreshing from a database that has tables or fields removed.

  • Stonefield Query Studio now checks that you've selected the correct name delimiters (the reverse apostrophe: `) when adding a MySQL database to the data dictionary.

  • Stonefield Query now handles two tables with the same name but different schemas.

  • The default for the Support Online Updates and Support Maintenance Reminder configuration settings for new projects is now False.

Wednesday, November 4, 2015

Calculating the Number of Days Between Dates

Suppose you want to know how long it took to ship an item. That is, you need to calculate the number of days between the date an item was ordered and when it was shipped. Calculating the number of days between dates is easy in Stonefield Query: just create a formula that subtracts the two date fields, such as:

ShippedDate – OrderDate

However, there may be a few complications with this.

Handling null dates

What should the number of days be if the item hasn’t shipped yet? If ShippedDate is null (that is, an unknown value), ShippedDate – OrderDate is also null, which displays as blank in Stonefield Query. If that’s what you want, great. If not, adjust the formula to display a special value in that case, such as:

IIF(ISNULL(ShippedDate), DATE(), ShippedDate) – OrderDate

which displays the number of days between the order date and today if the item hasn’t shipped, or:

IIF(ISNULL(ShippedDate), -1000, ShippedDate – OrderDate)

which displays –1000.

Note that you can’t use something like:

IIF(ISNULL(ShippedDate), "Not shipped", ShippedDate – OrderDate)

because the formula is supposed to be numeric but “Not shipped” isn’t numeric.

Handling weekends

Do you ship on weekends? If not, the number of days it took to ship an item may be overstated because the formula counts days even when you’re not open. For example, if something was ordered on Friday and shipped on Monday, the difference is three days when it should really be one.

In that case, you need a more complicated formula, one that subtracts weekend days. To do that, use an expression of:

WeekendSpan(OrderDate, ShippedDate)

After you tab out of the Expression textbox in the Formula Editor, Stonefield Query tells you that a function named WeekendSpan can’t be found and asks if you’d like to create it. Choose Yes and paste the following code into the code editor window that appears:

function WeekendSpan(tdStart, tdEnd)
local lnWeekendDays, lnI, ldDate, lnDay, lnSpan
lnWeekendDays = 0
for lnI = 1 to tdEnd - tdStart
      ldDate = tdStart + lnI
      lnDay  = dow(ldDate)
      if lnDay = 1 or lnDay = 7
            lnWeekendDays = lnWeekendDays + 1
      endif
next
lnSpan = tdEnd - tdStart - lnWeekendDays
return lnSpan

This code assumes you’re closed on Saturday and Sunday; the DOW function gives the day of the week for the specified date, which is 7 for Saturday and 1 for Sunday. Adjust the code as necessary if, say, you’re closed on Friday and Saturday and open on Sunday.

Handling holidays

This code doesn’t account for holidays, such as December 25 or January 1. To check for dates like that, change the code to:

function WeekendSpan(tdStart, tdEnd)
local lnWeekendDays, lnI, ldDate, lnDay, lnSpan
lnWeekendDays = 0
for lnI = 1 to tdEnd - tdStart
    ldDate = tdStart + lnI
    lnDay  = dow(ldDate)
    if lnDay = 1 or lnDay = 7 or IsHoliday(ldDate)
        lnWeekendDays = lnWeekendDays + 1
    endif
next
lnSpan = tdEnd - tdStart - lnWeekendDays
return lnSpan

function IsHoliday(tdDate)
local laHolidays[2], llHoliday, lnI
laHolidays[1] = date(2015, 12, 25)
laHolidays[2] = date(2016, 1, 1)
llHoliday     = .F.
for lnI = 1 to alen(laHolidays)
    if month(laHolidays[lnI]) = month(tdDate) and ;
        day(laHolidays[lnI]) = day(tdDate)
        llHoliday = .T.
        exit
    endif
next
return llHoliday

(New code is shown in bold.)

Note that you have to dimension the laHolidays array to the number of holidays and set each element in the array to the appropriate date (don’t worry about the year part of the date; the code only checks month and day) as this example code does.

If you need to handle holidays that aren’t on fixed days, such as Thanksgiving in the U.S., you need to code for that specifically, such as:

if lnDay = 1 or lnDay = 7 or IsHoliday(ldDate) or ;
    ldDate = DATE(2015, 11, 26)

Conclusion

As you can see, date math can be very simple or it can be more complicated, depending on your needs. However, it’s good to know that Stonefield Query can handle even the most complex date calculations you need.

Thursday, September 24, 2015

Stonefield Query for GoldMine Version 5.2

We are pleased to announce the release of version 5.2 of Stonefield Query for GoldMine. There are lots of new features in this release.

Report Designer
  • Stonefield Query is compatible with GoldMine 2015.
  • 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.
  • Four new sample reports have been added: Chart Sorted Descending by Category, Dashboard Including Quick Report, Field Background Color, and Most Recent Three Opportunities by Customer.
  • You can now select more than one grouping field for a grouping formula. This is necessary for a formula that, for example, shows 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.

Wednesday, September 23, 2015

Stonefield Query for Act! Version 5.2

We are pleased to announce the release of version 5.2 of Stonefield Query for Act!. 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.
  • Four new Act! 2010+ sample reports have been added: Chart Sorted Descending by Category, Dashboard Including Quick Report, Field Background Color, and Most Recent Three Opportunities by Customer.
  • 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.

Thursday, September 17, 2015

Customer Portal is Now Live

We have a new customer portal! This portal allows you to manage your Stonefield Query licenses and will later provide other features including links for downloading current and older versions and managing your software maintenance.

To access the portal, go to https://sqportal.stonefieldquery.com. You can also choose “Customer Portal” from the Support menu of StonefieldSoftware.com or StonefieldQuery.com. The login page appears:

portal1

The first time you access the portal, you’ll need to register so click the Register link.

portal2

In the Create a New Account page, enter your email address, a password (entered twice to confirm), and one of your Stonefield Query serial numbers. To get your serial number, run Stonefield Query, choose About from the help menu, and click the blue “Serial Number” text to copy your serial number to the clipboard; you can then paste it into the Serial # box in the Create a New Account page.

portal3

Whether you’ve just registered or not, you’ll see the Customer Portal next. It shows a list of your Stonefield Query licenses, including serial number, number of seats, product name, type of license, whether it’s activated or not, when the software maintenance expires, and when it was last activated. The Filter box allows you to enter a serial number or any other text to search on. You can sort on any column in the list by clicking the column header. Click it again to reverse the sort order.

portal4

There are a couple of blue buttons in the list. The button beside the serial number copies that serial number to the clipboard. The button beside Last Activated On displays the Manage Serial Number dialog.

portal5

This dialog allows you to activate or deactivate a license. Use the blue “lock” button to activate an unactivated license (it’s more convenient to do this using the License Manager in Stonefield Query but it’s here in case you need it). Use the “unlock” button to deactivate a license, such as if you want to move it to a different computer. The list below the buttons shows previous activations of the license, which may be helpful if you’re trying to track down a license problem due to the same serial number being used on more than one system.

We’re sure you’ll like the new portal. Please send any feedback to websupport@stonefieldquery.com.