Two and a half years ago, a client asked me to help them export purchase orders out of Dynamics GP. They work with third-party warehouses and logistics services that receive their inventory and also fulfill customer orders. Okay, no problem, a PO export. Famous last words, right?
Well, to this day, I am still updating and refining the PO export to properly send data to the third party warehouses and trading partners. I've learned that exporting purchase orders from Dynamics GP can be a very tricky proposition.
There are three primary reasons why exporting a PO is difficult.
First, Purchase Orders in Dynamics GP are dynamic documents. They are not posted transactions like a journal entry or payables invoice. They can be created, edited, have lines added, quantities changed, quantities cancelled, lines received, lines closed, and lines cancelled. And they can have different statuses, such as new, approved, released, and change order. There are a lot of ways that a PO can be changed. And since they can change constantly, they are a moving target. You can't just export a PO right after it is entered and consider your work done. Sure, some companies may have very simple PO procedures, but my client is not one of those companies. They have hundreds of lines on their POs, and those POs constantly change in nearly every way imaginable.
Second, Dynamics GP doesn't have a means to tell you exactly what was changed on a purchase order--or any transaction or record, for that matter. The eConnect Requester can detect that a PO was inserted, edited, or deleted, but that's about it. It doesn't tell you if a comment was changed, a line was added, or a line had a partial quantity cancelled. It doesn't tell you if the PO went from new to released. It's up to you to figure that out. Similarly, it's up to you to figure out which POs you have already exported. And which lines. If one line on a 300 line purchase order is updated, you probably only want to send the single updated line, not all 300 lines.
Third, different systems have different ways of handling purchase orders. If you are exporting POs from Dynamics GP and sending them to another company, their system will very likely have different rules and requirements than Dynamics GP. You will likely need to do some things with your PO data to accommodate the external system. If the quantity ordered on a line is changed from 15 to 20, do you export a "change" with a quantity of 20? Or do you send just the difference--a change with a quantity of 5? If you have a PO line with quantity 20, and cancel a quantity of 5, do you send a cancellation? Or a change? And what quantity do you send? 5? -5? The difference of 20 - 5 = 15? And if you have already received 10, do you net out those receipts? So 20 - 10 - 5 = 5? It all depends on the receiving system. And if you are sending PO data to multiple external entities, you can bet that they will each have slightly different requirements. (Trust me!)
And don't forget that there's more to it than just exporting the data. You need to pick a file format. TXT? CSV? XML? XLS? Windows or Unix line terminators? Yes to all of them! What file naming conventions do you use for each trading partner? And what about actually sending the file automatically? Drop it on your FTP site? Upload it to their FTP site? How about their Secure FTP site? And even better, a secure FTP site that uses public keys for a login instead of a password? And then there is good old e-mail for those trading partners that have manual processes. Naturally, some will want the PO data sent to them hourly. While others will only want it once per day. And what if a company says they didn't get the data from last Thursday? Yup, you will need to be able to re-export or re-send the POs that were added or modified last Thursday.
Get the drift?
It's much trickier than you might think...
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.
http://www.precipioservices.com
Thursday, August 30, 2012
Friday, August 24, 2012
Two bugs in GP 2010 SP1 that made an upgrade worthwhile
In my last post, I mentioned that I was helping a client upgrade to GP 2010 SP3 because SP1 has an eConnect bug.
The GP 2010 SP1 eConnect bug, described here by David Musgrave, effectively prevents eConnect from importing standard GL journal entries. The only way to resolve the issue is to install a newer hotfix or service pack so that the stored procedures are updated.
While working with the client to test the eConnect journal entry import, I happened to notice that logging into GP took an eternity. After selecting a company, it would take several minutes for GP to fully load.
The client indicated that the slow performance was normal for them, and that all users had the same experience on their Terminal Server and even their SQL Server. There are a few possible causes for GP to take a long time to load or login, but we went through the usual suspects and were unable to pinpoint the cause.
One of our suspects was a known issue with GP 2010 SP1, discussed here and here. But after clearing the SY07110 table and disabling all elements of the GP home page, the client was still having the slow login issue.
Since we had to install SP3 to fix the eConnect issue, we crossed our fingers in the hope that the service pack resolved the performance issue.
It only took 90 minutes for the entire upgrade of 5 company databases, including backups. After the upgrade, GP opened in seconds and the eConnect GL import worked just fine.
The GP users were absolutely thrilled that they no longer had to wait several minutes for GP to load or switch companies. And they were happy to be able to automatically import their journal entries with the eConnect integration.
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.
http://www.precipioservices.com
The GP 2010 SP1 eConnect bug, described here by David Musgrave, effectively prevents eConnect from importing standard GL journal entries. The only way to resolve the issue is to install a newer hotfix or service pack so that the stored procedures are updated.
While working with the client to test the eConnect journal entry import, I happened to notice that logging into GP took an eternity. After selecting a company, it would take several minutes for GP to fully load.
The client indicated that the slow performance was normal for them, and that all users had the same experience on their Terminal Server and even their SQL Server. There are a few possible causes for GP to take a long time to load or login, but we went through the usual suspects and were unable to pinpoint the cause.
One of our suspects was a known issue with GP 2010 SP1, discussed here and here. But after clearing the SY07110 table and disabling all elements of the GP home page, the client was still having the slow login issue.
Since we had to install SP3 to fix the eConnect issue, we crossed our fingers in the hope that the service pack resolved the performance issue.
It only took 90 minutes for the entire upgrade of 5 company databases, including backups. After the upgrade, GP opened in seconds and the eConnect GL import worked just fine.
The GP users were absolutely thrilled that they no longer had to wait several minutes for GP to load or switch companies. And they were happy to be able to automatically import their journal entries with the eConnect integration.
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.
http://www.precipioservices.com
Thursday, August 23, 2012
Life as a Consultant: My Brain Hurts
When I woke up this morning, I was pondering how to produce a custom Statement report for a client. They want a statement that shows outstanding receivables balances, but also shows original amounts from a SOP invoice, such as gross amount, markdown amount, and net amount. And they also want to see the total amount of payments applied to each invoice. And they want to see any 'adjustments' made to the SOP invoice by means of other adjusting invoices or returns. Oh, the outstanding balances also need to be in aging columns on the statement. And did I mention that they don't actually want transaction level detail? The data needs to be summarized by a special transaction grouping, where multiple invoices may be included in the group. Oh, and the data on the report needs to also be provided to the customer as a CSV file. Because the report and CSV file need to be automatically e-mailed to the customer.
It makes business sense, but it's a mind bending exercise trying to figure out how to get all of that information onto a single report. There is a slightly complex custom SQL view. Then there is a Crystal Report with some wacky formulas and running totals and groups. Then I have to automatically generate the CSV files. Then there is Liaison Messenger EDD for distributing the reports and files via e-mail. It's a handful.
After working on that for a while, I checked on the status of a test EFT transaction that a client sent from GP. Thankfully the payment went through okay, despite the several bugs in the GP 2010 CCD+ ACH file formats.
Then at 10am I deployed some changes to a custom PO Export application for another customer. The trading partner that is receiving the PO files has some interesting limitations with their custom system, so the client and I are having to reverse engineer their system behavior to figure out how to send new POs, changes to PO lines, partial line quantity cancellations, and then full line cancellations. It looks like we may have to send PO line quantity updates net of any receipts that have occurred. So if they originally had quantity of 20 on the PO, changed the quantity to 15, but have already received 9, but then cancel the line, I may need to send a cancellation for quantity of 6. Make sense? Fun stuff.
Then back to the custom Statement for 90 minutes.
Then at noon I had a call with another client that is having two GP issues. I developed a moderately complex custom order import application that automatically creates SOP orders and purchase orders for inventory items, non-inventory items, and drop ship items, all simultaneously. It seems that SOP/POP linking doesn't work properly with these imported SOP orders and purchase orders in GP 2010 for some reason, so there may be an issue with eConnect 2010. To help save them time, I'm going to add the ability to automatically link certain SOP lines with the PO lines. Unfortunately, eConnect does not allow you to link a SOP line item with an existing PO or PO line, so that has to be developed from scratch. And they are also using another small customization that I wrote for them that isn't playing well with their Nodus CCA credit card processing module, so I need to make some changes there as well.
Then another call to discuss some new requirements for the PO Export I just updated.
Then back to the custom Statement report.
And next up, I have a call with another client to assist with a GP 2010 SP3 upgrade, since an eConnect GL JE import that I developed is getting an error due to an SP1 bug. So what should have been a very simple integration deployment has turned into a GP SP upgrade before we can even resume testing of the integration.
After that, I'll be back on the custom Statement report, even though I still have to work on a custom eConnect Project Accounting Misc Log import for another client.
And then there is the occasional Dynamics GP Land blog post that I need to think about and write.
This is all just an example to point out a few themes of consulting.
1. The breadth and depth of knowledge required to be a competent, full service Dynamics GP consultant is staggering. I think we take it for granted, but really, when you think about everything from debits and credits to SQL queries, to business process, to accounting controls, to all of the different modules, to product support, to project management, it requires a pretty huge pile of knowledge and skills to take care of your customers. And any one consultant typically only handles certain realms, such as application consultant vs. technical consultant. I used to feel a little self conscious about our billing rates, and definitely understand if a client gasps or growls at the hourly fee, but given the knowledge we're being asked to provide at a moment's notice (and the constant investment that requires), I don't think we're being too unreasonable.
2. Task switching is very expensive. I read a news blurb years ago about a study that tested people's ability to handle interruptions when they were performing a task that required focus and concentration. I believe that it found that on average, people required about 15 minutes to recover from an interruption and get back to the task.
UPDATE: It was a NYTimes.com article from 2008, titled "Fighting a War Against Distraction". And my recollection was incorrect--apparently it can take up to 30 minutes to recover from a distraction. Great article, with many other points about focus and distraction in the workplace.
I can definitely relate, as I often have a hard time getting back into that custom Statement report, remembering where I was at and what I needed to do next. And just the mental process of switching tasks feels like I have to clear my 'memory buffer' from the prior task and fill it back up with the new task at hand.
Anyway, it's a busy day and my GP upgrade call is starting, so that's all my brain can handle for now.
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.
http://www.precipioservices.com
It makes business sense, but it's a mind bending exercise trying to figure out how to get all of that information onto a single report. There is a slightly complex custom SQL view. Then there is a Crystal Report with some wacky formulas and running totals and groups. Then I have to automatically generate the CSV files. Then there is Liaison Messenger EDD for distributing the reports and files via e-mail. It's a handful.
After working on that for a while, I checked on the status of a test EFT transaction that a client sent from GP. Thankfully the payment went through okay, despite the several bugs in the GP 2010 CCD+ ACH file formats.
Then at 10am I deployed some changes to a custom PO Export application for another customer. The trading partner that is receiving the PO files has some interesting limitations with their custom system, so the client and I are having to reverse engineer their system behavior to figure out how to send new POs, changes to PO lines, partial line quantity cancellations, and then full line cancellations. It looks like we may have to send PO line quantity updates net of any receipts that have occurred. So if they originally had quantity of 20 on the PO, changed the quantity to 15, but have already received 9, but then cancel the line, I may need to send a cancellation for quantity of 6. Make sense? Fun stuff.
Then back to the custom Statement for 90 minutes.
Then at noon I had a call with another client that is having two GP issues. I developed a moderately complex custom order import application that automatically creates SOP orders and purchase orders for inventory items, non-inventory items, and drop ship items, all simultaneously. It seems that SOP/POP linking doesn't work properly with these imported SOP orders and purchase orders in GP 2010 for some reason, so there may be an issue with eConnect 2010. To help save them time, I'm going to add the ability to automatically link certain SOP lines with the PO lines. Unfortunately, eConnect does not allow you to link a SOP line item with an existing PO or PO line, so that has to be developed from scratch. And they are also using another small customization that I wrote for them that isn't playing well with their Nodus CCA credit card processing module, so I need to make some changes there as well.
Then another call to discuss some new requirements for the PO Export I just updated.
Then back to the custom Statement report.
And next up, I have a call with another client to assist with a GP 2010 SP3 upgrade, since an eConnect GL JE import that I developed is getting an error due to an SP1 bug. So what should have been a very simple integration deployment has turned into a GP SP upgrade before we can even resume testing of the integration.
After that, I'll be back on the custom Statement report, even though I still have to work on a custom eConnect Project Accounting Misc Log import for another client.
And then there is the occasional Dynamics GP Land blog post that I need to think about and write.
This is all just an example to point out a few themes of consulting.
1. The breadth and depth of knowledge required to be a competent, full service Dynamics GP consultant is staggering. I think we take it for granted, but really, when you think about everything from debits and credits to SQL queries, to business process, to accounting controls, to all of the different modules, to product support, to project management, it requires a pretty huge pile of knowledge and skills to take care of your customers. And any one consultant typically only handles certain realms, such as application consultant vs. technical consultant. I used to feel a little self conscious about our billing rates, and definitely understand if a client gasps or growls at the hourly fee, but given the knowledge we're being asked to provide at a moment's notice (and the constant investment that requires), I don't think we're being too unreasonable.
2. Task switching is very expensive. I read a news blurb years ago about a study that tested people's ability to handle interruptions when they were performing a task that required focus and concentration. I believe that it found that on average, people required about 15 minutes to recover from an interruption and get back to the task.
UPDATE: It was a NYTimes.com article from 2008, titled "Fighting a War Against Distraction". And my recollection was incorrect--apparently it can take up to 30 minutes to recover from a distraction. Great article, with many other points about focus and distraction in the workplace.
I can definitely relate, as I often have a hard time getting back into that custom Statement report, remembering where I was at and what I needed to do next. And just the mental process of switching tasks feels like I have to clear my 'memory buffer' from the prior task and fill it back up with the new task at hand.
Anyway, it's a busy day and my GP upgrade call is starting, so that's all my brain can handle for now.
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.
http://www.precipioservices.com
Thursday, August 9, 2012
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 thata 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!
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
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.