Storing Additional Data in SOP Transactions

By Steve Endow

I am currently working on an integration between a client's operational system and Dynamics GP.  The operational system is a custom, industry-specific solution that records all of the client's transactions, helps them manage their unique product offering, and allows them to comply with specific regulations that vary by state.  But that system is not an accounting system and was not designed to invoice customers or generate customer statements.

So the client would like to import transactions into Dynamics GP to manage the financial aspects of the transactions, including cash receipts, invoices, statements, and financial reporting.  Great, no problem, it's a very common situation.

The operational system has some industry specific information that would be helpful to have in GP for tracking, reporting, and analysis purposes.  But Dynamics GP does not have fields designed to handle those values.  So when the transactions are imported as a Sales Order Invoice, what are the options for storing this additional data?

It's a fairly common situation, so I thought I would run down some of the common options and point out some of the pros and cons.

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

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

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 my project, 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.

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.

 
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.

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.

UPDATE:  Mark Polino mentions in his comment below that one limitation of Extender is that by default, the Extender fields will not move with the transaction as it changes document types.  So Extender fields that are linked to an Order number will not be copied or transferred to the different Invoice number for that transaction.    Christina notes that a one potential workaround to this issue is to link the Extender records to the SOP Master number SOP Number and Line Item Sequence, which does transfer with the transaction across different document types.

UPDATE 2:  Christina corrected me and provided the KB article that she was thinking of.  I corrected the field name referenced in the update above.  KB Article 932024 discusses this issue in one context.  The article seems to imply that the Extender information must be associated with the SOP lines in order to link to the Line Item Sequence.   So this workaround would only seem to work at the line item level, and not at the SOP header level.


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.

If I missed any fields, let me know!

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com