[Useful Tiny Little Things] Filtering records - search tips
LeandroCassa 270002840B Comments (5) Visits (17354)
Welcome to Useful Tiny Little Things, a series of topics that I will publish in the Process Automation blog. My name is Leandro Cassa and I work at IBM as the CCMDB Level 3 support team leader. The purpose of this series is to provide useful simple things that we sometimes have no idea exist. These tips apply to CCMDB, Tivoli Asset Management for IT, Service Request Manager, and other products based on Tivoli's process automation engine.
This week I'll talk about filtering records, which are everywhere on TPAE based systems.
Last week we discussed the the UI table. Most of the UI tables on your system are likely to be filterable.
OK, enough talking, lets get going on search tips!
Again I'll use the Work Order Tracking application as a sample for my experiments here. Let's use the following screen shot as a data table for our experiments (click on the image to enlarge):
As you can see I'm already using a very common filter wildcard, the %.
% or * - is for one or more characters
_ or ? - is for a single character
Let's play around. "%problem" is already in the Description field. If we change that to "%Plumbing%" only the first row would show up. On the other hand if we filter the Description by "13%", only the first record won't show up.
Let's try the single character match now (_ or ?). If we filter the Work Order (wonum) field by "300__", the result is the second and the third record appears. That is because the fourth record is 30157; it does not start with 300. Notice that you can mix filters in different fields. In other words, you could filter Description and Work Order (WONUM) together, at the same time.
The screen shot on the left shows the results for mixing field filters.
Well, those are the basics. I guess you have no idea about what is coming next.
You can actually search for null entries and reverse (use the logical NOT) for any searches.
!= - is the logical NOT, which means, return whatever does not match my search.
~null~ - search for null entries.
Some samples: "!=1003" return all records but the one with Work Order field (wonum) 1003. Notice you cannot mix this with what we already learned; the ones we are using right now are for unique values only, not wild cards.
You can use it for any fields, just as you use exact searches, but this is truly useful for unique values.
Talking about unique values, at some point you might need to list records with null values, use "~null~" to filter those. On our data sample table (the first screen shot on this post) we have a record with no Location set, therefore a null location. Filtering that with "~null~" results on that record only (Work Order 30058).
But you could also reverse that. If you are looking for a non-null location, accomplish this by using the "!=", just like this: "!=~null~".
There is more yet.
= - is for exact matches
Y or N - is for checkboxes
You probably have seem a "=something" around, it matches the exact values. So if you search for "=1003" on the Work Order field it only shows the record 1003. The only exception for the use of = is for dates. In that case, the match must not be exact. We'll cover that soon.
Checkboxes also can be filtered by using Y for checked (YES or TRUE) and N for unchecked (NO or FALSE), so if you have a field which is a check box, you could use =Y or =N.
Now the grand finale.
> or < - greater or less
>= or <= - greater and equal or less and equal
Those are useful and interesting, but they can only be applied for numbers and dates. In dates, the greater or less (> or <) are intuitive: if you filter the Scheduled Start using ">10/25/01" the records 30056 and 30157 show in the results, although no hour was set on the filter. Given that, ">=" or "<=" are useful if you have midnight hours.
The same behavior of not setting an hour value works if you use "=" for date fields. For example, if you filter the Scheduled Start using "=10/25/01", you still get the same records 30056 and 30157, just because they are in the same date.
The following results in a single record only (due to the few records used for this post), but it is a summary of all filters used on this post.
IMPORTANT NOTE: Using wild cards, especially at the beginning of a string and with no other criteria, almost always ends up with a full table scan.
Be aware that a query in a large table can take many minutes, which may cause the impression that the system is actually hanging.
That's it for this week. Hope this helps.