Storing Additional Data in Microsoft Dynamics GP SOP Transactions

Steve EndowMicrosoft MVP
CERTIFIED EXPERT
I'm a Microsoft MVP who works with Dynamics GP, Dynamics 365 Business Central, SQL Server, .NET, Power Automate, Power Apps, and Azure
Published:
If you need to import sales transactions from another system into Microsoft Dynamics GP as Sales Order Invoices, you may need to store some additional data related to the customer, the transaction, or the line items.  Even if your company is manually entering transactions in Dynamics GP, you may want to populate a few extra fields in the Dynamics GP Sales Order Invoice.

What are the options for storing additional data elements that are not natively supported by Dynamics GP SOP fields?

Here are some of the common options and some pros and cons.

First, let's start with the additional transaction, or header level fields that are available.

Additional SOP Transaction Fields
Document Number:  While Dynamics GP normally defaults the next document number, you do have the option of overriding the number.  In my case, I have imported a transaction ID into the SOP Document Number field that will allow the customer to trace transactions between both systems by using the same number.  Just make sure that the GP document numbering uses a different sequence or prefix so that the numbers never collide.

SOP GL Reference
GL Reference:  If you click on the blue arrow next to the Document Number field, you can specify a GL reference for the transaction that can flow through to the GL.  While this may not necessarily be a great place to store "extra" data, it's an option for folks who would like to trace and reconcile transactions in their GL.

Batch ID:  If the batch of transactions is related in some way that relate to the operational system, the Batch ID can be used to provide some additional meaning.  In one of my imports, an ID number is used by the operational system to group hundreds of transactions that relate to a single batch, so the GP Batch ID was a natural fit.

Customer PO Number:  This 20 character field is a natural place to store an additional field value.  If PO numbers are not used by customers, this is a convenient place to store additional transaction level data.  It isn't included in most inquiry windows, but it can be included on a SOP Transaction SmartList.  Although the PO number is not readily included in GP search or inquiry windows, this field is valuable because it is stored in the SOP10100 transaction table.  If you want to access it, you don't need to join against another table.

Sales Comment
Transaction Sales Comment:  This is an interesting field for GP.  The Comment field can store up to 500 characters, so it's a nice long text field that doesn't have some of the hassles associated with a GP Note field.  But what is interesting is how GP stores the data in the SOP10106 table.  The full text is stored in the CMMTEXT field, but the data is also simultaneously split into the 50 character COMMENT_1, COMMENT_2, COMMENT_3, and COMMENT_4 fields.  And if you add line-breaks to your text so that you have four values on four different lines, you can intentionally store four different values in the COMMENT fields.

Sales Comment Fields
SOP User Defined
User-Defined:  The Sales User Defined Fields offer a relative gold mine of options for additional Transaction level fields.  There are three list fields, where a user can choose from a pre-defined list of 20 character values, there are two date fields, and there are five 20 character text fields.  These fields are also stored in the SOP10106 table along with the Comment field data.  Last, there is the list of Tracking Numbers.  This is a scrolling window allowing you to enter multiple 40 character values which are then stored in the SOP10107 table.  In theory, you could store dozens of additional values here, but the caveat is that there is no way to differentiate the values.  So if you stored 10 different fields as tracking numbers, it would be tricky retrieving field 7, or 3.  You could probably prefix the values by storing "3: Northwest" and "7: Platinum Member", but that would then require that anything querying those values would need to have logic for interpreting the field numbers or prefixes.


Transaction Note:  Last but not least is the Transaction Note.  While Notes have a pretty large capacity of 32,000 characters, and are great for storing long descriptive text that doesn't require parsing, they are a bit of a hassle to use for storing field data.  The transaction note text is stored in the SY03900 table based on the transaction's Note Index.  Storing 2 or 3 different values in a note might work if users open the Note window and review the values, but if you ever need to query or report on the data, you would have to try and parse the note text, which isn't ideal.  The SY03900 TXTFIELD field data type is Text, which means that you can't just query the field value--you have to use a CAST or other technique to retrieve the text data.  The text data type is going to eventually be dropped from SQL Server, so these will eventually become easier to access when they are transitioned to varchar(max).

So those are most (all?) of the fields that can be used or borrowed at the transaction level, which gives you a pretty good set of options for storing additional data.

However, at the Sales Item level, there are far fewer options.

Line Sales Comment:  The Line Sales Comment is stored in the SOP10202 table, and has essentially the same structure and benefits as the Transaction Sales Comment data in SOP10106.  This is the primary field for storing additional information at the line level.

Salesperson ID and Territory:  If you aren't using these two fields for salesperson or territory reporting, it might be possible to use them to record distinct pre-defined values.  Definitely not ideal, but an option for a few situations.

Unlike Purchase Orders, there isn't even a Note field at the SOP Line Item level, so your options are quite limited.  Most of the other fields on the Item Detail Entry window play some integral role in the transaction, so they don't work well for storing additional data.


If these fields don't meet your needs for storing additional data, then there is always Extender.  Extender offers tremendous flexibility in terms of adding numerous additional fields with different data types that allow you to neatly organize the data without having to remember that the Customer PO Number is actually some other value.  The downside to Extender is that the data is stored in separate tables, and is stored in different tables based on data type.  So a text field is stored in the EXT00101 table, dates are stored in EXT00102, and numeric values are stored in EXT00103.  And then there is a header record in EXT00100.  So it takes extra work if you are importing data into GP, as you will have to create those records, and it takes extra effort to retrieve the data for queries or reports.  But, the plus side is that it essentially removes all of the limitations of Dynamics GP, allowing you to store all of the data that you want, the way you want.

So if you need to store additional data with your SOP Transactions, whether it is part of your standard data entry processes, or for importing data from another system, you do have several options depending on your requirements.

An earlier version of this article was originally published on the author's blog, Dynamics GP Land.
2
3,311 Views
Steve EndowMicrosoft MVP
CERTIFIED EXPERT
I'm a Microsoft MVP who works with Dynamics GP, Dynamics 365 Business Central, SQL Server, .NET, Power Automate, Power Apps, and Azure

Comments (1)

Thank you , this has helped me a lot.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.