SQL Views, NULL values and SmartList tools

I discovered something some time ago, and could have sworn I wrote a blog article about it, because I thought it was a tip that should be shared with the world. Today, I stumbled across the same problem again, and went to look up my post... yes I refer back to my own posts to refresh my recollection from time to time.

Anyway, I discovered I had neglected to write an article about it, but found the issue mentioned in a broader article written by luminary Dave Musgrave called Understanding how Dynamics GP works with SQL

In this article Dave explains that since Dynamics GP grew up on databases like Btieve and Ctree, and these databases typically avoided the occurrence of NULL values in the data set by initializing number and currency fields with Zero and String fields with blanks. Consequently, Dynamics GP doesn't intuitively know how to deal with NULL values.

This is important for users who are working with SmartList tools like SmartList Builder or Designer.

One of the best inherent features in SmartLists is the ability to search fields based on values or the lack thereof. If a NULL value is returned in a SmartList field, then there is no way to search/restrict your results based on the return of a NULL value.

The easiest way to resolve this issue is during the creation of the view your SmartList is built on. If you have a field returning NULL values, it is best to transform these null values into blanks instead. 

Here is an example of how to make this work.

Select 
CASE WHEN TABLE.FIELD IS NULL THEN ''
            ELSE TABLE.FIELD
END 'Field Label'
FROM TABLE

This will result in SQL and ultimately your SmartList column returning either nothing or the actual value in the field. The value nothing can be returned by searching for blanks in the field.

Failure to do this will both clutter your SmartList and limit the its functionality, so I highly recommend taking this into account when building SQL Queries, Views and SmartLists. 

NOTE: I did in fact write a blog post about this last October, and apparently mislaid it. Here is a link.

No filters on Null Values in SmartLists

CASE to convert MOP10213.MO_Activity_Reason_I to usable text values - SQL

I have amassed a number of useful CASE statements, which can be incorporated into SQL queries run against Dynamics GP. When Querying the Manufacturing Order Activity Reason Code field in Dynamics GP AKA (MO_Activity_Reason_I ), use the following case statement to return text versions of the default statuses in Dynamics GP - if the code is not correctly translated the code value will be returned.

CASE MOP10213.MO_ACTIVITY_REASON_I
WHEN 17 THEN 'Scheduled' --Odd but sometimes this value is used

WHEN 31 THEN 'Status Change'

WHEN 32 THEN 'Allocate'

WHEN 33 THEN 'Reverse Allocate'

WHEN 34 THEN 'Issue'

WHEN 35 THEN 'Reverse Issue'

WHEN 36 THEN 'Scrap'

WHEN 37 THEN 'Reverse Scrap'

WHEN 38 THEN 'Raw Material Relief'

WHEN 39 THEN 'Finished Good Post'

WHEN 40 THEN 'Reverse Finished Good Post'

WHEN 41 THEN 'Close'

WHEN 42 THEN 'Complete'

WHEN 43 THEN 'Post Variance From WIP'

WHEN 44 THEN 'Labor Data Collection'

WHEN 45 THEN 'Machine Data Collection'

WHEN 46 THEN 'Outsourced Costs'

WHEN 47 THEN 'Scheduled'

WHEN 48 THEN 'Picklist Built'

WHEN 49 THEN 'Change Working Routing'

WHEN 50 THEN 'Financial Activity'

ELSE convert(Varchar(2),MOP10213.MO_ACTIVITY_REASON_I,1)

END AS Reason_Code

CASE Statement for SOPTYPE in Dynamics GP Sales Tables

I have amassed a number of useful CASE statements, which can be incorporated into SQL queries run against Dynamics GP. When Querying the SOP Type Field in Dynamics GP Sales Open [SOP10100].[SOPTYPE] or History [SOP30200].[SOPTYPE] tables: use the following case statement to return text versions of the default SOPTYPE in Dynamics GP:

select

CASE SOPTYPE

      WHEN 1 THEN 'Quote'

      WHEN 2 THEN 'Order'

      WHEN 3 THEN 'Invoice'

      WHEN 4 THEN 'Return'

      WHEN 5 THEN 'Back Order'

      WHEN 6 THEN 'Fulfillment Order'

      ELSE 'ERROR'

END SOPTYPE_TEXT,

CASE Statement for Manufacturing Order Status in Dynamics GP (MANUFACTUREORDERST_I)

I have amassed a number of useful CASE statements, which can be incorporated into SQL queries run against Dynamics GP. When Querying the Manufacturing Order Status Field in Dynamics GP (MANUFACTUREORDERST_I): use the following case statement to return text versions of the default statuses in Dynamics GP:

case WO010032.MANUFACTUREORDERST_I

      when 0 then 'invalid entry'

      when 1 then 'Quote/Estimate'

      when 2 then 'Open'

      when 3 then 'Released'

      when 4 then 'Hold'

      when 5 then 'Canceled'

      when 6 then 'Complete'

      when 7 then 'Partially Received'

      when 8 then 'Closed'

      else 'ERROR'

End MO_Status

Here is a list of the Dynamics GP Tables containing MANUFACTUREORDERST_I

ISSP0201 – Manufacturing Series Sales Order Preferences

MOP10213 – MOP Order Activity

MOP1070 – MOP Edit MO Status Header

MOP1071 – MOP Edit MO Status Line

MOP3000 – MOP Variances

MOPGetMOVariances

MOPV1100 - MFGMOList

MOPV3001

MPO10001 – MRP CRP Scheduled Orders

mvarMOStartEndQtys

PK010033 – MOP Item Master

SOPS113B – MOP Sys Preferences

WO010032 – Manufacture Order Master

WO010213 – Manufacture Order History

CASE Statement for Inventory Item Master Item Types [IV00101].[ItemType]

I have amassed a number of useful CASE statements, which can be incorporated into SQL queries run against Dynamics GP. When Querying the Item Master Table in Dynamics GP (IV00101), use the following case statement to return text versions of the default Item Type field (ItemType):

case [IV00101].[ItemType]

      when 1 then 'Inventory'

      when 2 then 'Discontinued'

      when 3 then 'Kit'

      when 4 then 'Misc Charges'

      when 5 then 'Services'

      when 6 then 'Flat Fee'

      else 'ERROR'

end   ItemTypeText

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'

      ELSE rtg.RTSEQNUM_I

End Routing_Sequence,