Using Criteria Selection for Database Queries

To further filter the search to a more specific range of data, set the conditions in Criteria Selection tab. You can select exactly the values or ranges of values you want to display in the results.

  • Consider the following restrictions when using relations in filters:

    • If there are multiple references to relations in a filter, all relations referenced must be One-To-One relation types.

    • If a One-To-Many relation is referenced in a filter, it must be the single and only relation reference of any kind in the filter.

    • Multiple references to the same One-To-Many relation are not allowed.

    • Mixing One-To-One and One-To-Many relations is not allowed in a query.

Selection Criteria

To set criteria, select the following:

  • The field(s) that contain values you want to restrict the search to.

  • The values you want to restrict the search to.

  • Operators that describe the relationship between the field and the value.

    Note: You can select from a list of available values by choosing the Display Sample Data for Selection option and selecting the field again. Available values appear in the list.
    Operator Description
    = Equal to the value you set.
    != Not equal to value you set.
    < Less than the value you set.
    <= Less than or equal to you set.
    > Greater than the value you set.
    >= Greater than or equal to the value you set.
    ~ Similar to value you set.
    !~ Not similar to the value you set.

  • If you set more than one criterion, the conjunction that describes the relationship between the criteria. Possible values are AND and OR.

Grouping Selection Criteria

You can further define the data you want by grouping selection criteria using open and closed parentheses. You can either specify your own parentheses in Selection Criteria fields, or you can select two or more criteria and click the Group Criteria button. The following example illustrates how criteria grouping works.

If you want to create a report that displays all purchase orders for a certain buyer for the months of January and March of 2006, you might set up your selection criteria as shown below.

Conjunction Field Operand Criteria
BUYER-CODE = JS
And PO-DATE >= 01/01/2006
And PO-DATE < 02/01/2006
Or PO-DATE >= 03/01/2006
And PO-DATE < 04/01/2006

The returned results are probably not what you expected because of the Or operand and the lack of any parentheses. The records returned could be for January purchase orders for Buyer Code JS plus any PO (for any buyer) where the PO Date is in March.

To get the results you want, you need to place parentheses around the PO-DATE criteria as a whole, and then you need to put each pair of PO-DATE criteria within parentheses. Click in the first PO-DATE field. Drag your mouse down to select the last PO-DATE field, and then click Group Criteria. Next, for each pair of PO-DATE fields, click in the first field, drag the mouse through the second field, and then click Group Criteria. The Query Wizard adds the parentheses in the correct places. When the grouping parentheses are added, the data returned would be only for purchase orders for buyer JS that are also in the January and March date ranges.

Using Fields from Related Tables in Selection Criteria

You can use a field value from a related table as a selection criteria. The related field used for selection criteria must exist in the Selected Fields list you chose on the Fields tab.

Note: 

  • If you use a related table field in selection criteria, set the Maximum Primary Records to Return and the Maximum OTM Values to Return to a high number. See "Table Settings" for more information on how these settings affect your query results.

  • If the related table field has detail lines or has Occurs fields, you cannot use it for selection criteria. Doing so causes the query to fail.

  • If you use related table fields, you cannot use selection criteria that require parentheses.

If you use both base table and related fields as search criteria, your query results may be different than you expect. The reason is that the Query Wizard processes criteria as follows:

  • First, the DME processes base table fields for matches.

  • Then Infor Lawson Add-ins applies the relational field selection criteria to data returned by the DME from the base table fields.

This type of processing means that you may not get as many records as you request in the Maximum OTM field.

For example, if the Maximum OTM value is 25, the DME only returns the first 25 values that meet the base record table selection criteria—it does not take any relational fields into consideration in its selection. Once it has 25 records, the Query Wizard applies the relational field selection criteria to the 25 records. As a result, fewer than 25 records may appear in the spreadsheet. Only those records that match specific processing criteria return from the search. The records must:

  • Meet all the selection criteria (both base and relational).

  • Be one of the first 25 OTM values in the database.

There may be other records that meet the selection criteria, but if they aren't in the first 25 records they are not returned.

  • If the total number of matches is still not equal to the Maximum Primary Records to Return as expected by the user, Infor Lawson Add-ins will get the next set of records and then apply its filtering process using the relational field selection criteria.

  • The cycle continues until the total number of matches is already equal to the Maximum Primary Records to Return expected by the user or there are no more available records to retrieve.

Grouping selection criteria in the proper sequence is critical for obtaining the data you want. When grouping, remember that you can only use parentheses to group criteria when you have base table fields. Related field criteria cannot be grouped using parentheses or errors occur.

Note: You will get the results you expect from your search only if you enter all your base field criteria first, then enter all relational fields.