SQL Server Management Studio IntelliSense Doesn't Work

I have recently worked on several Dynamics GP SQL Server 2008 servers where the IntelliSense feature in SQL Server Management Studio was not working.

I would type "SELECT * FROM RM", expecting to get a list of RM tables, but nothing would happen.

Ever since I noticed the problem at a client, I seem to notice it happening all of the time, even on my own development servers, where I know IntelliSense normally works.

It seems that there are at least two general causes of this issue.  In the first case, for some reason the IntelliSense "local cache" has an issue or isn't setup.  To resolve that, try and refresh the IntelliSense cache by going to Edit -> IntelliSense -> Refresh Local Cache. 

 
After choosing this menu option, wait several seconds, and then try typing a query to see if IntelliSense starts working.  That seems to work for me most of the time.  I don't know why I have to do it occasionally on my SQL Servers, but it's easy enough.

In other cases there may be an issue with SQL Server.  At one client, the TEST server had an older SQL service pack level and apparently there were some IntelliSense issues resolved in a newer service pack.  But I've read other articles that tell you to dig around and make some changes in Management Studio or in Windows.  None of those instructions happened to work for me, but you can give them a try if Refresh Local Cache doesn't work.

Here are a few discussions of the issue:

http://www.mssqltips.com/sqlservertip/2291/enabling-intellisense-and-refreshing-intellisense-data-in-ssms-2008/

http://blog.sqlauthority.com/2009/03/31/sql-server-2008-intellisense-does-not-work-enable-intellisense/

http://stackoverflow.com/questions/552458/why-is-sql-server-2008-management-studio-intellisense-not-working


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

Dynamics GP 2010 Localization and Language

Two "L" words that cause a lot of confusion in regards to Microsoft Dynamics GP are "Localization" and "Language".  I have heard these used interchangeably when they actually mean very different things.

Localization is the inclusion of specialized functionality within Dynamics GP to address country-specific reporting and regulatory requirements.

Translation is actual language translation, meaning data, labels, reports in another language.

Unlike love and marriage, you CAN have one of these without the other. Currently, Microsoft Dynamics GP 2010 is available with the following:

• Localizations: Argentina, Australia, Canada, Chile, Colombia,United Kingdom, United States
• Languages: English (US), English (UK), French (Canadian),Spanish (Latin America)

Check out the following link for more information on localization and language availability for Dynamics GP:
http://download.microsoft.com/download/3/4/9/349B103F-95ED-4029-A9BF-ADB78A1852B2/MBS_GP_AvailabilityGuide_US.pdf

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.

Support Expectations

Recently, I have been coaching consultants that are relatively new in their careers working with Dynamics GP.  It has been a really eye-opening experience in learning styles, consulting styles, and how we all build knowledge and expertise in a way that allows us to craft a career path.  I know this will sound trite, but I have learned alot from them and it has brought back some of my own passion for what I do every day.  I do love my job- I love helping customers/clients/users get the most out of their software, and therefore improve their work life.  We spend way too many hours on the job to be miserable.  I also enjoy building relationships over the years with clients, former students, and fellow consultants.  Some turn in to friends, others turn in to mentors and mentees (is that even a word?).

It has gotten me thinking, though, about what I call the Support Conundrum.   A client's first interaction with consultants (beyond the salesperson) tends to be with the most senior folks in an organization.  Make sense, right?  We send out our "A" team to build the relationship, and to showcase our expertise. And, if the timing works out, these same people take the helm of the implementation project and serve in primary roles with the customer. 

So....what happens when the customer's implementation is done?  And they transition to support?  Who happens to be on the support desk?  In many organizations, it is the least senior folks.  The skill level both in product knowledge and working with customers may still be in development and uneven in some areas.  So how do we manage that?  How do we ensure that the customer is not disappointed with support, and at the same time the folks on the support desk are valued/not diminished in importance?

I believe strongly that working on the support desk is an excellent way for consultants to build knowledge, relationships, and strong troubleshooting skills. I don't have all of the answers regarding how best to support both the folks on the desk and the customers so that they don't look at support as the "2nd string".  But here are some of my random thoughts, and I hope that you will share yours as well...

1. Have a solid troubleshooting methodology as an organization, what questions do you ask immediately? 
2. Teach that getting the problem "down the road as far as possible" is as important as ultimately solving the issue, meaning that using solid troubleshooting to isolate the real issue/root cause is a productive task
3. Set expectations regarding how issues are escalated, and what information should be collected prior.  Involve the support desk in the resolution when it is escalated.
4. Teach triage (prioritization)
5. Make sure everyone understands that overcommunication in support is imperative, update clients on progress (or lack thereof) on a regular basis
6. As much of an ego boost as it may give you, do not "bad mouth" your support team...even passive- aggressively (e.g., encouraging a client to contact you directly when they should be using support)
7. Consider a reduced rate for phone/remote support services
8. Support "behind the scenes", providing the support desk with tips, tricks, etc directly so that they can provide it to the customer and learn themselves

What are your thoughts? What else could we add to the list?  Do you agree or disagree with any of these?

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.

Generating Secure Report Links in Management Reporter

Back in the FRx days, you could generate a report directly to Excel.  Due to security concerns related to generating financial data to a file that was not secure, that capability was eliminated with Management Reporter.  However, many users still have a need to generate reports to Excel without manually exporting it after publishing the report.

With Management Reporter 2012, you can publish a secure report link that will prompt the user to open the report in the Report Viewer, Excel, or XPS format.  The link is secure because it relies on Management Reporter security for the user to determine the reports to which they have access.  Very cool!

Let's take a look a the steps involved in doing this.  But first, the prerequisites...

1. You can publish the link to either a shared network location (UNC or mapped drive) or to a SharePoint site. 
2. If publishing to a shared network location, the service account used for the MR process service must have read/write access to the shared location.  And when using SharePoint, the service account must have Design permissions on the library.

Here is a link to a great blog post from the Dynamics Corporate Performance Management team on publishing links to a SharePoint site.

Now, to set the location, we navigate to the report definition in Management Reporter, then click on the Output and Distribution tab:



