eConnect for Microsoft Dynamics GP is fantastic. It's easy for a .NET developer to use, it's fairly comprehensive, it's fairly well documented, and it's relatively fast. Except for the occasional missing field, unsupported transaction or bug, I love working with it.
Unfortunately, I have found that among non-technical folks, there is often a misconception about what it takes to develop a complete eConnect custom integration. I am guessing that they think that eConnect should be no different than Integration Manager, where in a few hours or less, you should be able to produce a fancy integration.
It's rarely that simple. While trying to explain to a GP partner why a POP + SOP + Project Accounting integration would really take 40 hours to develop, I came up with this way of describing it: eConnect is the easy part. That's the 10% of the equation. It's a standard API with good documentation. It's a known quantity. The other 90% is dealing with the client's source data file, developing business logic for processing the data, and handling exceptions in that data. And don't forget testing, debugging, minor changes, installation, and documentation.
Sure, I have had situations where customers provided a very simple and clean data file that went straight into GP. No data transformations, no lookups, no exceptions or errors--a perfect integration. Those are simple and easy. But those situations are generally rare--in part because such integrations can often be done with Integration Manager or even a table import (not that I'd recommend a table import...).
The real power and some of the greatest benefits of custom eConnect integrations comes with complex integrations. And with such integrations, it isn't eConnect that provides the benefit as much all of the extra logic that can be included with custom integration. For example, importing a customer with eConnect is essentially always the same. Customer object, address object, populate the properties, and send it off to eConnect. Simple.
But when the customer data is in multiple files, or different data formats, or the address isn't parsed at all or is incomplete, or the data needs transformation, or has exceptions that need to be handled, that is when additional custom .NET code can come into play to make the integration much more valuable than a generic data import.
A timely example: I had a phone call today with a client that receives over 600,000 cash receipts every month, a number that is rapidly growing. What they actually receive is a single giant payment from an external trading partner, and that trading partner also provides them with a 150MB text file containing the list of every customer payment. Not only do these cash receipts need to be entered, but they also need to be applied to invoices--so think of it as over 1,000,000 transactions. Every month. They would like to automate this process to eliminate the 3 full days of work that are required to enter and apply the cash receipts. In concept that doesn't sound so bad, and eConnect can obviously handle that.
So I then ask if there are any exceptions in the data that are handled manually, or problems that require attention before the cash receipt can be entered or applied. "No, not really" the customer answered. Hmmm, that seems unlikely.
About 30 seconds later, I ask them which column in the data file is the Dynamics GP customer ID. It turns out that the customer ID is not in the data file--only the customer name. Sometimes the customer name is the same as GP, sometimes it's similar, and sometimes it is completely different. The accounting staff manually looks up each customer name in GP to determine which GP customer sent the payment. I don't know about you, but I would call that a giant 600,000 line exception. Okay, so we can setup an easy method to map the customer name in the data file to the GP customers. Not a problem, but note that this has nothing to do with eConnect.
I asked about the additional worksheet in Excel where some of the data had been separated. "Oh, that's a different type of cash receipt, we handle those separately." So now we have regular cash receipts, and special cash receipts. Another exception, to be handled well before eConnect gets involved.
And what about the payment applications? Any issues with those? "No, those are pretty standard." But, the first cash receipt we look at in GP is not applied to an invoice, because there is no invoice available for that customer. Another exception. And then we find an overpayment. Another exception. And then an underpayment. Another exception. After discussing overpayments and underpayments, they explain that they want debit and credit memos automatically created, and then applied to the customer's credit or debit balance. But only if the difference is within a certain threshold, such as under $100. If the difference is greater than $100, it should appear on an exception report requiring a review. Makes total sense, and mimics their manual processes. But eConnect is still only peripheral at this point.
The requirements and exception list goes on, and I now have a good idea of what needs to be done. And you know what? The LEAST of my concerns is eConnect. It's dealing with the 150MB data file and the thousands of exceptions that the integration is going to need to handle during the import.
And when an exception or error occurs, I'll need to create an exception file so that they can deal with just the 1,000 records with errors instead of swimming through the massive 600,000 line data file. And don't forget reporting--they will need a report or log listing how the cash receipts were imported and the invoices to which they were applied.
It's a fantastic project, and I can't wait to help them turn a 3 day ordeal into a simple 15 minute process. But roughly 90% of my work will be wrestling with the source data, while only 10% will be working directly with eConnect.
Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified 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
Friday, May 20, 2011
Wednesday, May 18, 2011
Dynamics GP .NET eConnect Developer Wanted
5/18/2011
I hear and read news reports saying that the US economy is still in bad shape, but I am having a hard time reconciling that news with the continuous projects that I've been frantically working on for the last 6 months. And when I've reached out to a few GP .NET developers that I know to see if they can help with a few projects, they too have typically been fully booked, working late nights just to keep up.
So, if you are a Dynamics GP .NET eConnect and VS Tools developer, or if you know of one, I'd love to chat.
I'm looking to have additional resources available for those times when 5 clients suddenly want work done immediately, or when I'm swamped and a new project pops up out of the blue.
Ideally, I'm looking for developers located in North America who are independent contractors.
He or she should be proficient with Visual Studio, C# and VB, as well as eConnect, Visual Studio Tools for GP, and SQL Server. Strong understanding of Dynamics GP functionality, distributions, GL accounts, and debits and credits is desirable as well.
I can be reached at steveendow@gmail.com.
I hear and read news reports saying that the US economy is still in bad shape, but I am having a hard time reconciling that news with the continuous projects that I've been frantically working on for the last 6 months. And when I've reached out to a few GP .NET developers that I know to see if they can help with a few projects, they too have typically been fully booked, working late nights just to keep up.
So, if you are a Dynamics GP .NET eConnect and VS Tools developer, or if you know of one, I'd love to chat.
I'm looking to have additional resources available for those times when 5 clients suddenly want work done immediately, or when I'm swamped and a new project pops up out of the blue.
Ideally, I'm looking for developers located in North America who are independent contractors.
He or she should be proficient with Visual Studio, C# and VB, as well as eConnect, Visual Studio Tools for GP, and SQL Server. Strong understanding of Dynamics GP functionality, distributions, GL accounts, and debits and credits is desirable as well.
I can be reached at steveendow@gmail.com.
And a One, And a Two, SQL Report Security
One of the most valuable (and yet underutilized) benefits of Microsoft Dynamics GP is the standard SQL Reporting Services (SSRS) reports that are available. These reports are valuable for a number of reasons including:
• Delivery via web to non-GP users
• Easy export capability to Excel, Word
• Ability to modify existing reports and access additional information
• Ad-hoc reporting capabilities using standard Report Models
• Subscription capabilities within SQL Reporting Services (delivers the report to your inbox!)
I know I have other posts dedicated to my love of SSRS, so I will spare you another love letter for now. But I do want to chat a bit about SQL Reporting Services security.
It seems like Dynamics GP users fall in to three main categories when it comes to security:
1. Everyone sees everything- Most common when the finance department is small and there is not a lot of delineation of duties. So often in GP, all users are POWERUSERS.
2. Limited access for convenience only- Maybe some security is defined, but only to help simplify the user experience. From a reporting perspective, there are no concerns about “protecting” information. This is usually the case when there is a delineation of duties, but due to overlap and backups, everyone knows everything anyway.
3. Limited access out of necessity- Security is defined not only to simplify the user experience, but also to protect data from unauthorized access. This is very common when payroll and human resources are included in the system, or in larger organizations with multiple offices/companies/entities to consider.
In the first two scenarios above, SQL Reporting security is not much of a concern. But in the third scenario, we would really need to be measured in our approach to ensure data is appropriately secured. I think, sometimes, it is assumed that customers fall in to #1 or #2 because of our understanding of their GP user base. But we need to be careful and consider the user base for SQL Reporting, and ensure that the customer understands its security model as well.
SQL Reporting security can be broadly categorized in to three areas:
• Site Security
• Item Security
• Data Security
Site Security is defined in Report Manager (generally http://servername/reports) using the Site Settings link. This is where you define who has permissions to manage and administer site level settings. Users do not have to be defined in Site Security to access and use SQL Reports. They only need this level of access to allow them to manage aspects of the site like system properties and schedules.
Item Security is also defined in Report Manager. This controls access to the folders and reports on the site. This can be accessed by choosing Properties or Security from the drop down list that appears when you click on an item. Or by using the Folder Settings button. By default, when an Windows user or group is assigned to an item in Report Manager, all items below inherit the access. For example, if a user is granted access to a folder, the same user also has access to the reports in the folder. This parent-level security inheritance can be broken by item by accessing security for the item. For example, if the user shouldn’t have access to one report within the folder, you can access security for that report and delete the user (therefore “breaking” the parent-level security inheritance).
Okay, so here is where it gets confusing :) Item level security controls access to the report. HOWEVER, being able to get to the report is NOT the same as being able to generate the report. This is where data security comes in to play. This can take on a lot of angles, depending on how you have configured the data source for your report. But let’s assume that you have configured it to use Windows credentials (that are not stored in the data source).
Data Security is defined in SQL Server Management Studio. This security controls the user’s ability to access the tables, stored procedures, and views necessary to generate reports. Keep in mind that although we are defining security for use with SQL Reporting Services, the data level security you define also allows users to access the data through Excel, Crystal Reports, Access, or another ODBC data source. For this reason, it is important that Data Security must be as restrictive as your requirements necessitate to prevent unauthorized access to data. Simply restricting access at the Item Level would only limit the user’s ability to access the data using that particular medium. If Data Security gives them access to the underlying data, they could access it with Excel, Access, etc even if they can’t access the SQL Report itself.
To configure Data Security in SQL Server Management Studio, the user must first be set up under Security>>Logins using their Windows login. Then, using the User Mapping tab in User Properties, you must map the user to the appropriate company databases and the corresponding database roles (to grant access to the necessary tables, views, and stored procedures to generate the reports).
Fortunately, Microsoft Dynamics GP comes with a number of predefined roles that begin with “rpt” specifically for use with the SQL reports. These roles have access to specific tables, views, and stored procedures. And the access is restricted so that data cannot be updated, only selected/viewed. Remember, whatever access you give them at the Data level can be used with Excel, Access, etc. So it is important, if you use roles outside of the standard “rpt” roles provided by Dynamics GP , to ensure that the roles do not grant additional permissions that you would not normally allow (e.g., updating tables, accessing additional tables, etc)
When planning security for your SQL reports, it is important to consider all three levels of security. There are resources on CustomerSource to assist with this process including:
Frequently Asked Questions About SQL Reporting Services and Dynamics GP
Download the SQL Reporting Services Administration Guide (last updated for GP 10), which includes additional information on security
Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.
• Delivery via web to non-GP users
• Easy export capability to Excel, Word
• Ability to modify existing reports and access additional information
• Ad-hoc reporting capabilities using standard Report Models
• Subscription capabilities within SQL Reporting Services (delivers the report to your inbox!)
I know I have other posts dedicated to my love of SSRS, so I will spare you another love letter for now. But I do want to chat a bit about SQL Reporting Services security.
It seems like Dynamics GP users fall in to three main categories when it comes to security:
1. Everyone sees everything- Most common when the finance department is small and there is not a lot of delineation of duties. So often in GP, all users are POWERUSERS.
2. Limited access for convenience only- Maybe some security is defined, but only to help simplify the user experience. From a reporting perspective, there are no concerns about “protecting” information. This is usually the case when there is a delineation of duties, but due to overlap and backups, everyone knows everything anyway.
3. Limited access out of necessity- Security is defined not only to simplify the user experience, but also to protect data from unauthorized access. This is very common when payroll and human resources are included in the system, or in larger organizations with multiple offices/companies/entities to consider.
In the first two scenarios above, SQL Reporting security is not much of a concern. But in the third scenario, we would really need to be measured in our approach to ensure data is appropriately secured. I think, sometimes, it is assumed that customers fall in to #1 or #2 because of our understanding of their GP user base. But we need to be careful and consider the user base for SQL Reporting, and ensure that the customer understands its security model as well.
SQL Reporting security can be broadly categorized in to three areas:
• Site Security
• Item Security
• Data Security
Site Security is defined in Report Manager (generally http://servername/reports) using the Site Settings link. This is where you define who has permissions to manage and administer site level settings. Users do not have to be defined in Site Security to access and use SQL Reports. They only need this level of access to allow them to manage aspects of the site like system properties and schedules.
Item Security is also defined in Report Manager. This controls access to the folders and reports on the site. This can be accessed by choosing Properties or Security from the drop down list that appears when you click on an item. Or by using the Folder Settings button. By default, when an Windows user or group is assigned to an item in Report Manager, all items below inherit the access. For example, if a user is granted access to a folder, the same user also has access to the reports in the folder. This parent-level security inheritance can be broken by item by accessing security for the item. For example, if the user shouldn’t have access to one report within the folder, you can access security for that report and delete the user (therefore “breaking” the parent-level security inheritance).
Okay, so here is where it gets confusing :) Item level security controls access to the report. HOWEVER, being able to get to the report is NOT the same as being able to generate the report. This is where data security comes in to play. This can take on a lot of angles, depending on how you have configured the data source for your report. But let’s assume that you have configured it to use Windows credentials (that are not stored in the data source).
Data Security is defined in SQL Server Management Studio. This security controls the user’s ability to access the tables, stored procedures, and views necessary to generate reports. Keep in mind that although we are defining security for use with SQL Reporting Services, the data level security you define also allows users to access the data through Excel, Crystal Reports, Access, or another ODBC data source. For this reason, it is important that Data Security must be as restrictive as your requirements necessitate to prevent unauthorized access to data. Simply restricting access at the Item Level would only limit the user’s ability to access the data using that particular medium. If Data Security gives them access to the underlying data, they could access it with Excel, Access, etc even if they can’t access the SQL Report itself.
To configure Data Security in SQL Server Management Studio, the user must first be set up under Security>>Logins using their Windows login. Then, using the User Mapping tab in User Properties, you must map the user to the appropriate company databases and the corresponding database roles (to grant access to the necessary tables, views, and stored procedures to generate the reports).
Fortunately, Microsoft Dynamics GP comes with a number of predefined roles that begin with “rpt” specifically for use with the SQL reports. These roles have access to specific tables, views, and stored procedures. And the access is restricted so that data cannot be updated, only selected/viewed. Remember, whatever access you give them at the Data level can be used with Excel, Access, etc. So it is important, if you use roles outside of the standard “rpt” roles provided by Dynamics GP , to ensure that the roles do not grant additional permissions that you would not normally allow (e.g., updating tables, accessing additional tables, etc)
When planning security for your SQL reports, it is important to consider all three levels of security. There are resources on CustomerSource to assist with this process including:
Frequently Asked Questions About SQL Reporting Services and Dynamics GP
Download the SQL Reporting Services Administration Guide (last updated for GP 10), which includes additional information on security
Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.
Implementation Trauma AGAIN!!
So, continuing on my theme, I'm back with number two on our list of sins from a few weeks ago. Let's take a bit closer look at how these can sink an implementation from the start. As a refresher, number two on the list for consultants was "Forgetting that customer service is important even during the heat of an implementation" and the companion to this for customers is "Approaching the consulting team as adversaries instead of partners."
Both of these may seem obvious when we see them in writing, but in the stress of an implementation, these items are often forgotten. And when that happens, a positive experience can quickly turn sour. First, let's look at the customer service sin. Consider the following scenario:
Ralph is a good consultant. He has put in long hours on the implementation, and has worked hard to keep it on time and under budget. He has also been diligent in training and documenting every step of the way. So when he receives a call from the client asking the same question, yet again, he lets his frustration slip through. Maybe he lets the voicemail linger a bit longer before returning it. Or maybe he emails back a reference to the documentation or the training, without actually answering the question. Many of you reading this might wonder, so what? Don't customers need to take responsibility for their learning? Sure they do. And, yet, we have to continue to approach customers with good faith throughout the process. This means assuming that they are asking because THEY DON"T KNOW THE ANSWER. Not because they are lazy, or they weren't paying attention, or even that they aren't smart (we all have witnessed this consulting sin-- speaking to users like they aren't intelligent). Yes, there may be a larger issue to be addressed regarding user investment in the process, and yes, users who are too dependent on consultants can create budget issues in the long run. But, from a customer service perspective, we have to handle these issues professionally. Answer the question, and escalate the risk to your project manager or sponsor.
On a side note, whenever I find myself frustrated with these sorts of things-- I remind myself of every consulting horror story I have been told by customers. You know the ones. Where you can't believe that the customer who you love working with was treated so badly. It tends to snap me back in to the customer service mindset pretty quickly.
Another variation on this sin is when the consultant becomes too familiar with the client, and their interaction becomes so casual that it ignores the fact that customer service is involved. This most often manifests itself as "griping". About the project. About the software. About (oh, my) fellow consultants and/or client team members. Yes, you might become friends with clients over the years, but you can never forget that within the confines of a project they are still your customer and deserve the service and discretion that comes along with that. After all, they are paying for that :)
So, let's flip over to the customer side for awhile. The sin of cultivating an adversarial relationship with the consultant is one that also often intensifies towards the end of an implementation. The customer's staff is tired and minor bumps become more significant. Consider the following scenario:
ABC Company has been through a number of software implementations in the past few years. And none of them have gone very well. They like their current consultant personally, but the customer feels that consulting is not about sharing knowledge or customer service- its about billing. A lot. So they want to get what they can from the consultants, but they really don't trust them. And over the course of the project, there have been a few minor bumps that have arisen (new requirements, additional costs, etc) that have just reinforced this perception. So, with the go live in sight, the stress level is high and the customer feels alone in the process with no one to trust.
As a customer, you need to pick consultants you can see yourself trusting when that trust is earned. If you feel "scarred" by prior experiences, examine why the other projects failed. Take those lessons in to your new project, and share those concerns with your consultants and ask them for their help in mitigating them. You want to form a partnership built on this trust, after all you are relying on these people to guide you in configuring your system and business processes. Why would you pick someone you don't trust? And let them in to your business' nerve center? Along those same lines, as I discussed on the consulting side, you need to approach the process with good faith whenever possible. This means being honest about your concerns with the consulting team, assisting them in the identification and management of risks. Acknowledging that all projects encounter changes, but what matters is how these changes are uncovered and how they are addressed. And this can be done best when there is a partnership between the customer and consultants.
As a consultant, the projects that are approached in this way are the most rewarding to work on and the ones that I am most proud of professionally and personally. And, I have to say, most GOOD consultants take a lot of personal pride in their work and in happy customers! So find one of them to work with :)
A bit long winded tonight, but I hope you find the ramblings useful :)
Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.
Both of these may seem obvious when we see them in writing, but in the stress of an implementation, these items are often forgotten. And when that happens, a positive experience can quickly turn sour. First, let's look at the customer service sin. Consider the following scenario:
Ralph is a good consultant. He has put in long hours on the implementation, and has worked hard to keep it on time and under budget. He has also been diligent in training and documenting every step of the way. So when he receives a call from the client asking the same question, yet again, he lets his frustration slip through. Maybe he lets the voicemail linger a bit longer before returning it. Or maybe he emails back a reference to the documentation or the training, without actually answering the question. Many of you reading this might wonder, so what? Don't customers need to take responsibility for their learning? Sure they do. And, yet, we have to continue to approach customers with good faith throughout the process. This means assuming that they are asking because THEY DON"T KNOW THE ANSWER. Not because they are lazy, or they weren't paying attention, or even that they aren't smart (we all have witnessed this consulting sin-- speaking to users like they aren't intelligent). Yes, there may be a larger issue to be addressed regarding user investment in the process, and yes, users who are too dependent on consultants can create budget issues in the long run. But, from a customer service perspective, we have to handle these issues professionally. Answer the question, and escalate the risk to your project manager or sponsor.
On a side note, whenever I find myself frustrated with these sorts of things-- I remind myself of every consulting horror story I have been told by customers. You know the ones. Where you can't believe that the customer who you love working with was treated so badly. It tends to snap me back in to the customer service mindset pretty quickly.
Another variation on this sin is when the consultant becomes too familiar with the client, and their interaction becomes so casual that it ignores the fact that customer service is involved. This most often manifests itself as "griping". About the project. About the software. About (oh, my) fellow consultants and/or client team members. Yes, you might become friends with clients over the years, but you can never forget that within the confines of a project they are still your customer and deserve the service and discretion that comes along with that. After all, they are paying for that :)
So, let's flip over to the customer side for awhile. The sin of cultivating an adversarial relationship with the consultant is one that also often intensifies towards the end of an implementation. The customer's staff is tired and minor bumps become more significant. Consider the following scenario:
ABC Company has been through a number of software implementations in the past few years. And none of them have gone very well. They like their current consultant personally, but the customer feels that consulting is not about sharing knowledge or customer service- its about billing. A lot. So they want to get what they can from the consultants, but they really don't trust them. And over the course of the project, there have been a few minor bumps that have arisen (new requirements, additional costs, etc) that have just reinforced this perception. So, with the go live in sight, the stress level is high and the customer feels alone in the process with no one to trust.
As a customer, you need to pick consultants you can see yourself trusting when that trust is earned. If you feel "scarred" by prior experiences, examine why the other projects failed. Take those lessons in to your new project, and share those concerns with your consultants and ask them for their help in mitigating them. You want to form a partnership built on this trust, after all you are relying on these people to guide you in configuring your system and business processes. Why would you pick someone you don't trust? And let them in to your business' nerve center? Along those same lines, as I discussed on the consulting side, you need to approach the process with good faith whenever possible. This means being honest about your concerns with the consulting team, assisting them in the identification and management of risks. Acknowledging that all projects encounter changes, but what matters is how these changes are uncovered and how they are addressed. And this can be done best when there is a partnership between the customer and consultants.
As a consultant, the projects that are approached in this way are the most rewarding to work on and the ones that I am most proud of professionally and personally. And, I have to say, most GOOD consultants take a lot of personal pride in their work and in happy customers! So find one of them to work with :)
A bit long winded tonight, but I hope you find the ramblings useful :)
Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.
Fun eConnect Bug: Check those field lengths!
Today I deployed an updated version of an AR Invoice eConnect integration. The customer has used the integration previously, and it worked fine, but when we ran it with a new batch of data, the Edit List report indicated that some transactions had missing distributions or incorrect distribution amounts.
Puzzled, I checked the transactions and their distributions in Dynamics GP, and everything looked fine. But when I ran the Edit List again, the same errors were listed--except for any transactions that I had opened and viewed.
Hmmmmm.
So I queried the RM Distribution Work table, RM10101 to see what was going on.
For the one transaction that I had opened and viewed in GP, I saw four distribution lines instead of the two that should be present. I was pretty sure I didn't import four lines, but I wasn't sure where the other two came from.
After looking closely at the four records, I noticed one discrepancy. The DOCNUMBR field value for two of the lines was different than the other two distribution lines. One had the full document number that was imported, but the other had a truncated document number, missing the last 4 characters. And the account indexes for the two sets of distributions were different.
And that is when it dawned on me.
Back in 2008, I wrote this blog post discussing how eConnect handles character values that exceed the length of the eConnect / GP field. Normally, it truncates character data values without any warning or error and proceeds happily, letting you discover that you have some data missing.
Well, in this case, the client had an invalid AR invoice number that was 20 characters long, due to a change in data formatting. eConnect happily imported the invoice, truncating the invoice number to 17 characters.
But, it appears that there is a minor 'bug' in eConnect 10, as the AR distribution imported the full 20 characters into RM10101. No truncation!
So, in the RM10301 Sales Work table, we have a 17 character invoice number, and in RM10101 Distribution Work table, we have a 20 character invoice number. Naturally, when the GP edit list tries to match the invoices to distributions, it can't match the two numbers, so it appears as if the invoice does not have any distributions at all. And when I opened the invoice distribution window, everything looked fine because GP was defaulting distributions based on the customer account settings. And when the distribution window is opened, GP creates new distribution records, but using the customer default accounts.
In short, the distributions that were imported with the full 20 character invoice number are "orphaned". They are not associated with any invoice, and even if you delete the invoices, the distributions will not be deleted. They will have to be cleaned out manually or via Check Links.
So, the lesson here is that you shouldn't rely on eConnect to manage the length of your character field values. Ideally, you should include field length checks in your integration validation code to detect data values that may exceed a GP field length.
Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified 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
Puzzled, I checked the transactions and their distributions in Dynamics GP, and everything looked fine. But when I ran the Edit List again, the same errors were listed--except for any transactions that I had opened and viewed.
Hmmmmm.
So I queried the RM Distribution Work table, RM10101 to see what was going on.
For the one transaction that I had opened and viewed in GP, I saw four distribution lines instead of the two that should be present. I was pretty sure I didn't import four lines, but I wasn't sure where the other two came from.
After looking closely at the four records, I noticed one discrepancy. The DOCNUMBR field value for two of the lines was different than the other two distribution lines. One had the full document number that was imported, but the other had a truncated document number, missing the last 4 characters. And the account indexes for the two sets of distributions were different.
And that is when it dawned on me.
Back in 2008, I wrote this blog post discussing how eConnect handles character values that exceed the length of the eConnect / GP field. Normally, it truncates character data values without any warning or error and proceeds happily, letting you discover that you have some data missing.
Well, in this case, the client had an invalid AR invoice number that was 20 characters long, due to a change in data formatting. eConnect happily imported the invoice, truncating the invoice number to 17 characters.
But, it appears that there is a minor 'bug' in eConnect 10, as the AR distribution imported the full 20 characters into RM10101. No truncation!
So, in the RM10301 Sales Work table, we have a 17 character invoice number, and in RM10101 Distribution Work table, we have a 20 character invoice number. Naturally, when the GP edit list tries to match the invoices to distributions, it can't match the two numbers, so it appears as if the invoice does not have any distributions at all. And when I opened the invoice distribution window, everything looked fine because GP was defaulting distributions based on the customer account settings. And when the distribution window is opened, GP creates new distribution records, but using the customer default accounts.
In short, the distributions that were imported with the full 20 character invoice number are "orphaned". They are not associated with any invoice, and even if you delete the invoices, the distributions will not be deleted. They will have to be cleaned out manually or via Check Links.
So, the lesson here is that you shouldn't rely on eConnect to manage the length of your character field values. Ideally, you should include field length checks in your integration validation code to detect data values that may exceed a GP field length.
Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified 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
New Dynamics GP 2010 Reporting Book and Packt Books Dynamics Month!
Congratulations to Christopher Liley and David Duncan for publishing their new book, Dynamics GP 2010 Reporting.
Reporting in Dynamics GP is not exactly the most user friendly experience, and is often overlooked or given insufficient attention. As a result, there are dozens of powerful reporting features that are never used by most Dynamics GP customers. It's great to see that there is now a book dedicated to the topic.
Celebrating the publication of their third Dynamics GP book and fourteenth Dynamics book, Packt has also declared May 2011 "Dynamics Month", so make sure to stop by their site and take advantage of the discounts that they are offering.
Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified 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
Reporting in Dynamics GP is not exactly the most user friendly experience, and is often overlooked or given insufficient attention. As a result, there are dozens of powerful reporting features that are never used by most Dynamics GP customers. It's great to see that there is now a book dedicated to the topic.
Celebrating the publication of their third Dynamics GP book and fourteenth Dynamics book, Packt has also declared May 2011 "Dynamics Month", so make sure to stop by their site and take advantage of the discounts that they are offering.
Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified 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
Tuesday, May 17, 2011
One Very Hairy Dynamics GP Integration
I develop a lot of custom Microsoft Dynamics GP integrations. A few are simple and straight forward. The majority have some quirks that require a few tricks or workarounds in .NET. And a few, well, they remind me that my imagination definitely has limits when it comes to envisioning complexities with certain businesses processes and related integrations.
I've been working on one particular integration over the course of a few months that has been vastly more challenging and complex than I could have ever possibly imagined. Believe it or not, it's a Purchasing Receipt import.
I know, I can hear it now: "Purchasing Receipt? You kidding? How hard could that possibly be?"
Let's start with the source data file. It's an Excel file, which is fine, but it is formatted as a report, with a report header in the first three rows (merged cells) and column headers on row 5. But there are only 7 column headers for 11 columns of data. Fine, I can read the file without headers, no problem.
And then the fields. There are only 3 fields related to the receipt transaction in GP: PO Number, Item Number, and Quantity.
Okay, so I have to lookup the PO number to find the vendor ID, no biggie. And sure, I can use the vendor ID and item number to lookup the vendor item number. Not so bad.
Except that some of the current vendor item numbers don't match the vendor item number on the PO. Sometimes the PO shows the vendor item number, sometimes it's the item number, and sometimes it's something else entirely--perhaps an older vendor item number. Oh, and sometimes even the item number won't match.
Okay....so I can add some logic that will figure out which PO line is being received by checking the vendor item number, then the item number, and then, believe it or not, the item description.
Whew, all done, right? Not so fast.
For some reason, many of the POs have the same item on multiple lines. Not 2 or 3 lines--we're talking 10 lines or more. So I can't just send the PO number and item number to eConnect--I have to know which PO line I'm receiving against. Oooookay, I now have a routine that can figure that out.
Almost done, right? Nope.
Did I mention that the receipt quantities sometimes exceed the PO quantities, and those excess quantities need to be received too, in some situations? Well, eConnect allows you to receive excess quantities if a PO line is not fully received, but if the line has already been fully received by a prior receipt, eConnect will refuse to receive additional quantities on that PO line. So if you have a PO line with a quantity of 20, and 20 have been received, but you need to receive 4 more, what do you do?
Well, I learned that eConnect will not allow you to adjust the quantity of a PO line that has been received. And it will also apparently not allow you to add a new line to an existing PO. So that just leaves...creating a new PO to receive the excess quantity.
You still with me?
So now that I've created the new PO, I need to pair it up with the receipt line. And then I need to write the new PO number back to the Excel file so that it can be traced or researched if necessary.
Oh, did I mention that there are POs that were created before the inventory item records were setup in GP? So not only do some PO lines have an "incorrect" item type status, but the corresponding vendor item record is not yet setup. So I need to "fix" those PO lines, and create a vendor item record.
And the list actually goes on and on from there--seriously, there really is alot more, but I assume you get the point. What at first seemed like a relatively simple endeavor ended up having requirements that have left me in amazement. Nearly every issue that could exist with the data and receipt process did exist.
I will no longer look at purchasing receipts the same ever again.
Which leads me to the general topic of designing and estimating custom eConnect integrations, which I'll discuss in another post: "The 90/10 Rule: 90 Percent Source Data, 10 Percent eConnect".
Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified 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
I've been working on one particular integration over the course of a few months that has been vastly more challenging and complex than I could have ever possibly imagined. Believe it or not, it's a Purchasing Receipt import.
I know, I can hear it now: "Purchasing Receipt? You kidding? How hard could that possibly be?"
Let's start with the source data file. It's an Excel file, which is fine, but it is formatted as a report, with a report header in the first three rows (merged cells) and column headers on row 5. But there are only 7 column headers for 11 columns of data. Fine, I can read the file without headers, no problem.
And then the fields. There are only 3 fields related to the receipt transaction in GP: PO Number, Item Number, and Quantity.
Okay, so I have to lookup the PO number to find the vendor ID, no biggie. And sure, I can use the vendor ID and item number to lookup the vendor item number. Not so bad.
Except that some of the current vendor item numbers don't match the vendor item number on the PO. Sometimes the PO shows the vendor item number, sometimes it's the item number, and sometimes it's something else entirely--perhaps an older vendor item number. Oh, and sometimes even the item number won't match.
Okay....so I can add some logic that will figure out which PO line is being received by checking the vendor item number, then the item number, and then, believe it or not, the item description.
Whew, all done, right? Not so fast.
For some reason, many of the POs have the same item on multiple lines. Not 2 or 3 lines--we're talking 10 lines or more. So I can't just send the PO number and item number to eConnect--I have to know which PO line I'm receiving against. Oooookay, I now have a routine that can figure that out.
Almost done, right? Nope.
Did I mention that the receipt quantities sometimes exceed the PO quantities, and those excess quantities need to be received too, in some situations? Well, eConnect allows you to receive excess quantities if a PO line is not fully received, but if the line has already been fully received by a prior receipt, eConnect will refuse to receive additional quantities on that PO line. So if you have a PO line with a quantity of 20, and 20 have been received, but you need to receive 4 more, what do you do?
Well, I learned that eConnect will not allow you to adjust the quantity of a PO line that has been received. And it will also apparently not allow you to add a new line to an existing PO. So that just leaves...creating a new PO to receive the excess quantity.
You still with me?
So now that I've created the new PO, I need to pair it up with the receipt line. And then I need to write the new PO number back to the Excel file so that it can be traced or researched if necessary.
Oh, did I mention that there are POs that were created before the inventory item records were setup in GP? So not only do some PO lines have an "incorrect" item type status, but the corresponding vendor item record is not yet setup. So I need to "fix" those PO lines, and create a vendor item record.
And the list actually goes on and on from there--seriously, there really is alot more, but I assume you get the point. What at first seemed like a relatively simple endeavor ended up having requirements that have left me in amazement. Nearly every issue that could exist with the data and receipt process did exist.
I will no longer look at purchasing receipts the same ever again.
Which leads me to the general topic of designing and estimating custom eConnect integrations, which I'll discuss in another post: "The 90/10 Rule: 90 Percent Source Data, 10 Percent eConnect".
Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified 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
Subscribe to:
Posts (Atom)