Thursday, April 9, 2015

Finding the Fields You Need for a Report

If there are a lot of fields in your database, it can be challenging to find the one you're looking for. Here are some tips that may help:

  • If you're not sure what a field contains, click the Values button in step 2 of the report wizards. The different values displayed in the dialog often give a clue as to what the field is used for.
  • Fields are displayed by default 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. Click the SORTASCXPSMALL button to display fields in table order.
  • 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 the findxpsmall 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 the RELATIONXPSMALL button to reduce the list of tables to only those directly related to ones you've already selected fields from.
  • If you're familiar with the name of your application's tables and fields, you might find it easier to locate the tables and fields you want by their real names. Turn on the Display real table and field names option in the Options dialog to display the real name followed by the descriptive name in parentheses.
  • Sometimes the fields in an application are organized into pairs of "header-detail" tables; invoices and invoice lines is a classic example. These are called "header-detail" pairs because the information displayed in the header or top of an invoice goes in the invoices table and the line items go in the invoice lines table. When looking for a particular field, ask yourself if there's only one of these things in the invoice (invoice number, invoice date, customer number, and so on) or if there can be more than one (product description, quantity sold, etc.). If there's only one, you'll find the field in the header (in this case, invoice) table. If there's more than one, it'll be in the detail (invoice lines in this example) table.
  • Application view shows fields the way your application does in its various dialogs. However, not all fields may appear when Application view is turned on in step 2. If you can't find a particular field, try turning on Database view. (Your version of Stonefield Query may not support application view.)
  • A lot of fields may contain default or even no data; for example, the fields may be there to support certain features in the program, but you may not use those features. To hide those fields, choose Analyze Database from the Tools menu. This function goes through your database, looking at all fields in all tables for fields containing meaningful data (that is, more than a single unique value), and marking those as "favorite." After you've run this function, step 2 in the report wizards displays a FAVORITES button. If you turn this on, the table and field lists show only those tables and fields marked as "favorites." (Your version of Stonefield Query may not support favorites.)