Email Back Up

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

Stonefield Query Version 3.2 Build 3169

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

All Versions

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

SDK

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

Sage Accpac ERP

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

Sage Pro ERP

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

Web-Based Technical Support

Due to an excessive amount of spam (as much as 2500 per day), we're no longer using support@stonefieldquery.com for technical support.

Instead, we now offer Web-based technical support to all of our customers. This service is included with your Software Maintenance and will allow us to track and respond to your Stonefield Query issues in a timely and efficient manner.

You can submit a Technical Support Ticket at our Online Support Center (http://www.stonefieldquery.com/Support). Alternatively, you can post messages in our Support Forums(http://www.stonefieldquery.com/Support/SupportForums.aspx).

Please note that you must register on our site (registration is free) in order to submit or review your technical support issues. If you are already a member of our site, please sign-in to submit a support ticket.

Video on Creating a Customized Version of Stonefield Query

We created a short (6 minutes) video on how to create a customized version of Stonefield Query to work with any application's database. As this video shows, setting up the data dictionary for your database is mostly automated, with you doing additional customization work as necessary.

Stonefield Query for SalesLogix 3.2 Released

Stonefield Software Inc. is pleased to announce the release of version 3.2 of Stonefield Query for SalesLogix.

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

Some of the new features in this release are:

• If it is installed, Stonefield Query will use the SalesLogix OLE/DB provider for reporting. This means that Stonefield Query now adheres to SalesLogix record based security (you only see those records that your SalesLogix username is allowed to see) and all date/time fields will automatically be converted to your local time zone. If the OLE/DB provider is not available then Stonefield Query will connect via ODBC.
  
• When using the OLE/DB provider, Stonefield Query automatically retrieves a list of the SalesLogix datasources that have been defined on your computer by your SalesLogix administrator.
• Exclusion filters: this allows you to query on the absence of data, such as "give me a list of customers I didn't contact last year" or "give me a list of everyone who did not buy product X". Behind the scenes, Stonefield Query generates a NOT IN subquery, but from the end-user's perspective, it's just as easy as creating a normal filter.
• Modeless report wizards: the report wizards are no longer modal, so you can modify more than one report at a time.
  Single ask-at-runtime dialog: all ask-at-runtime filter conditions now appear in a single, wizard-like dialog rather than one dialog per condition.
• Workstation installer: Stonefield Query now has a separate WSSetup.EXE installer, installed by Setup.EXE, that does workstation installations with the minimum of user interaction.
• More grouping options: you can now specify that a non-grouped field should be included in the group header for another field, you can have all fields in the group header on a single line rather than stacked on separate lines, and all fields in the group header now appear in summary reports.
• Multi-line headings: many fields now have multi-line headings so they don't take up as much room in a report, and you can change field headings to use multiple lines.
• Customize Report Wizard for all report types: all report wizards now have an Advanced button, allowing access to TOP N, custom SQL statements, and other features.

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

Antivirus Software Causing Errors in Stonefield Query

We've received several error reports from customers over the past couple of days about errors when running reports in Stonefield Query. The most common error is "Cannot load XFRXLIB.FLL". It turns out that a recent change in the definition files for the Trend Micro anti-virus software was erroneously flagging one of our DLLs, HNDLIB.DLL, as a suspect file and quarantining it. Since XFRXLIB.FLL relies on that DLL, it fails to load so Stonefield Query can't run reports.

Apparently Trend Micro has fixed the problem, so updating to the latest definition files should resolve the problem, although you'll have to manually remove HNDLIB.DLL from quarantine.

Even if you're not using Trend Micro, it's likely a good idea to configure your anti-virus software to not scan the Stonefield Query program folder.

Update: see http://esupport.trendmicro.com/support/viewxml.do?ContentID=EN-1036613&id=EN-1036613 for instructions on configuring Trend Micro to allow or block specific programs.

Blog Moved

We've moved our blog from http://stonefieldquery.blogspot.com to http://blog.stonefieldquery.com. The old URL automatically redirects to the new one.

Calling Stonefield Query from .Net Applications

As described in our online documentation, Stonefield Query includes a lightweight COM object called SQProxy. This allows other applications to run reports, get the names of folders and the reports in them, and so on. Even though SQProxy is a COM object, it can be used in .Net applications such as ASP.Net.

You can easily add a reference to the SQProxy Type Library to a .Net project and use the interop class Visual Studio creates for you in your application. Simply right-click references in the Solution Explorer, choose Add Reference, select the COM page, and choose the SQProxy Type Library.

However, there's a complication: object members of SQProxy, such as SQApplication, aren't included in the type library so they don't have the proper type information that .Net needs. As a result, these members are cast as Object and you therefore cannot access their properties or call their methods.

To get around this, use reflection. This demo class has two helper methods, GetProperty and CallMethod, that get the value of a property or call the method of a Stonefield Query object. Thanks to C# MVP Rick Strahl for providing this technique and code.

using System;

using System.Collections.Generic;

using System.Text;

using sqproxy;

using System.Reflection;

namespace SQProxyTest

{

  class SQProxyTest

  {

    static void Main(string[] args)

    {

      SQProxyTest loTest = new SQProxyTest();

      loTest.TestSQProxy();

    }

    private void TestSQProxy()

    {

      //Create the SQProxy object.

      sqproxyClass SQP = 

        new sqproxy.sqproxyClass();

      //Load a project.

      SQP.LoadProject("C:\\Test", "admin",
       "admin", "Northwind");

      if (SQP.ProjectLoaded)

      {

        System.Console.WriteLine("Project Loaded");

        //Get references to some objects.

        object loApp = 

          this.GetProperty(SQP, "SQApplication");

        object loRE = 

          this.GetProperty(loApp, "ReportEngine");

        //Find out how many folders there are.

        object loFolders = 

          this.CallMethod(loRE, "GetFolders");

        int lnFolders = (int)
         this.GetProperty(loFolders, "Count");

        System.Console.WriteLine("There are " +
         lnFolders.ToString() + " folders");

        //Display the folder names.

        for (int i = 1; i <= lnFolders; i++)

        {

          object loFolder = 

            this.CallMethod(loFolders, "Item",
             new object[] { i });

          string lcFolder = (string)
           This.GetProperty(loFolder,
             "FolderName");

          System.Console.WriteLine
           (lcFolder.ToString());

        }

        //Run a report.

        bool llOK =
         (bool) this.CallMethod(loRE,
         "RunReportToFile", new object[]
         {"Customers", "C:\\Temp\\Test.pdf"} );

        if (llOK)

            System.Console.WriteLine
             ("The Customers report was output " +
             to PDF.");

        else

            System.Console.WriteLine
             ("The Customers report was not " +

              run successfully.");

        System.Console.ReadLine();

        }

      }

    //This method uses reflection on the specified
   //object to call the desired method, passing
   //it any necessary parameters.

    private object CallMethod(object toObject,
     string tcMethod, params object[] toParams)

    {

      return toObject.GetType().InvokeMember(
       tcMethod, BindingFlags.InvokeMethod,
       null, toObject, toParams);

    }

    //This method uses reflection on the specified
   //object to read the value of a property.

    private object GetProperty(object toObject,
     string tcProperty)

    {

      return toObject.GetType().InvokeMember(
       tcProperty, BindingFlags.GetProperty,
       null, toObject, null);

    }

  }

}

Updating the Stonefield Query for Sage Pro ERP Data Dictionary

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

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

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

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

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

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

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

Stonefield Query Version 3.2 Build 3105

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

All Versions

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

SDK

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

Sage Accpac ERP

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

Sage Pro ERP

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