I’m making a custom view to pull the labor class from Routings to show in Customer Order Lines, it functions by checking the first number of the labor class and giving it a 10, 40, 50, or using the site number from COL otherwise based on the result. The issue is if there is no corresponding entry in Routings then it would default to the site instead, it will just show up as null. Is there a way to force IFS to use the source field?

Here is the main code I have in the SELECT custom field.

SELECT CASE WHEN SUBSTR(labor_class_no,1,1) = '1' THEN '10' WHEN SUBSTR(labor_class_no,1,1) = '4' THEN '40' WHEN SUBSTR(labor_class_no,1,1) = '5' THEN '50' WHEN :catalog_type = 'Non inventory part' THEN :contract ELSE :contract END FROM ifsapp.routing_operation where part_no = :part_no and contract = :contract and routing_revision = (select MAX(routing_revision) from ifsapp.customer_order_join left outer join ifsapp.routing_operation where part_no = :part_no and contract = :contract and alternative_no = '*') and operation_no = (select MAX(operation_no)from ifsapp.routing_operation where part_no = :part_no and contract = :contract and alternative_no = '*') and alternative_no = '*'
 

This results in entries like this, where the null values should be pulling from the contract in Customer Order Lines.

This is one version I was testing with that gives me some more of the fields but will error out due to multiple entries in one query.