Mark the option to "Generate to multiple report library locations" and then specify the report library location in the left hand column, this will be used to determine the security access for the report.  Then, on the right hand side, use the Browse button to locate the shared directory to publish the report link.  You can then add additional combinations of report library locations and related report link locations, so that the report is published to multiple locations with different security settings as needed.

Once you have specified the necessary paths, you can generate the report.  The report link will automatically generate and appear in the shared directory:

Double-click to open the link:

If Report Viewer is installed, you will be given the option to open the report in Report Viewer, otherwise you can select Microsoft Excel or XPS Viewer.  We select Microsoft Excel:

Remember, it is using the security defined for the associated report library location to determine access to the report.  Then you are prompted to open or save the report in Excel:

We choose Open, and we see the report...Ta Da!

I know I need more data on the report, but hopefully you get the idea :)  Pretty cool in my book and super easy to set up!

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.

When developing eConnect integrations, don't forget the details...

I am testing an eConnect SOP Invoice integration that I just developed.  The invoices are importing fine, and everything looks good...except...the invoice distributions are duplicated.  Instead of 3 distribution lines, there are 6 lines.

Hmmm, that isn't good.  I'm sending in the three distributions, and they look good in GP, but somehow they are being duplicated.

I triple check my code to confirm I'm not doing anything silly, and the code looks good.  I then check the XML that is being sent to eConnect, and it looks good as well--only 3 distributions are being sent.

Puzzled, I then do a SQL trace on the eConnect inserts to see what it is doing.  The trace confirms that taSopDistribution is only being called 3 times, not 6 times.

So if only 3 distributions are being sent to eConnect and the stored procedure is only being called 3 times, how do I have 6 distribution lines in GP?

Although I have encountered several eConnect bugs over the years, I found it difficult to believe that this could be an eConnect bug.  Hundreds of people would have discovered this issue by now, so I have to assume it isn't a bug.

I then decide to randomly query the distribution records in SQL (SOP10102) to see what they look like.  And that is when it dawns on me.

Notice that the first three records have a DistRef value.  But the last 3 records don't.

The first three records are ones that I'm sending in to eConnect.  So what are the second three?

They look like the default distributions.  The distributions that would normally be created if you didn't send any distribution data to eConnect as part of the SOP invoice.  Which happens when you send a value of taSopHdrIvcInsert.CREATEDIST = 1.  Or, if you don't send any value for CREATEDIST.  Or if you forgot to send a value for CREATEDIST.

Ooops.

As soon as I saw the distribution records in  SOP10102, I realized what I had missed.  One little tiny detail that is kind of important.  Well, it's important if you want your distributions to be correct...

Don't forget the details!

 

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

Management Reporter Migration and Row Linking

It has been very well publicized that MR does not yet have row linking capability.  In FRx, you could design a report tree and have each branch of the tree use a different row format.  In MR, you can link to different GL links within a row format but not to different row formats.  Recently, though, I learned of a quirk with the migration related to row linking that I did not expect.

But first, a couple of good blog posts from the Dynamics Corporate Performance Management Team regarding FRx migration to Management Reporter and row linking.

http://blogs.msdn.com/b/dynamicscpm/archive/2012/05/23/migrating-to-management-reporter-2012.aspx

http://blogs.msdn.com/b/dynamicscpm/archive/2011/08/09/management-reporter-advanced-reporting-scenarios-and-features.aspx

What I was surprised to learn is that when you have row linking on a tree in FRx, it impacts the migration in more ways than one.  Not only does it remove the row linking from the tree (which would be expected), it also does not migrate the GL linking in all of the row formats there were linked in the tree.  For example...

The tree has a summary and three branches:

• Summary
• Dept 1
• Dept 2
• Dept 3

In FRx, the three departments are linked to three different row formats as follows:

• Summary: Row format A
• Dept 1: Row format A
• Dept 2: Row format B
• Dept 3: Row format C

When the row formats are migrated, only Row format A would be migrated intact.  Row formats B and C would drop their GL links (when you open the row, there would only be descriptions) so that the row formats would be have to be rebuilt with the correct GL links.

And it is not enough to simply remove the row linking in the tree in FRx prior to migration, as unassociated rows will not migrate.  So you would have to associate the rows with another catalog for them to migrate properly.  A little prep work to consider if you use row linking in FRx.  Then you can address the report design (the blog post above on advanced reporting has some ideas) to achieve similar results to row linking.

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.

Save Operation on UPR_WORK_State_Tax

When building a payroll (Transactions-Payroll-Build Checks) on GP2010, a client encountered the error "A save operation on table 'UPR_WORK_State_Tax' has created a duplicate key".  We isolated the error to benefit transactions for three specific employees and then determined what they had in common-- they were all being paid wages that were being taxed in a state other than their default state.  So, here is the scenario...

1. Employee has default state tax code defined, Cards-Payroll-Tax Information (in this example, MO)
2. Employee has pay code transactions entered (Transactions-Payroll-Transaction Entry) with state tax codes for another state only (in this example, IL)
3. Employee has benefit transactions entered (Transactions-Payroll-Transaction Entry) that are state taxable (Cards-Payroll-Benefits)

Now, in the example above, if the employee also had pay code transactions with the state tax code of MO then there would be no error.

Also, if you change the employee's default state tax code (Cards-Payroll-Tax Information) to IL, then there would be no error either.

I know this is not the most common scenario since most employees have wages that are only taxed in a single state. But I work with several organizations who need to tax specific wages in specific states for employees. I will update you all when I get more information from Microsoft on additional workarounds and/or a fix.

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.

The Power of the Microsoft Dynamics Ecosystem

The following question was posted to Experts Exchange recently:

We currently use Solidworks for CAD modelling.  Initial BOMS are created in Solidworks, and we would like to be able to import a BOM from Solidworks into Dynamics GP.

Hmmm.  That's a good one.  I checked the eConnect documentation, but BOMs are not a supported import.  You could reverse engineer the tables and try and import the data, but that isn't the most desirable solution--and that's just the GP side of things.  Since I'm not familiar at all with CAD software or Solidworks, it didn't occur to me to look for a third party add-on that might import such data into GP.

