soptypedatabase vs soptype in Dynamics GP SOPEntry Window

I was asked today why I’d just typed SOPTypeDatabase rather than SOPType when we were code reviewing a bit of code. In the SopEntry Window the there is a field SOPType, this shares its name with the database field soptype that defines what type an order is:

Sales DocumentType SOPTYPEvalue GUI Drop Down FieldIndex position
Quote 1 1
Order 2 2
Invoice 3 4
Return 4 5
BackOrder 5 6
Fulfilment Order 6 3


The SopType field value originates from the position index of the selected item in the drop down box as shown on the SOP Entry form below. As the order of items in the combobox items does not follow the order used in the database, a translation is needed of values.

To make it easier, some helpful MS dev has created a field that does this for you (also helps binding the field no doubt in dex). This field, the SOPTypeDatabse field represents the currently selected SopType, but with a value as it should be stored in the database, hence the word database on the end of the field. Hence in most circumstances this is the value to use for SOPTYPE when writing VBA or .NET add-ins for SOPEntry.

It is really easy to get caught out and forget this when working between TSQL and different GP forms and just type into the keyboard soptype, and as that field exists no error occurs, but it is the wrong field for that form, copy and paste of snippets of code from other forms can cause this too. I should know better but I was caught out by it myself, after this question, we checked and there were a couple of instances of the wrong SOPType field being used in the code that we were reviewing. Due to the way the code was written it had not caused any odd behaviour yet (“is soptype in (2,3)?”) but would rear its head later in use or testing.


Patrick added the following in a comment against this post, I'm putting the comment in the main post here, for those that don't scroll down to the comments...

"To make it easier, some helpful MS dev has created a field that does this for you (also helps binding the field no doubt in dex)."

I'll take credit for this one.

Actually it doesn't help with any type of "binding". The SOPTypeDatabase field isn't used in SOP Entry at all (other than being set) nor is it bound in any data field in any table.

I was in GP Dev support at the time when the new feature for advanced distribution was released that added the Fulfillment Order type.

I immediately saw that that this would cause issues for VBA developers (there wasn't vstools at the time - think this was 7.0 or 8.0 GP) because of the sort order on the UI vs the database value. From VBA we cannot

  1. Tell the UI value of the item selected
  2. Tell how many items were in the list.
  3. Can't tell if Advanced Distribution is enabled (so we know to expect 5 or 6 items in the list)

In short, no way possible that I could think of (without using continuum/dexterity in VBA) to know what value is actually selected other then "3" so we don't know if Invoice or Fulfillment Order.

So I told development that I needed any of the above feature or a field on the UI itself that is the true and correct value the same as before this new feature was added.

Being development, they took the easiest path and put the SOPTypeDatabase value on the UI so VBA could read it.

Personally I would have rather had #1 above since it has applications everywhere in GP - but at least it got us over this particular issue.

From a Dexterity integrating application standpoint, it isn't necessary because there are functions in GP that developers can call.

So just thought I'd throw the back story out there and (not very modestly) my role in this.

Patrick Roth
eOne Solutions
formerly Great Plains/Microsoft GP Developer Support