Step 2, in which you select which fields appear in the report, is shown below:
The options available in this step depend on whether Stonefield Query is set to show application view or database view; this is controlled by the option in the lower left corner of Step 2. The difference between the two is that Database View displays a database-centric view of the data in Sage 300—that is, organized the way the tables and fields are in the database—while Application View shows fields the way Sage 300 does in its various dialogs. Database View is useful if you are familiar with the structure of Sage 300's database while Application View is better suited to those familiar with the user interface of Sage 300.
For Application View, the options are:
View: choose the view you want to select fields from.
Available fields: a list containing all reportable fields and views from the selected view that have not already been selected. A view is displayed as ; click the + sign in front of it to expand it and see the list of fields it contains. Hover the mouse pointer over a field to see any comments about that field in a "balloon" tip (the tip won't appear unless there are comments).
Selected fields for this report: a list showing the fields to be included in the report, with the table the field is from shown in parentheses. Fields in this list may be moved up and down to change the order in which the columns are printed on the report; to do this, click the button in front of the field to move and drag the button up or down in the list.
Add: clicking this button adds the selected field in the Available list to the Selected list. You can select several fields by holding down the Ctrl key as you select them; clicking Add adds all selected fields to the Selected list. You can also add the field by double-clicking it in the Available list, selecting it and pressing Enter, or dragging the field from the Available list to the Selected list. The field is removed from the Available list. If you click Add when a view is selected, all fields in that view are added to the Selected list.
If you select a field from a table that is not related in any way to the other tables involved in the report, you are asked if you want to create a relationship between the tables.
Remove: clicking this button removes the selected field (or fields if you have selected more than one) from the Selected list. You can also remove the field by dragging it from the Selected list to the Available list. The field is added to the Available list (although you may not see it if the view the field is from is not the selected view).
For Database View, the following options are available:
Table: a drop-down list that displays the names of the tables you can select fields from.
: Stonefield Query by default displays fields 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. For example, for typical address fields, you would see this order alphabetically:
Address
City
Contact Name
Country
Customer Name
Postal Code
Regionwhereas they'd appear like this in table order:
Customer Name
Contact Name
Address
City
Region
Postal Code
CountryClick this button to display fields in table order. Click it again to display them alphabetically. The setting of this button is remembered, so if you edit a report later, it has the same setting.
: 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 this 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 this button to reduce the list of tables to only those directly related to ones you've already selected fields from. Click it again to restore the list to all tables.
Available fields: a list containing all reportable fields from the table shown in the Table list that have not already been selected.
Selected fields for this report: a list showing the fields to be included in the report, with the table the field is from shown in parentheses. Fields in this list may be moved up and down to change the order in which the columns are printed on the report; to do this, click the button in front of the field to move and drag the button up or down in the list.
Add: clicking this button adds the selected field in the Available list to the Selected list. You can select several fields by holding down the Ctrl key as you select them; clicking Add adds all selected fields to the Selected list. You can also add the field by double-clicking it in the Available list, selecting it and pressing Enter, or dragging the field from the Available list to the Selected list. The field is removed from the Available list.
If you select a field from a table that is not related in any way to the other tables involved in the report, you are asked if you want to create a relationship between the tables.
Remove: clicking this button removes the selected field (or fields if you have selected more than one) from the Selected list. You can also remove the field by dragging it from the Selected list to the Available list. The field is added to the Available list (although you may not see it if the table the field is from is not the selected table).
Regardless of whether application view or database view is selected, the following additional options are available:
Advanced: click this button to customize how the report runs.
New Formula: click this button to define a new formula. The new formula is automatically added to the report.
Edit Formula: click this button, which appears instead of New Formula when you have selected an existing formula, to edit the formula.
Values: displays a list of the unique values in the field selected in either the Available or Selected list, depending on which one was selected last. This is handy if you're not sure what data a field contains.
Properties: clicking this button displays the Properties dialog. You can also display this dialog by double-clicking the field in the Selected list or selecting it and pressing Enter. This dialog allows you to specify how the field looks, how grouping is performed, and whether summary totals are shown or not.
Another option may appear if you've used the Analyze Reports function in the Tools menu and are displaying Database View: a button with beside Table. Click this button to show only those tables and fields that have been used in reports before. Click it again to show all tables and fields.
© Stonefield Software Inc., 2023 • Updated: 01/26/21
Comment or report problem with topic