Assigning Criteria for Database Queries

After you've chosen the fields to query, you want to limit the search to the range of data you want to display. The Criteria tab contains parameters you can use to further define the query.

Note: We recommend that you generate a worksheet with an index-based query, then view the results and use the selection criteria to further limit the search.

There are several ways to limit a database query:

  • Use an index to limit the query search to a pre-defined set of fields. This method results in the fastest query, because the DME call skips fields that are not specified in the index. You can type in a key value for any of the fields in the index to further define the query.

    Indexes are part of the database definition. You cannot use the Infor Lawson Add-ins to add, change, or delete an index.

  • Use a condition to limit the query to a certain range of data. Available conditions for the fields you chose display in the Conditions list. For example, the CURRENT YEAR condition limits the query to data for this year only. Select a condition from the list to apply it to the query.

    Conditions are part of the database definition. You cannot use the Infor Lawson Add-ins to add, change, or delete a condition.

  • Use the Maximum Primary Records to Return and the Maximum OTM Values to Return options to control the number of values your query returns.

Table Settings

Index Select an index to base the query on. The associated key fields appear in the Index Key column.
Key Values

Specify values for the index keys to limit the query to the data that has those values. To add multiple key field values, separate values with a semicolon. To select a range of values, separate values with ->.

Note: When you add key values for more than one key field, do not skip lines. The query only reads consecutive key values. If you skip a line, any key values that you enter after the blank line are not recognized by the system.
Conditions Select a condition to further limit the data that is searched.
Clear Index and Condition

If you select this button, all index key values and conditions you specified are cleared.

Note that this button does not remove conditions from the Condition list. Conditions are part of the database definition. You cannot use the Infor Lawson Add-ins to add, change, or delete conditions.
Maximum Primary Records to Return

Set a maximum number of primary records to return.

  • This is the total number of primary records that the user expects to be returned.

  • If you leave this field blank, the value defaults to 600 records returned.

  • If you specify 0 in this field, the value defaults to 65,520 records returned.

  • If your query includes fields with detail lines or fields with one-to-many relations, the results may include more worksheet lines than the maximum number of primary records you specified. This is because records with detail lines or one-to-many related values require more than one line on the worksheet.

Note: When you set the Maximum Primary Records to Return values to a high number, your queries run more slowly because they are processing more data and a paging call is being implemented.

The Add-ins application restricts database table queries to a maximum of 65,520 records to allow for headers, a title, and subtotals. Exceeding this limit may mean that some lines do not return or data may be lost.
Maximum OTM Values to Return

In the Maximum OTM Values to Return field, set the number of one-to-many relationship values you want to return. The maximum value is 600.

Note that when you set the OTM values to a high number, your queries run more slowly because they are processing more data.

Note: 
  • If your query uses related table fields that you specified in the selection criteria, set this number high. The DME query only returns the number OTM records you specify here. The Query Wizard applies selection criteria involving the related fields only to the returned DME. If the Maximum OTM Values to Return value is too low results may be incomplete.
  • If you set the value to 1, there is no limitation for number of related tables.
  • If you set the value greater than 1, you can only include up to 2 related tables with One-To-Many relation when defining selection criteria. There is no limitation for One-To-One relations. The result of the query will produce a tabular output similar to an SQL join query output, displaying the OTM records distributed on its corresponding primary and related records.