Well, the author of the question did some research, and believe it or not, there is a solution called Agni Link designed to provide a real-time, bidirectional integration between three major CAD software solutions and all four of the Microsoft Dynamics ERP solutions.

This is an example of the power of the Microsoft Dynamics "ecosystem".

First, products like Dynamics GP have been around for a long, long time, starting with its many years as Great Plains.  Second, the Dynamics ERP products are used by tens of thousands of customers, providing a large installed based that makes it economically viable to develop relatively niche products like a CAD to Dynamics integration.  Third, Dynamics ERP products have development tools and APIs that facilitate the development of third party solutions.

And last, because they all run on common Microsoft technologies like Windows and SQL Server, both of which are ubiquitous in the business world, third party solutions have the option of integrating with multiple Dynamics ERP products, providing a larger customer base for those ISV solutions.

I thought this was a great example of how all of those things come together to help customers use Dynamics GP to integrate their business systems.

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

Calculating Number of Days Past Due in SmartList Builder

Do you want to be reminded when a payable becomes past due by a certain number of days?  This can be difficult because it needs to be based on a calculation using the system date and due date of the invoice.  Here is a way to create a calculated field using SmartList Builder to do it.

First, I created a basic SmartList in SmartList Builder using the PM Transaction Open table.

Then I clicked on Calculations, and created a new calculation.

This calculation uses two functions (if you do not see the Functions, click on the white triangle next to the Fields header on the right hand side of the window and change the view to Functions): DATEDIFF to calculate the difference between two dates in days, and GETDATE to pull the system date in to the calculation.  So, in this example, it will calculate the difference (in days) between the due date and the system date.

After you add a calculate field to a SmartList, make sure you mark the Default checkbox for the field if you want it to display by default in the SmartList.

The resulting SmartList has a field called Days Past Due that calculates exactly that!  Then, if desired, you could set search criteria for the SmartList to display only those records where Days Past Due > 90.  Then when you save the SmartList favorite, you can choose to Add a Reminder based on the SmartList as well.  More information on using SmartLists as the basis for custom reminders in GP in a future blog post :)

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.

Management Reporter Drilldown Anomaly

Sometimes issues make sense even when you wish something worked differently. I came across a case like this in Management Reporter last week.  A client has a variety of reports for specific users, that contain the main accounts that each particular user is interested in. 

For example, for the user Bob Smith, the row format includes the range of  accounts from 4100 to 4800 as the following screenshot shows.

When the report is generated, and you drilldown on the row, you see all of the accounts in the range as expected.

But, let's say that you want to exclude the account 000-4510-01 because the user should not be able to see that account, Cost of Goods Sold.  So, you return to the row format, and update it accordingly:

What is key in this example is that we subtracted out the account we wanted to exclude, rather than modify the range.  When you subtract in this manner, the result is not what you might expect when you generate the report:



The account still appears on the drilldown report, although no amounts are displayed.  I understand. logically, why it is working this way-- as the account is included in the range and then subtracted. In the client's case, however, they would prefer that the account not appear at all.  And you will want to keep this in mind when designing reports and subtracting multiple dimensions.  As a workaround, you can avoid using the subtraction functionality and using smaller ranges instead in your row format.

On a side note, there was an earlier known issue where in this case the totals on the drilldown report would be incorrect as well.  But this has been resolved in the latest version of MR 2012.

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.

Sending LinkedIn invites to new connections

Every once in a while, I receive a LinkedIn invitation from someone I don't know, have never met, and have never e-mailed or spoken with. 

The LinkedIn invitation email message simply says:

"Matt Groening wants to connect with you on LinkedIn"

"Aziz Ansari wants to connect with you on LinkedIn"

There is no note, no introduction, no explanation as to why they have sent me the invite.  Who is this person that I've never met and why would I want to accept his invitation?

Don't get me wrong--I don't mind receiving invites from people I don't know and have never met--in fact I appreciate them and think they are part of the value of LinkedIn and online networking.

But what I don't like is the complete lack of introduction by the person sending the invite to me.  It would be like attending a business networking "mixer" event, walking up to someone, handing them your business card, and then walking away, all without even saying "Hi".  You would never do that in person, so why do that online?

Sometimes I take the time to click on the LinkedIn profile and look up the sender.  Most of the time it appears that they work with Dynamics GP in some fashion.  But without any explanation, for all I know, they may have just sent random invites out to random people recommended to them by LinkedIn.

I am not a heavy LinkedIn user, but when I do send LinkedIn invitations, I always delete the default message of "I'd like to add you to my network" and then write a brief note introducing myself or mentioning why I would like to connect with the person.

Maybe I want to connect with someone after reading their blog.  "Hi Ted, I just read your blog post about how Dynamics GP is going to conquer the world and really enjoyed it.  I look forward to more articles!"

More often, I send an invite to people after I've spoken with them on the phone for the first time.  "Hi Sally, it was great to speak with you this morning about your client that is looking for assistance with their Dynamics GP integrations.  I look forward to speaking with you when they are ready to move forward."

Or even simpler.  "Hi, I see that you are a member of the Dynamics GP group.  I'm connecting with other GP users and consultants." 

Any reason is fine with me, as long as there is some reason and you are willing to take 30 seconds to type something. 

LinkedIn can be a valuable resource and the connections you can make can be valuable as well.  So invest a few seconds to at least write a brief note if you are going to send someone an invitation to connect.

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

Calculating the number of fiscal periods between two dates

I developed a Purchasing Invoice integration for a client that has been running well for over a year.  Recently, the client requested some help automatically handling invoice posting dates during month end close.

The client typically kept their period open for 3 extra days while they gathered invoices related the prior period, but any invoices that arrived after those 3 days should go into the current period.  So if a 9/28 invoice arrived on 10/2, it would post to 9/28.  But if a 9/25 invoice arrived on 10/4, it would post to 10/1.

This was a challenge for them with the Purchasing Invoice integration because the vendor invoice data files would often contain invoices with a mix of dates.  A file received on 10/8 could have invoice dates from 9/15 to 10/7.  Sometimes a data file would contain an invoice dated over one month in the past.

