Why Assignment Datalookup rules bringing wrong data though there is a matching category/sub-category?



A Data lookup definition was created with proper Matcher table, Matcher Field Definition and Setter Field Definition in "dl_definition" record.

Also, corresponding "Assignment Data Lookups" entries are created in "dl_u_assignment" table.

As per the definition in "dl_u_assignment" and "dl_definition" tables, when user changes a category and sub-category on the incident record, corresponding "Assignment Group" needs to be set.

Though, there is a entry for the matching combination in dl_u_assignment.list, system doesn't set the "Assignment group" as expected instead setting it to a wrong "Assignment group".


Any supported release. 


When the category and sub-category is modified in the incident form, the data look up query fired as per below sql,

SELECT ... FROM ((dl_u_assignment dl_u_assignment0 INNER JOIN sys_metadata sys_metadata0 
ON dl_u_assignment0.`sys_id` = sys_metadata0.`sys_id` ) INNER JOIN dl_matcher dl_matcher0
ON dl_u_assignment0.`sys_id` = dl_matcher0.`sys_id` ) WHERE dl_matcher0.`active` = 1 AND
(dl_u_assignment0.`category` = '<category selected>' OR dl_u_assignment0.`category` IS NULL )
AND (dl_u_assignment0.`subcategory` = '<subcategory selected>' OR dl_u_assignment0.`subcategory`
IS NULL ) AND (dl_u_assignment0.`cmdb_ci` IS NULL OR dl_u_assignment0.`cmdb_ci` IS NULL ) AND
(dl_u_assignment0.`location` = '<location selected>' OR dl_u_assignment0.`location` IS NULL )
ORDER BY dl_matcher0.`order` limit 0,1

As per this query, the system looks for category with the selected one or null, also subcategory with selected one or null. So, when we setup "Assignment Data lookup", if there are many entries with same category but the corresponding sub-category is empty with lower order, system will pick up them instead of exact match. This is as per the product design.


To resolve the issue, you should make sure that there are no entries in "Assignment Data Lookup"("dl_u_assignment.list") with category is empty or subcategory is empty. 

So that platform assignment lookup query will work as expected and return the expected results.

Additional Information

Data lookup rules