As mentioned our Working with MBO Collections article, it is possible to call the setWhere() method to restrict the members of the collection. There is an alternative method for those folks that might be uncomfortable writing SQL clauses. This feature is called Query By Example or QBE. To access it, use the setQbe() method as shown in the following example:

mboSet.setQbe("wonum", "1%")

This applies a restriction such that for each member of the resulting work order collection the WONUM attribute will begin with the character “1”. As discussed earlier, such a filter is effective only when the collection is first retrieved, so it may be necessary to call the reset() method. The above example is very simple, but it is possible to make successive calls to setQbe() on different attributes – the result is the concatenating together of the set of restrictions with AND logical connectors. A second version of setQbe() concatenates the restrictions together with OR logical connectors – in this version the arguments are passed in a slightly different format. It is not possible to set more than one restriction on a given attribute, nor is it possible to perform a QBE on an attribute of a related object rather than on attributes of the objects in the collection themselves. Thus, for example, selecting work orders based on whether the Asset is rotating would not work, because the attribute in question is a member of the Asset object, not the WO object. To fulfill the request would require accessing related objects of a type different from the ones that make up the collection. Such actions are not permitted. If this is too restrictive, use the setWhere() method. To clear the contents of the entire QBE, call the resetQbe() method. It is important to understand the relationship between setWhere() and setQbe(). Internally, there is a complete where clause that is used for fetching records to the collection. The complete where clause is built by calls to both setWhere() and setQbe(). New restrictions set by calling setQbe() are AND’ed to existing restrictions that were set using setWhere(), and vice-versa. The resetQbe() method clears only those restrictions set by setQbe() calls. The setWhere() method can clear restrictions by passing an empty string:

mboSet.setWhere("")

This call, however, clears only those restrictions set by a setWhere() call. Thus, if it is necessary to start with an totally empty where clause, both setWhere(“”) and resetQbe() must be invoked. At first glance it may not be obvious why one should use the setQbe() method and not setWhere(). The setQbe() method hides many of the database sensitivities from the programmer. It places literals in the quote characters as necessary and adds any appropriate database function calls to support querying on date, time and timestamp fields. Case insensitive queries can also be handled more easily using this mechanism. The programmer has better control over the treatment of a search string because two methods have been provided for this purpose: setQbeExactMatch() and setQbeCaseSensitive().

woSet.setQbeExactMatch(True) woSet.setQbeCaseSensitive(True)