And this client uses 4-4-5 fiscal periods, so I couldn't just use calendar months for the periods--I would need to query the GP fiscal period tables to determine the start and end dates for every period.

Once I knew the fiscal period dates, I had to create logic to check the invoice date, compare it to the current period dates, and determine whether the invoice should post to the current period or the prior period.

The first step was to query the GP fiscal period tables.  Whenever I think of "GP" and "query", I think of Victoria Yudin, the Dynamics GP MVP and reporting guru, who has a few billion queries posted on her blog.  Sure enough, a quick Google search of "dynamics gp fiscal period query" sent me right to her blog. (Thank you Victoria!)

http://victoriayudin.com/2010/11/24/sql-queries-for-fiscal-years-and-periods-in-dynamics-gp/

I borrowed Victoria's second query and adjusted it to only return info about the current fiscal period.

SELECT D.PERIODID PeriodNumber, D.PERNAME PeriodName, D.PERIODDT StartingDate, D.PERDENDT EndingDate, D.YEAR1 FiscalYear
FROM SY40100 D INNER JOIN SY40101 H ON H.YEAR1 = D.YEAR1
WHERE D.FORIGIN = 1 AND D.PERIODID <> 0
   and GETDATE() between H.FSTFSCDY and H.LSTFSCDY
   and GETDATE() between D.PERIODDT and D.PERDENDT
ORDER BY D.PERIODID

Now that I had the current fiscal period start and end dates, I could calculate if the period "cutoff window" (3 days) was still open for the prior period, and whether an invoice should post in the current or prior period.  That worked out well for invoices dated in the current period or the immediate prior period.

But what about an invoice dated 8/1 that arrives on 10/10?  I needed a way to determine how many periods "old" the invoice date is for these older invoices.  Since the client uses fiscal periods and not calendar periods, I can't count the number of months difference between the current date and the invoice date.

I created another version of Victoria's fiscal period query to get the period info for the invoice date.  My .NET code substitutes in the invoice date in the WHERE clause.

SELECT D.PERIODID PeriodNumber, D.PERNAME PeriodName, D.PERIODDT StartingDate, D.PERDENDT EndingDate, D.YEAR1 FiscalYear
FROM SY40100 D INNER JOIN SY40101 H ON H.YEAR1 = D.YEAR1
WHERE D.FORIGIN = 1 AND D.PERIODID <> 0
   and '07/15/2012' between H.FSTFSCDY and H.LSTFSCDY
   and '07/15/2012' between D.PERIODDT and D.PERDENDT
ORDER BY D.PERIODID

In this example, the 7/15/2012 date would return fiscal period 7.  If the current date is 10/10/2012, then I know that 10 - 7 = 3, so this particular invoice cannot post to the immediate prior period, and must post to the current period (October).

But that 10 - 7 calculation only works for periods in the same year.  What if it is January 2013 and the invoice is dated December 2012?  I can't use 12 - 1 = 11, as that is incorrect.

The trick here is to multiply the fiscal years by 12, then add the period numbers, then find the difference between those two values.  So using the example above:

(Current Year * 12 + Current Period) - (Invoice Year * 12 + Invoice Period)

(2013 * 12 + 1) - (2012 * 12 + 12) = 1

24157 - 24156 = 1

And if an old invoice dated May 2012 arrives in January 2013:

(2013 * 12 + 1) - (2012 * 12 + 5) = 8

24157 - 24149 = 8

And that is how you calculate the number of fiscal periods between two dates.

The last minor challenge I faced was that the eConnect taPopEnterMatchInvHdr object does not have a property for GL posting date--it only allows you to import the invoice date.  This is not uncommon for eConnect transaction headers, but it is annoying.  So, after you import each document, you have to directly update the transaction GL posting date yourself.

In my case, I updated the POP10300 table.

UPDATE POP10300 SET GLPOSTDT = @GLPOSTDT WHERE POPTYPE = 2 AND POPRCTNM = @POPRCTNM AND VENDORID = @VENDORID AND VNDDOCNM = @VNDDOCNM AND receiptdate = @INVOICEDATE


And then I was finally done.


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 most baffling Dynamics GP issue I've ever seen

I spoke with a customer today who discovered an extremely unusual problem with one of their Dynamics GP batches.  I think it rises to the top of my list to be the most baffling problem I've ever seen.

The client has two company databases--one is for a US company, and the other is for a Canadian company.  While reviewing some of their posted transactions, they found that a batch from the Canadian company had "posted" to their US company.

You read that right.  A batch from Company A ended up posting to Company B.  And this isn't an intercompany transaction.

What does that even look like?

This is a screen shot from the US Company.  Notice that the customer Name field is blank and the Currency ID is CAD.

The customer number 131654 doesn't even exist in the US company, and the US company doesn't process Canadian Dollar transactions.

In the Distribution window, there are no distributions.

And like the transaction window, the customer Name is blank.

Making it more fun is that the distributions posted to the GL in the Canadian company!

So the transaction posted to the subledger in one company, and the GL was affected in another company.

This is the case for all transactions (dozens) in a single batch.

Anyone who understands how GP is designed, how it works, how it posts transactions, and how the underlying company databases are setup would, I believe, say that such a situation is impossible.

I would have previously agreed, but now that I've seen the screen shots and stopped stuttering in disbelief, I can't dispute that it happened; however, I also can't begin to rationally explain how it could have possibly happened.

Like many puzzling situations, there are a few clues, although none directly explains what happened.

First, this batch was imported into GP--it was not manually entered.  So perhaps, maybe, somehow, something in the data that was imported could have resulted in some highly unlikely posting fluke.  But, I'm told the batch was imported using eConnect.  eConnect is pretty rigorous with its validation, so it's unlikely that any transaction data elements could have possibly caused this.  And even if we say that something was odd about this particular imported batch, I can't imagine any scenario that could possibly cause a batch to be posted to two different companies simultaneously.  Imported into the wrong company, maybe, but not two different companies.

