Stonefield Query Version 3.2 Build 3226

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

• Stonefield Query is now available in Spanish thanks to Juan Antonio Santana.
• Stonefield Query now supports linked reports that pass a value to a report with an "is between" ask-at-runtime condition by using the same value for both the starting and ending values.
• Multiple text lines (for example, in a memo field) are put into the same cell in a Microsoft Excel spreadsheet rather than separate cells.
• An issue where the dollar sign is cut off for currency values or the value shows as **** when the largest-width item in the column is a negative value was fixed.
• The correct currency symbol now displays in charts.
• You can now call the built-in GetConditionValue function from an expression in the Advanced Report Designer.
• Using a currency field in the data area of a cross-tabulation report but formatting it as "Count" no longer displays the currency symbol.
• Cross-tab data columns are now sized properly in the unusual case where all row totals are zero (that is, individual columns have a mixture of positive and negative values so that the row total is zero and all rows have this condition).
• You no longer get an error if you edit a group in the Maintain Users and Groups dialog, select the Users tab and when asked to save, choose No, and then change the license type.

SDK

• Joins involving fields with the same name as the tables they are in (such as "Category.Category") are now handled properly.
• You can now access the FilterConditions object in DataEngine.FinalizeSQLStatement scripts.
• Anonymized projects no longer give an error looking for SQOUTPUT.EXE when you print a report.
• Running a report with ask-at-runtime filter conditions and a custom SQL statement from SQProxy no longer displays the ask-at-runtime dialog if you specify that the conditions should be ignored by passing blanks for their values.

Sage Accpac ERP

• New Combined Invoices & Credit Notes and Combined Invoice & Credit Note Details tables in Order Entry make it easy to report on both invoices and credit notes on the same report.
• You can call new built-in GetStartingBalance and GetStartingBalanceFYPeriod functions in an advanced layout report to display the starting balance for the specified account in the specified fiscal year and period.
• There's a new Quantity on Hand (Current) field in Inventory Control Location Details table. By default, Sage Accpac does not store the quantity on hand but calculates it. When day end is run, all pending transactions are applied and the Quantity on Hand (Last Day End) field is updated to the true quantity on hand. The Quantity on Hand (Current) field allows you to see what is on hand without running day end. It is calculated as Quantity on Hand (Last Day End) + Quantity Received Not Costed - Quantity Shipped Not Costed + Quantity Adjusted Not Costed.
• The built-in functions for date expressions, such as "Today's Date", now work properly for Sage Accpac dates.
• For optional fields that have descriptive as well as code values, a new "Optional field Description" field is available to display the descriptive value.
• There are several new fields in the General Ledger Posted Transactions table which you can use to create more compact transaction listings: Batch-Entry, which combines the batch and entry numbers, Source, which combines the Source Ledger and Source Type values, and Fiscal Year-Period, which combines the Fiscal Year and Period.
• Stonefield Query no longer permits a relationship between Accounts Receivable Customers and Project and Job Costing Projects unless you are using Sage Accpac 5.5 or later.
• The Purchase Order and Account Sets tables are now included in the Order Entry module.
• Since the Salesperson table was removed from Order Entry in Sage Accpac 5.5, all relationships to it now work with the Accounts Receivable Salesperson table.
• The Account Segment Code fields in the General Ledger Accounts table now display the segment code rather than its descriptive name. However, new Account Segment Description fields were added to display the descriptive names. This gives you the best of both worlds: you can display either the code or descriptive name or both.

Sage Pro ERP

• Stonefield Query now displays the proper values for the Transaction Type field in the Inventory Transaction table for issues and receipts.
• All fields in "current + history" tables now have same data type and size as those in the current tables.
• Stonefield Query now properly handles fields that were renamed in Sage Pro version 7.0 so you can use Stonefield Query with Sage Pro version 6.5 and earlier.
• The MACODE field in the Order Entry Transaction table is no longer available since it's obsolete.

Doug Hennig receives 14th Microsoft MVP Award

Doug Hennig, Stonefield's CTO, was awarded a Microsoft Most Valuable Professional (MVP) award for the fourteenth consecutive year.

The Microsoft MVP Award recognizes outstanding members of a technical community for their participation and willingness to help others. The MVP Award is given to exceptional technical community leaders who foster the free and objective exchange of knowledge by actively sharing their real-world expertise with technology users.

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);

    }

  }

}