Friday, October 24, 2014

SmartList Builder - No filter on NULL Values

Today I was working on a project for a client who has a customization, which imports Manufacturing Orders into their system. The project involves a configurable product with a configurable Bill of Materials and Routing.

If a user incorrectly configures the routing options, we've found sometimes the Work Order is imported with an invalid routing.  When this happens the result is a NULL value in the first routing sequence. The easiest way to identify these records and assign them a valid routing is using SmartList Builder. The problem with this is SmartList Builder does not allow a filter on NULL database values.

I routinely use validation columns in queries (i.e. when using a Union statement, I show Posted versus Unposted data, or Open versus History). Rather than create a validation column in my SQL view, I built the validation into the column using a CASE to return the word 'ERROR' when a NULL value was encountered. 

Now, when there is a broken routing the word ERROR shows up in the SmartList, and we've built as SmartList with Reminder to make sure these don't slip the net.

Here's what this looks like.

CASE when rtg.RTSEQNUM_I IS Null then 'ERROR'
End Routing_Sequence,

No comments:

Post a Comment