Second, the batch was posted automatically using an ISV solution.  The logs from that batch posting solution show that the batch was detected and posted in the Canadian company.  So that is presumably where the posting occurred.  But even if something went wrong or there was a posting error or problem, how could that possibly explain what happened?  The batch posting solution simply logs into the GP company, selects the batch, and then posts it, all within the GP client application.  It isn't doing anything behind the scenes, calling stored procedures, or otherwise attempting to simulate the posting process outside of GP--it's all native GP functionality.

The client has been importing these batches for several months, and automatically posting them with the ISV batch posting solution for months.  So this batch was, in theory, no different than any other batch.  But obviously something went wrong.

I'm only left with one tenuous, and perhaps unprovable theory.  My wild guess is that when the automatic batch posting solution switched GP companies and logged into the Canadian company, something in GP stayed "connect to" or "logged into" the US company.  When the post command was issued for the batch in the Canadian company, somehow GP posted the subledger transactions in the US company, but posted the distributions in the Canadian company.

Or perhaps it's simpler than that.  Perhaps the transactions all "posted" in the Canadian company, but when GP moved the transactions from the Work to Open tables, the subledger transactions were moved from the Canadian company to the US company.  That's probably the best guess I can come up with, simply because it is the simplest technical explanation I can imagine--although even saying something like about Dynamics GP that sounds like complete crazy talk.  Unless it can be reproduced, which I'm guessing it can't, nobody is going to believe it.

This was only recently discovered, so the research is still ongoing and it will likely require a support case to resolve.

But I am pretty confident that it is a contender for one of the most unusual GP support issues.

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

NIST SHA-3 Cryptographic Hash Selected

Today, NIST announced that they have selected the "Keccak" algorithm to be the new SHA-3 cryptographic hash algorithm.

Interestingly, Bruce Schneier, one of the SHA-3 contest finalists, recently wrote that he didn't think NIST should select any of the candidates.

For anyone that doesn't know what a cryptographic hash algorithm is, here is the Wikipedia page that hopefully offers an understandable explanation.

In short, they're pretty important to computer security.


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

C# errors updating Excel file: "External table is not in the expected format", and "Operation must use an updateable query"

A client has been using a Dynamics GP Purchasing Receipt Import application that I developed over a year ago.  The application reads receipt data from an Excel file, imports the receipts and matches them against Purchase Orders, and then updates each row in the Excel file with a status, the receipt number assigned by GP, and any notes regarding the transaction.

The client recently setup some new GP Terminal Servers running Server 2008 R2 and Office 2010, so I transitioned the Receipt Import to the new servers.

After the server move, when they tried to run the import, it was not importing all of the receipt lines, and we saw that some of the rows in the Excel file were not updated with a status or receipt number.  In the log files, we found two different error messages:

• External table is not in the expected format
• Operation must use an updateable query

Both of these are relatively common JET or ACE OLEDB driver errors when working with Excel files.  At first I thought that the updates to the Excel file were consistently failing, but some rows were updated successfully.

I then thought that maybe there was a data type issue or formatting issue in some Excel rows that was preventing them from being updated--something I've seen regularly, and is one of the reasons why I no longer recommend using Excel as a data source.  But after reviewing the data for the rows that had update errors, I didn't see anything wrong with the data.

I researched both errors, but the common solutions for each were not applicable.  I've done plenty of integrations using Excel files, so I knew that my connection string was fine, my Excel provider was correct, and that the Excel file permissions were not the issue.  After all, most rows were updating properly--but every once in a while, an update would fail.

After a dozen rounds of debugging and stepping through the code, I couldn't figure out the exact cause, but I accidentally found a workaround to avoid the errors.

It seems that there might be an issue with the Microsoft ACE driver when it attempts to connect to an Excel 2007 or 2010 file.  In some cases, the Connection Open command or an Execute command will simply fail, returning one of the two errors listed above.

It seems neither of the errors is accurate, but rather the connection to the Excel file is somehow bad or in an invalid state.  My guess is that this occurs when there are frequent connections and updates, which is what my import is doing--updating hundreds of rows in the Excel file, one at a time, after each receipt line is imported into GP.

While stepping through the code, after the exception was caught, I jumped back to the point where my connection string was built and the connection was opened--just to try the same update query again and confirm where the exception occurred.  But the second time the code ran, the connection opened fine, and the query executed properly.

One moment, the query fails, the next moment it works fine, as long as the connection was re-opened.

So, I added an additional try / catch block around my connection open and execute commands, and in case of an exception, the code simply tries to reconnect to the file a second time.


try
{
    //Do not use IMEX=1 on updates to Excel, as the IMEX parameter prevents updates
    connString = "provider=" + Properties.Settings.Default.excelProvider + ";Data Source='" + fileName + "';Extended Properties=\"" + Properties.Settings.Default.excelWriteSettings + "\";";
    xlConn = new OleDbConnection(connString);
    xlCmd = new OleDbCommand(xlQuery, xlConn);

    xlConn.Open();
                   
    rowsAffected = xlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    log.Write("Re-trying UpdateExcelRow for query (" + ex.Message + "): " + xlQuery, false, true);
    //Try and close and then re-open the connection
    xlConn.Close();
    connString = "provider=" + Properties.Settings.Default.excelProvider + ";Data Source='" + fileName + "';Extended Properties=\"" + Properties.Settings.Default.excelWriteSettings + "\";";
    xlConn = new OleDbConnection(connString);
    xlCmd = new OleDbCommand(xlQuery, xlConn);

    xlConn.Open();

    rowsAffected = xlCmd.ExecuteNonQuery();
}


It's a bit like using duct tape and twine, but this approach appears to have resolved the issue, and was much quicker than diagnosing the underlying cause, which may be an ACE driver issue.  I was able to watch the exceptions occur occasionally, but the second connection Open and Execute commands always seemed to work.

The new code is now in production, so I'll see if that fully resolves the issue, or if there is some other gremlin waiting to cause new problems.

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

GoToMeeting vs. Mikogo for desktop sharing and web conferencing

