Filtering Lists (Advanced)
Purpose
The purpose of this topic is to explain how to use more complex filter commands to query the data stored in Timemaster.
Operation
Sample Timesheet Dataset
Rec No |
Date |
Staff ID |
Project ID |
Client Code |
Hours |
Recharge Cost |
|---|---|---|---|---|---|---|
1 |
01/01/2014 |
MM |
C1234 |
BL |
7.5 |
112.5 |
2 |
02/01/2014 |
MM |
C1234 |
BL |
7.5 |
112.5 |
3 |
03/01/2014 |
MM |
C1240 |
Lotus |
8 |
120 |
4 |
01/01/2014 |
SP |
C1240 |
Lotus |
7.5 |
75 |
5 |
02/01/2014 |
SP |
C1240 |
Lotus |
7.5 |
75 |
6 |
03/01/2014 |
SP |
C1240 |
Lotus |
8 |
80 |
7 |
03/01/2014 |
AB |
C1240 |
Lotus |
6 |
30 |
AND's vs OR's
A common mistake users make when querying the data is the misuse of the AND and OR operators.
For example, if you want to retrieve all records for Staff ID "MM" and "SP" you would actually use the 'OR' operator not the 'AND' operator.
This query would return no records:-
And/Or |
( |
Field |
Condition |
Value |
) |
|
|
|
Staff ID |
= |
MM |
|
Condition 1 |
AND |
|
Staff ID |
= |
SP |
|
Condition 2 |
This can be a little confusing as you want to retrieve the records for Staff ID "MM" AND Staff ID "SP".
The key thing to remember is that the computer evaluates each record against the requested query.
Once you understand this it is obvious that Condition 1 and Condition 2 can never be met as there is not a record where the Staff ID equals "MM" AND "SP".
So, if you restructure your query swapping the AND for an OR you will get the desired results;-
And/Or |
( |
Field |
Condition |
Value |
) |
|
|
|
Staff ID |
= |
MM |
|
Condition 1 |
OR |
|
Staff ID |
= |
SP |
|
Condition 2 |
This is because Condition 1 OR Condition 2 is being met.
Using AND's with OR's - the use of brackets!
Now that you understand the use of the AND and OR operator we can now look at using them both together.
Suppose you would like to query the data to obtain all the records that Staff ID MM and SP have worked on where the hours are greater than 7.5.
You may construct the query as below;-
And/Or |
( |
Field |
Condition |
Value |
) |
|
|
|
Staff ID |
= |
MM |
|
Condition 1 |
OR |
|
Staff ID |
= |
SP |
|
Condition 2 |
AND |
|
Hours |
> |
7.5 |
|
Condition 3 |
However, this will not return your desired results as it groups Condition 2 and Condition 3 together.
To obtain the desired results you would need to introduce brackets as follows;-
And/Or |
( |
Field |
Condition |
Value |
) |
|
|
( |
Staff ID |
= |
MM |
|
Condition 1 |
OR |
|
Staff ID |
= |
SP |
) |
Condition 1 |
AND |
|
Hours |
> |
7.5 |
|
Condition 2 |
The use of brackets groups the conditions together. In this instance both the conditions in Condition 1 will need to be TRUE before it then applies Condition 2.
Test your knowledge
Question 1: How would you retrieve all Timesheet records recorded on 02/01/2014?
|
Question 2: How would you retrieve all Timesheet records recorded by Staff ID "MM' where this staff member has booked 7.5 hours on 02/01/2014?
|
Question 3: Construct the filter query that could be used to retrieve all records that Staff ID "MM" and Staff ID "SP" have recorded that are greater 7.5 hours on either 02/01/2014 and 03/01/2014.
|
Question 4: How would you construct a filter query to show ALL of staff ID's "MM" records booked to Project ID "BL" and all other staff members records that have a Recharge Cost less than £100 where the hours are greater than 6.
|