3E Queries filtering on Status

I have been reviewing some of my own old work and some work by juniors & third party company's integrating methods. A type of query comes up now and again that works today, but is likely to cause you an issue in the future.

3E's database is well put together. There is a consistency across several areas with one little gem often overlooked. I'm talking about status fields. Let's use the Timekeeper table as an example. The Timekeeper table has a TkprStatus column which can be set by the firm, likely during implementation and forgotten about. A typical request might be to retrieve all the timekeepers who can enter time. Your firm might use the status 'Active' for this. So if you are wanting a list of the active timekeepers you might write something like:

select 
Timekeeper.Number,
Timekeeper.DisplayName

from Timekeeper
where Timekeeper.TkprStatus = 'Active'

What might not be known is most of these types of status fields have a corresponding table, in this case TkprStatus, which holds the code, description and a number of boolean fields for what that status can do. If you are looking for the timekeepers that can bill hours, instead of filtering for 'Active', join to TkprStatus and filter on the boolean field.

select 
Timekeeper.Number,
Timekeeper.DisplayName

from Timekeeper
join TkprStatus on TkprStatus.Code = Timekeeper.TkprStatus
where TkprStatus.IsAllowTime = 1

This future proofs things a little bit particularly if someone adds a new timekeeper status and your query is really meant to get all the timekeepers who can enter time. There may be times when you truly want the timekeepers with 'Active' status, however, it is good to be aware of the other options.

Matter (MattStatus) and Client (CliStatusType) are two other examples of such status tables to be aware of.