I've used GoToMeeting for years to provide remote support to Dynamics GP customers and do some remote presentations to small groups.  It has worked very well overall and has done a great job of meeting my needs.  While more expensive than Microsoft's offering, I can confidently say that GoToMeeting is vastly superior to Live Meeting, and I also like it much better than WebEx.

Although it is a great service, I do occasionally have some problems using GoToMeeting.  This morning I had a GoToMeeting session scheduled with a new client.  We both dialed into the GTM conference number and were on the call, but when the client clicked on the GoToMeeting link that I had provided, he said nothing happened.  Customers sometimes do have some issue clicking on the meeting link--whether it is a browser issue, or a problem downloading or installing the GoToMeeting client app.

So I asked my client to open a web browser to navigate directly to www.gotomeeting.com.  When he tried that, he said nothing happened.  The web browser would just 'spin' and he couldn't connect to the web site.  He even tried entering the IP address in the browser address bar, but he still couldn't connect.  Obviously this isn't GoToMeeting's fault.  There was apparently something with the customer's network, firewall, or proxy that was preventing any connection to the GoToMeeting web site.  Since things like this happen, it doesn't hurt to have a backup.

Quite a while ago, a colleague told me about a free GoToMeeting alternative called Mikogo, and he spoke very highly of it.  I gave it a brief try and saw that it seemed to work fine, but since I normally used GoToMeeting, I didn't have much need for it.  Well, this morning I was scrambling for an alternative to GoToMeeting so that I could help my client.

After several Google searches to remember the name, I found Mikogo and downloaded the latest version.  In just a few minutes, I had signed up for the free account, had a meeting started, and a few seconds later, the client was connected to the meeting and showing his desktop.  Mikogo worked great and really saved the day.


Now that I've used it once and taken a few minutes to check out some of its features, I thought I would provide a review and compare it to GoToMeeting.

The full Mikogo client application is about 9.5 MB, so it may take a little while to download, but it installs very quickly and easily.  Attendees who join your meeting can download and install the "Connection Program", which is about 5 MB and runs on the remote machine without requiring an installation.  Mikogo also has a Join a Session web page that lets an attendee select an HTML Viewer option, allowing them skip any downloads and join the session right in their browser window (view only).  I tested this on my iPhone and it worked great--I was able to see my desktop session on my phone and the refresh rate and speed seemed very good.

When Mikogo launches, a small, simple control panel is displayed.

Interestingly, Mikogo allows you to create and save different session "profiles" that let you to specify options like whether or not to display your task bar or whether you want to allow participants to see file transfer options or record the session.  For simple customer support sessions, it may be preferred to turn off many of the options to keep things simple and easy for you and the user.  I found this option interesting and appealing.

Like GoToMeeting, Mikogo uses a 9 digit "Session ID", so it is simple to share with meeting attendees.  And like GoToMeeting, Mikogo allows you to create a new session at any time, or have multiple scheduled sessions.  Mikogo also has both a Chat and Whiteboard feature, very similar to GoToMeeting. 

Similar to GoToMeeting, Mikogo allows you to choose which applications are visible when you are presenting.  But unlike GoToMeeting, which only lets you select one application at a time, Mikogo lets you selectively enable or disable the display of multiple applications.  This is a great feature.

So if I want to show Word, Excel, and Dynamics GP, but want to hide Outlook, Live Messenger, my desktop wallpaper, and even my desktop icons, I can do so by clicking individual checkboxes.  And unlike GoToMeeting, the presenter can display his Mikogo control panel to the attendees, and vice versa.  This is very helpful when you need to show a user which button to click to perform an action on the control panel--something that I regularly struggle with when I ask clients to click on the "Give Keyboard and Mouse" control option in GoToMeeting.  This multiple application display feature is very clever and very nicely implemented.  Mikogo also supports multiple monitors, like GoToMeeting.

One significant feature that Mikogo offers that is not available with GoToMeeting is File Transfer.   The lack of file transfer is one weakness with GoToMeeting.  Although I don't need to use it very often, I've had plenty of situations where it would have been very convenient to transfer a file as part of the remote session, rather than having to use e-mail or FTP.  This is a big plus if you are doing a lot of interactive work and need to transfer a data file, SQL script, documents, etc.

Mikogo offers a unique "URL Push" feature, that lets you send a URL to attendees, which can automatically open that URL in their web browser if they accept the link.  This can be done via chat in GoToMeeting, but the URL Push makes the process simpler for the user.

Like GoToMeeting, text copy and paste works between the presenter and attendee sessions, so you can copy and paste text in both directions.

Mikogo provides screen scaling and resizing, so displaying smaller or larger screen resolutions isn't a problem.  It also has a handy zoom / magnify feature in cases where the presenter has a much higher screen resolution.  The screen scaling was relatively good, although it didn't seem quite as sharp or as readable as GoToMeeting when a high resolution presentation is scaled down to display on a lower resolution display.  But it is still very good compared to other services I've tried, and Mikogo even provides a Session Profile option to increase or decrease the picture quality, presumably for performance.

In terms of drawbacks of Mikogo compared to GoToMeeting, so far I've only found two small ones.

First, it appears that the screen refresh 'speed' with Mikogo is slower than GoToMeeting.  GoToMeeting seems to be better at quickly updating the cursor location, and it seems to be faster updating the overall display.  The Mikogo speed is perfectly acceptable, but since I'm used to GoToMeeting, I immediately noticed the slightly slower speed.  I was testing with two machines on my wireless network, but obviously Internet connection speeds will vary by session and participants with any web conferencing solution, so real world performance may vary.

Second, a minor difference is that the Mikogo client doesn't display conference calling options by default.  They do offer international telephone audio conferencing options, similar to GoToMeeting, which you can configure to be displayed in the Mikogo client, but those don't automatically appear like they do in each GoToMeeting.  More importantly, Mikogo does not appear to yet (see update below) have a Voice Over IP audio conferencing option like GoToMeeting, so if you do a lot of international sessions, that may be a drawback.  All of my non-US customers used the VoIP option in GoToMeeting, so I would have to use Skype or some other VoIP option if I were to use Mikogo for those sessions.

UPDATE:  Mikogo has informed me that they will be adding a native VoIP voice conferencing feature, with beta testing starting very soon.


