Description
Description
When viewing a List or Report of the Task [task] table, the state column can often lead to confusion. This confusion stems from a couple facts.
- Firstly, [task] is the base table for many task-related tables (incident, change_request, problem, chat_queue_entry, etc...).
- Secondly, the 'state' is an Integer field type. The states we see on these tables are labels defined in the Choice [sys_choice] table.
- Finally, the extended tables often define their own labels for Integer values.
The result of the above considerations is that often-times an extended table's state choice already exists on a base table, with a different label.
Procedure
This is apparent on the out-of-box offering of the Service Desk module My Groups Work. You may notice the following symptoms:
- When filtering by state, users do not see the expected filter option ('New' is not present in the dropdown, for example)
- When grouping by state, different states appear to be grouped (You may see 'Pending' tasks and 'New' change requests grouped together) This is because they both share the same integer value (-5 on a baseline instance)
Note in the screenshot, we have filtered where 'state is Pending.' This queries for the integer value of -5, which returns any task-related record with a -5 state value. You can see this includes 'New' change request records.
Additional Information
We can determine which labels are present in the drop-down. Looking at the following screenshot, it may appear random.
The order of the state fields that show up follow these rules:
- All the sys_choice options for the base table come first.
- It will not add duplicate values for the sys_choice.value field.
- Choices are ordered by sequence, then by label.
- Only choices where inactive = false are included.
- After all the base tables are added, all the options for the extended tables are added.
- Extended tables are added one table at a time.
- Extended tables follow all the same rules as (2 through 4).
Considering the above, we can see which values are displayed/shared. Out of box, the choices will look something like this (Note: bold indicates the label will appear in the filter dropdown. Comma-delimited text indicates an extended table shares the integer value using another label):
- -5 Pending, New
- -4 Assess
- -3 Authorize, Awaiting Information
- -2 Scheduled
- -1 Implement
- 0 Review
- 1 Open, Waiting, New
- 2 Work in Progress, In Progress, Known Error
- 3 Closed Complete, Closed, On Hold, Completed, Pending Change
- 4 Closed Incomplete, Skipped, Canceled, Closed Escalated, Closed/Resolved
- 6 Resolved
- 7 Closed Skipped, Closed by Client, Closed
- 8 Closed Abandoned, Canceled
This applies to any similarly-configured choice field. [task.state] is a great example to study the architecture and logic because of it's prevelance on any instance. Please note there may be plugins which include additional task-related tables, or other configurations such as customized choice lists which impact the order and display.
If this is causing confusion to your end users, our recommendation would be to report on the extended tables (like incident/change/problem) directly rather than on task. This would also improve performance by marginalizing the query.
A great Community Blog also exists discussing this in How to report on and group tasks by State field
Applicable Versions
London, Kingston, previous