Sage 300 allows you to define optional fields that can contain custom information you want to store. These optional fields are quite difficult to report on in other reporting tools because of the way Sage 300 stores them: the values are stored in a different table than the main table the optional fields are for. For example, if you create an optional field for customers, which are stored in the ARCUS table, the values of those optional fields are actually stored in a table named ARCUSO. This requires joins between those tables with a complex filter. Also, the values are stored as character values rather than the actual data type you specified. This makes it difficult to create subtotals on numeric values, for example.

Stonefield Query make it as easy to query on optional fields as it does any other field. Optional fields appear in the same table the optional fields are for. For example, an optional field for customers appears in the Customers table using the descriptive name you gave it in Sage 300. So, simply select the fields you want and they'll appear as you expect in a report.

Behind the scenes, Stonefield Query handles optional fields by creating a virtual field in the data dictionary for each one and placing it in the table the optional field is for. The field is named "A" plus the name of the optional field. For example, if you named the optional field COLOR, it's named ACOLOR in the data dictionary. The actual optional field tables (such as ARCUSO) are non-reportable so you don't see them in Stonefield Query.

When you select an optional field for a report, Stonefield Query opens the optional field table, finds the record with the ID that matches the source table ID (in the case of customers, matching IDCUST) and has OPTFIELD set to the name of the optional field ("COLOR" in this example). It then converts the contents of the VALUE field to the appropriate data type for that field (since it's stored as a character value rather than the proper data type) and uses that as the value for the virtual field.

This makes it a lot easier for you. You see optional fields in the tables you expect to see them and Stonefield Query prints out the value using the correct data type. You don't have to know that there are two tables involved, different lookups depending on the specific table, and data conversion issues.

If you know you won't need access to Sage 300 optional fields, you can make Stonefield Query start up faster by adding a line with "CustomMeta=N" to the [Meta Data] section of SFQuery.INI.