With all of that said, I think Mikogo is a very impressive, very capable web conferencing and desktop sharing solution that can compete fairly well with GoToMeeting.  I would personally choose it over Microsoft Live Meeting any day without question.

So now let's talk about pricing.  Mikogo used to be an exclusively free offering.  But they have recently transitioned to a "freemium" model.  This means that they offer their product for free for personal use, but kindly ask that you buy a subscription if you are using it for business or commercial use.

They have several "single user" subscription options, depending on how many participants you need in your sessions.  At this time, their "Basic" plan is $13 per month, which allows up to 3 simultaneous participants per session.  The "Pro" plan is $19 per month, and allows up to 15 participants.

$13 per month seems like a great price considering how much functionality Mikogo provides.

They also offer "multi-user" plans for organizations that want to share a pool of subscriptions, although you'll have to do the math on whether this is any cheaper for your organization.

As a comparison, GoToMeeting is currently $49 per user per month, or $468 for a year.  That's $312 more per year than a Basic Mikogo subscription.

If you are on a budget, that's quite a difference.  It's enough that when my GoToMeeting renewal comes up, I'm going to have to seriously consider whether I should switch.

I've only used Mikogo "for real" once, so I would have to use it a lot more to see if there were any other quirks or drawbacks, but if you are shopping for a web conferencing or desktop sharing solution, I would definitely recommend checking out Mikogo.


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

Developer Gripe: "An unknown error has occurred"

While at the Dynamics GP Technical Airlift two weeks ago, I was speaking with someone who mentioned that Management Reporter was driving them nuts with a generic error message:

An unknown error has occurred

That's it.  No further description, no error number, and no further clues as to the cause of the problem, when or how it occurred, or how it might be resolved.

In short, an utterly useless error message.

Coincidentally, Christina just wrote a blog post about one possible cause for this generic Management Reporter error.  Who knows how many other reasons may cause that error to occur.

This generic error message is coming from Management Reporter.  A relatively new, modern Microsoft product.  Not that Microsoft products are known for their illuminating error messages, but they don't get much worse that "An unknown error has occurred".

I'm no Super Hero Developer, but I have never written a program that had such a lame error message.  It's puzzling for a few reasons.

First, the fact that the message is displayed in a dialog box means that the error was detected or trapped by the application.  This means that some part of the application code anticipated that there could be some type of "error", is handling the error, and is displaying the error message.  I understand that not every possible error can be anticipated, but when an error is being thrown, and you have an error handler setup to display a dialog box, it is negligent to not provide any information to the user as part of the error message.  What would be the point?  Displaying highly technical or a very specific detailed message is better than nothing--at least the user can send more info to an administrator or consultant who can then evaluate it rather than spending hours aimlessly trying to guess the problem.  Or it can at least be sent on to Microsoft as part of a support case to help their internal debugging. 

Second, more generally, such a useless error message seems to indicate an application architecture or development methodology that is designed to not provide error information to the user.  That's not an accidental choice.  It's something that has to be intentionally, designed, coded, reviewed, tested, and released.  As I said, I'm not a Super Coder, but even I develop my relatively simple applications to capture and expose error messages.  It makes the customer's life easier, and it makes my life easier.  Why a development team for a commercial Microsoft software product would implement such a generic error message design is baffling to me.  If my applications can do it, certainly Microsoft could pull it off.

Last, I would argue that there is no such thing as an "unknown error".  There are only "expected errors" and "unexpected errors".  If you display an error dialog box, then by definition your application has detected the error, and the error is known.  Something caused that dialog to display based on some condition in your code.  You may not know the scenario or specific cause or details, but you fundamentally know that an error occurred.  However, that error may be expected or unexpected.  Expected errors are those that you anticipated and handle explicitly.  Unexpected errors are ones that you didn't anticipate, and handle generally (such as with an Unhandled Exception error).  An invalid GL account or an invalid date might be expected errors--you anticipated that a user may enter an incorrect value.  A divide by zero error might be unexpected--until you see it happen, identify the cause, and add validation to check for a zero denominator--and then it becomes an expected error:  "Hey, you can't divide by zero in that formula!".  In either case, you can capture the error and display a meaningful message with actual information that a human might find valuable.

What makes it puzzling is that good error handling and error messages aren't particularly difficult to do with typical business applications.  They aren't typically dealing with low-level functions like CPU instructions, hardware drivers, or low level network connectivity, so there aren't too many excuses for not having decent error handling.  It just takes a little bit of knowledge, some basic design practices, consistent coding, and project management.  I'm confident the team that developed Management Reporter could easily improve the error messages if it were made a priority and a practice.

Users, customers, consultants and developers:  Demand better error messages.

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

Dimension Filters in Columns in MR 2012

I came across an interesting issue in Management Reporter 2012 last week with a client.  They had recently upgraded and found that one report did not work.  They would generate it, and receive "An unknown error has occurred while processing report".  So we did some basic troubleshooting, and were able to isolate the issue to the column layout.

While testing with the column layout, we noticed that the error only occurred when we included columns that had a dimension filter that was adding and subtracting dimension sets.  And in contacting Microsoft, we found that it was a recently reported quality report.  The actual issue is with regards to subtracting, it doesn't matter if it is dimensions or dimension sets.  If you use a (-) in a dimension filter in a column layout in MR 2012, you may encounter the error.  There are a couple of workarounds...

1.  Create a dimension set that includes the necessary dimensions and any subtraction you need to include
2.  Create two hidden columns, one with the dimensions you need to add and one with the dimensions you need to subtract. Then use a calculated column to perform the necessary math.

Word from Microsoft is that hopefully this will be fixed in the October update for MR, we will keep our fingers crossed.  Although this may not seem major, it is one of those annoying "quirks" that I am always happy to see fixed :)

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.

Back in the saddle

You may (or may not) have noticed that the past couple of months have been filled with Steve's awesome technically profound blog posts while little has been heard from myself in that time.  Well, I actually have a really great excuse....

