Determine SOP Type in SOP Entry using VBA or Visual Studio Tools

  • Article

Patrick Roth - Click for blog homepageI think the most customized window in VBA must be the SOP Entry window. 

If the customization is not saving custom data for the header/line items, then it is adding custom logic based on document type and any number of other factors.

In all of this - especially saving custom data to a parallel table - the SOP Number is important as it identifies the document.  And, of course, to be unique your primary key would also include the SOP Type field - just like the SOP10100 table does in Dynamics.

Getting the SOP Number is easy - it is the string value from the window. 

The SOP Type value is a bit more difficult with the introduction of Fulfillment Order SOP Type.  This all would have been a lot easier if we would have just added this new type to the end of the SOP Type list.  But I guess I can see it would go well with the Order type in Dynamics. So Fulfillment Order was added into the middle of the list - complicating things a bit when trying to determine if SOP Type = 3 (on the window) is an Invoice or a Fulfillment Order.

While getting the SOP Type is easy from Dexterity using the SOPTypeToInt function, that function cannot be called from Visual Studio Tools due to an anonymous field being passed to it and certainly not used from VBA since it natively cannot call Dexterity functions.

So where does that leave our enterprising VBA/VSTools developer?  How do those developers get the true SOP Type value?

By referencing a hidden field added specifically for non-Dexterity developers.

Since at least 9.0, an invisible field called 'SOP Type Database' was added to the SOP Entry window.  It is in the 2nd column of fields, 16 fields down off the bottom of the window.  This field contains the correct "SOP Type" of the current document, the value that matches the SOP Type field stored in the SOP10100 table regardless of what the value SOP Type field in the SOP Entry would show.  Just exactly what we need.

Using the field from Visual Studio Tools is easy - just reference the field as below:

C# Code Snippet Example

 int sopType = 0;
sopType = SopEntry.SopEntry.SopTypeDatabase.Value;

From VBA it is a little more difficult because the field is off the window and invisible so not easily added to VBA without a bit of work.

To get at it with VBA, you must:

  1. Using Modifier, locate the field and move it onto the visible portion of the SOP Entry window.
  2. Make the field Visible property set to true.
  3. Exit Modifier and save your changes.
  4. Grant yourself access to the modified form (if necessary - you might already have that in your customization)
  5. Open the window - this field should be visible.
  6. Add the field to VBA.
  7. Go back into Modifier and move the field back to it's original location and set the field Visible property back to false.
  8. Exit Modifier and save your changes.

Now in VBA, instead of referencing the SOP Type field directly, reference this new field to get the actual value your code requires to properly save your parallel data.

VBA Code Snippet Example

Dim realSOPType as Integer
realSOPType = SOPTypeDatabase

Hope this help,

Patrick
Developer Support

// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)