Carter Wesley Phillips joined our family on July 23rd at 11:23am.  So I have been out on maternity leave, and I actually did not work for most of it!  And, yes, for those Johnny Cash fans out there...that is a "Crawl the Line" onesie that Carter is sporting in the picture above (which was taken when he was 3 days old).

I am excited to be back in the saddle, and promise some real blog posts soon.  I was so disappointed to miss the Technical Airlift in Fargo, so I have really enjoyed catching up on the fun reading all of the blog posts from the event :)

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.

How do I find the eConnect 2010 version?

During lunch today at the Dynamics GP Technical Airlift conference in glorious Fargo, I was asked "How do I find the eConnect 2010 version?".

The question was asked because the eConnect 2010 service packs have no dialogs, no status indicators, and no indication that they have completed or installed successfully.  So after installing the service pack, you don't really know if it ran, and you can't tell whether eConnect was updated.  You are left wondering, "Did eConnect really get updated?".  You feel compelled to check something to confirm the eConnect version.

With eConnect 2010, the definition of the eConnect version changed slightly.  With GP 10 and earlier, eConnect version numbers were values like 10.0.3 or 10.0.5--these were values stored in the database that related to the version of the eConnect SQL objects.  But with GP 2010, the eConnect version numbers are now values like 11.0.1761.0 or 11.0.1923.0, which are not related to the SQL objects.

The eConnect 2010 version number format is different because Microsoft is now referring to the version number of the eConnect 2010 run-time files.

To check the eConnect 2010 version, open Windows Explorer and navigate to:

C:\Program Files\Microsoft Dynamics\eConnect 11.0\API

Right click on the eConnect.dll file and select Properties.  Then click on the Details tab.

Once you have the file version number, you can open the Dynamics GP 2010 version list Excel file, go to the eConnect tab, and determine which eConnect service pack is installed.

The follow up question was:  "But then what is the point of the eConnect Release Information application under Start -> All programs -> Dynamics -> eConnet -> Release Info?".

My understanding is that the eConnect Release Information application is now of little or no value.  That application queries the GP databases to determine the version of the SQL eConnect objects--but those objects are now updated by the GP service packs, not by eConnect service packs, so that version number will no longer correspond to the eConnect service pack installed on any given machine.


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

Dynamics GP 2013 Beta Available for Download on PartnerSource

As announced this morning at the Dynamics GP Technical Airlift conference in Fargo, the Microsoft Dynamics GP 2013 BETA is now available for download on PartnerSource. 

https://mbs.microsoft.com/partnersource/support/selfsupport/productreleases/MDGP2013_TAPReleaseDownload.htm

It is sitting queued in my File Transfer Manager at the moment without starting the download yet, so it looks like there might be a lot of people all trying to download it today.

Lots of Beta code caveats, so read the release notes and test on a disposable test server.

Good luck!

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

Interesting eConnect PA Misc Log Bug: Fiscal period for posting date does not exist

Today I finished up developing an GP 10 PA Misc Log import using eConnect.  I was doing some final testing in Fabrikam and everything was looking good in the Misc Log transactions.

I then clicked on the Distributions button (because one should ALWAYS test the distributions on any transaction import), and after I clicked OK to close the distribution window, the following error message was displayed:

The following errors were found while validating distributions:

Fiscal period for posting date does not exist


Hmmm.  Okay.

I went to another transaction to check the distributions but the error did not appear.  I tried to check other distributions, but I couldn't seem to reproduce the issue.

I deleted my test batch and reimported it again.  I pulled up the first transaction in the batch and was able to reproduce the error.  So I open the distribution window to see what might be wrong, and I confirm that there is no posting date visible on that window.  I check the eConnect documentation and confirm that there is no posting date field that can be set--and regardless, the distributions are being defaulted by GP, so I'm puzzled why the distributions would have a problem.

I then check the database tables, starting with the Misc Log Distributions Work table, PA10203.  There is no posting date in that table, just basic distribution info.  Then I check the Misc Log Work table, PA10200.  While browsing the dates, I see something odd.  One of the transactions has a value of "1900-01-01" in the "PAPD" field.  The rest of the transactions have the proper test date of 7/1/2017.

I reviewed the eConnect documentation yet again and confirm that there is no posting date available--only a document date. 

After doing more testing, I determine that only the first transaction that is imported has a bad posting date.  Very odd.  At first I think it's because it's the first transaction imported for some reason, but after doing more testing that doesn't seem to be the case.

After a few more rounds of tests, I finally figure it out.

By default, the Fabrikam company is set to use a Batch posting date for Misc Log transactions (Setup -> Posting -> Posting -> Post Date From:  Batch).

So that "PAPD" field value is technically coming from the batch posting date rather than from the transaction data that I am sending in to eConnect.  But for this import, I am not creating the batch--I'm just sending in the Misc Log transactions and letting eConnect create the batch automatically.

Well, it seems that there is a bug in the Misc Log import.  When the first Misc Log transaction is sent to eConnect for a new batch that does not yet exist, eConnect is not properly setting or updating the PAPD date value for the transaction.  Perhaps it is inserting the transaction first (resulting in a PAPD value of 1/1/1900) and then creating the batch, but not updating the PAPD value.  Or perhaps when it inserts the transaction, it attempts to get the batch posting date, and returns 1/1/1900 because the batch doesn't yet exist.

In any case, it's a small but annoying bug, since it will potentially affect the transaction posting.

There are two potential workarounds.

1. If the client has their posting setup to use Posting Date From:  Transaction, the bug is not triggered, so the import should work fine.

2. If the client uses the posting date from the Batch, then you can create the batch first, using the eConnect taCreateUpdateBatchHeaderRcd transaction type, and then import the Misc Log transactions.  As long as the batch exists with a valid posting date, the transactions should import with the correct PAPD value.


Again, this is on GP 10 / eConnect 10 SP3.  I don't have time to see if this was resolved with a GP 10 service pack, and I haven't yet tested with GP 2010 to see if it also exists there.  In the unlikely event that I can test it with GP 2010 (or when the client upgrades), I'll try and post an update.


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

Exporting Dynamics GP Purchase Orders: It's Much Trickier Than You Might Think

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