tag:blogger.com,1999:blog-66919941292227447592024-03-18T02:11:36.061-07:00Dynamics GP LandMy blog has moved! Please visit the new blog at: https://blog.steveendow.com/
I will no longer be posting to Dynamics GP Land, and all new posts will be at https://blog.steveendow.com
Thanks!Christina Phillipshttp://www.blogger.com/profile/03332221198245457747noreply@blogger.comBlogger763125tag:blogger.com,1999:blog-6691994129222744759.post-3242510808068462252019-02-17T07:16:00.000-08:002019-02-17T07:16:00.646-08:00eConnect Performance: Using GetNextDocNumbers vs taGetNext stored proceduresNew blog post at <a href="http://blog.steveendow.com/">blog.steveendow.com</a>:<br />
<br />
<br />
eConnect Performance: Using GetNextDocNumbers vs taGetNext stored procedures<br />
<br />
<a href="https://blog.steveendow.com/2018/11/econnect-performance-using.html">https://blog.steveendow.com/2018/11/econnect-performance-using.html</a><br />
<br />
<br />
This image shows 3 instances of my load test application simultaneously retrieving a total of 3,000 JE numbers over about 45 seconds.<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8Zn6Pgv_lTEVYYtvtV9INpsq2tFLgisTp21XbCE4oI3Uk9_AkJsu0WLYRT-OVrvKyKV-xdco68ywejK-ncfWUIyqvhzBFaxR8r68KcfQvIEOMpdVHFnpGfNkQCeriYLYY7AiBQ5zvjYu8/s1600/NextJE1.jpg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="755" data-original-width="1494" height="161" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8Zn6Pgv_lTEVYYtvtV9INpsq2tFLgisTp21XbCE4oI3Uk9_AkJsu0WLYRT-OVrvKyKV-xdco68ywejK-ncfWUIyqvhzBFaxR8r68KcfQvIEOMpdVHFnpGfNkQCeriYLYY7AiBQ5zvjYu8/s320/NextJE1.jpg" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">3,000 JE Numbers</td></tr>
</tbody></table>
<br />
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-51686942956199488262019-02-17T07:13:00.002-08:002019-02-17T07:13:33.489-08:00Dynamics GP Document Attach Record Type (Origin Description) CodesNew post at <a href="http://blog.steveendow.com/">blog.steveendow.com</a>:<br />
<br />
<br />
Dynamics GP Document Attach Record Type (Origin Description) Codes<br />
<br />
<a href="https://blog.steveendow.com/2019/01/dynamics-gp-document-attach-record-type.html">https://blog.steveendow.com/2019/01/dynamics-gp-document-attach-record-type.html</a><br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEitDbdn1wVbNrTqhDWTZesZGPbI8HEtcSWCUcGWNUichrhaY9-cPuP3cI4onXBnyVcjxg6obMWP-kku3KxPKX1g9VUCbVZ9zPstgrJ1kO5QbsfyuJgZUl9_-JFFbanhwfQv5rqnHa3Uf14A/s1600/DocAttachRecordTypes.jpg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="364" data-original-width="970" height="120" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEitDbdn1wVbNrTqhDWTZesZGPbI8HEtcSWCUcGWNUichrhaY9-cPuP3cI4onXBnyVcjxg6obMWP-kku3KxPKX1g9VUCbVZ9zPstgrJ1kO5QbsfyuJgZUl9_-JFFbanhwfQv5rqnHa3Uf14A/s320/DocAttachRecordTypes.jpg" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Dynamics GP Document Attachments Listed in the CO00101 Table</td></tr>
</tbody></table>
<br />
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-55739866113047700532019-02-17T07:11:00.006-08:002019-02-17T07:11:55.032-08:00Bulk Export Dynamics GP Document Attachments using .NETNew post at <a href="http://blog.steveendow.com/">blog.steveendow.com</a>:<br />
<br />
Bulk Export Dynamics GP Document Attachments using .NET<br />
<br />
<a href="https://blog.steveendow.com/2018/12/bulk-export-dynamics-gp-document.html">https://blog.steveendow.com/2018/12/bulk-export-dynamics-gp-document.html</a><br />
<br />
<br />
<span style="background-color: white; color: #444444; font-family: Arimo; font-size: 15.4px;">I created a .NET solution to allow a user to select a Database, Record Type, and indicate whether Deleted attachments should be exported.</span><br />
<span style="background-color: white; color: #444444; font-family: Arimo; font-size: 15.4px;"><br /></span>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPoWNDM31eE6oSaqIBtyRTcsBFP13OQn9ca8U3pBMzFUcrVFpXfO2E_ih_VT5mexysZ9NIt19n2X6dBODkTiIM8rlae_5faMmzb-0CrsUyPZopytmutZ2pCsrjOEqyAmd_qslpNZGUy0ta/s1600/DocExport1.jpg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="505" data-original-width="770" height="209" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPoWNDM31eE6oSaqIBtyRTcsBFP13OQn9ca8U3pBMzFUcrVFpXfO2E_ih_VT5mexysZ9NIt19n2X6dBODkTiIM8rlae_5faMmzb-0CrsUyPZopytmutZ2pCsrjOEqyAmd_qslpNZGUy0ta/s320/DocExport1.jpg" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Export Dynamics GP Document Attachments</td></tr>
</tbody></table>
<br />
<span style="background-color: white; color: #444444; font-family: Arimo; font-size: 15.4px;">Once those options are selected, the user can retrieve a list of all of the attachments, which shows the type, the associated record number, the file name, and file size.</span><br style="background-color: white; color: #444444; font-family: Arimo; font-size: 15.4px;" /><br style="background-color: white; color: #444444; font-family: Arimo; font-size: 15.4px;" /><span style="background-color: white; color: #444444; font-family: Arimo; font-size: 15.4px;">The user can then select an export path and click a button to export all of the attachments to disk.</span><br />
<span style="background-color: white; color: #444444; font-family: Arimo; font-size: 15.4px;"><br /></span>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAxtSLyrZ8YDe28X4IQY9AyTdCm_owOL6-y8HT5jZRnSPdOskoOkFzK5Eq3XGkZLyJ01G6u_NOlLPjcXNKf9479ToPd1aKDJ8bo8gksMggP7zr8252zFaNJNVbMmkEXBMF1buUJlbV3vpG/s1600/DocExport2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="174" data-original-width="744" height="74" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAxtSLyrZ8YDe28X4IQY9AyTdCm_owOL6-y8HT5jZRnSPdOskoOkFzK5Eq3XGkZLyJ01G6u_NOlLPjcXNKf9479ToPd1aKDJ8bo8gksMggP7zr8252zFaNJNVbMmkEXBMF1buUJlbV3vpG/s320/DocExport2.jpg" width="320" /></a></div>
<span style="background-color: white; color: #444444; font-family: Arimo; font-size: 15.4px;"><br /></span>
<span style="background-color: white; color: #444444; font-family: Arimo; font-size: 15.4px;"><br /></span>Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-88611174135626128152018-11-26T13:13:00.000-08:002018-11-26T14:12:53.833-08:00New Blog at steveendow.comIf you are following this blog through an RSS feed or other automated system, please update your feed to:<br />
<br />
<a href="https://blog.steveendow.com/">https://blog.steveendow.com/</a><br />
<br />
<br />
I will no longer be posting to Dynamics GP Land, and all new posts will be at <b><a href="https://blog.steveendow.com/">https://blog.steveendow.com</a></b><br />
<br />
Thanks!Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-74179438589990630632018-10-04T13:03:00.001-07:002019-01-19T11:29:10.249-08:00I'm a kid in the Microsoft Candy Store<span style="color: red;">My blog has moved! Please visit the new blog at: <a href="https://blog.steveendow.com/">https://blog.steveendow.com/</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">I will no longer be posting to Dynamics GP Land, and all new posts will be at <a href="https://blog.steveendow.com/">https://blog.steveendow.com</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">Thanks!</span><br />
<br />
<br />
<br />
View the original post at: <a href="https://blog.steveendow.com/2018/10/im-kid-in-microsoft-candy-store.html">https://blog.steveendow.com/2018/10/im-kid-in-microsoft-candy-store.html</a><br />
<br />
<br />
By Steve Endow<br />
<br />
It's a fantastic time to be working with Microsoft products.<br />
<br />
Let's start with Microsoft Azure. Open this web page and read the list.<br />
<br />
<a href="https://azure.microsoft.com/en-us/services/">https://azure.microsoft.com/en-us/services/</a><br />
<br />
Just look at that list. It's pretty long, so you'll need to scroll down. And scroll some more. Keep scrolling. And scrolling.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEicpmTtse_YHkacMbOXdbTOwT4EuZoUZk__PRJItS9R0l7c2IS3bxvfwAHQkiyaEQLNZoRJMMvgd9JycBXS414kxHWAKqeXqmfsGSU8nATAmMIKgWaHoRowIV-gcSMg7pEW3ddWynwwaRgF/s1600/Azure1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="511" data-original-width="612" height="333" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEicpmTtse_YHkacMbOXdbTOwT4EuZoUZk__PRJItS9R0l7c2IS3bxvfwAHQkiyaEQLNZoRJMMvgd9JycBXS414kxHWAKqeXqmfsGSU8nATAmMIKgWaHoRowIV-gcSMg7pEW3ddWynwwaRgF/s400/Azure1.jpg" width="400" /></a></div>
<br />
<br />
It's a crazy long list. It's geek heaven. It's consultant heaven. So many amazing services to play with and learn and offer to customers.<br />
<br />
<br />
Next, there is the Power Platform.<br />
<br />
<a name='more'></a><br />
<br />
<a href="https://dynamics.microsoft.com/en-us/microsoft-power-platform/">https://dynamics.microsoft.com/en-us/microsoft-power-platform/</a><br />
<br />
<b>PowerApps</b>: An entire universe of opportunities for Citizen Developers to create applications that run in a browser and on mobile devices.<br />
<br />
<b>Power BI</b>: An amazing reporting, analysis, and data visualization tool.<br />
<br />
<b>Microsoft Flow</b>: A no-code solution to automate tasks and workflows that connects to dozens of systems and data sources.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEig5KinWYxgcegl18NIrpjCDwRRXke_wAEuTRngxaPcLocoJZBPum20a7n8IAg7XQKg4v2MhfZQvlCO9oUfMNMcxQWvFojzowc584Z9YAo9aeme6IKJSYFJadNgeB8Mtgkc1UucBvbD_jIs/s1600/PowerPlatform.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="269" data-original-width="784" height="136" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEig5KinWYxgcegl18NIrpjCDwRRXke_wAEuTRngxaPcLocoJZBPum20a7n8IAg7XQKg4v2MhfZQvlCO9oUfMNMcxQWvFojzowc584Z9YAo9aeme6IKJSYFJadNgeB8Mtgkc1UucBvbD_jIs/s400/PowerPlatform.jpg" width="400" /></a></div>
<br />
<br />
Is that enough for you? No? Oh, you want more?<br />
<br />
For those of us in the ERP world, there is an entire universe of functionality and learning available in the relatively new Dynamics 365 Business Central. The "D365 BC" SaaS ERP offering has most of the functionality from the Dynamics NAV product, but has been designed to work with the AL programming language, which is fully supported by the VS Code IDE tool.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://dynamics365cdn.azureedge.net/cvt-ca5132e95647d112e299ffae911cfb16527d297f45683a18bcee339802734d17/pictures/pages/business-central/TryPanel_1399x600.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="343" data-original-width="800" height="171" src="https://dynamics365cdn.azureedge.net/cvt-ca5132e95647d112e299ffae911cfb16527d297f45683a18bcee339802734d17/pictures/pages/business-central/TryPanel_1399x600.jpg" width="400" /></a></div>
<br />
<br />
So in addition to having an opportunity to learn a new ERP system with rich, advanced functionality, anyone who is technically inclined can learn the AL programming language and quickly develop basic Extensions for Business Central. At the recent Directions NA conference, several sessions had attendees creating custom pages in Business Central in just 1 hour. I was amazed at how easy it was to add custom pages to Business Central.<br />
<br />
<br />
And then there are APIs! The Business Central APIs are currently based on the <a href="https://github.com/Microsoft/api-guidelines/blob/vNext/Guidelines.md">Microsoft REST API guidelines</a>, which offer a powerful combination of REST with OData query syntax. But Microsoft is also working on adding support for the <a href="https://swagger.io/blog/news/announcing-openapi-3-0/">Open API Specification 3.0</a>, which will include Swagger support. If you haven't used a Swagger web API yet, you should definitely check it out--even if you aren't a developer. It lets you view and test the API directly from a web browser, making the API completely accessible to both developers and non-technical users alike.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhu0tzomVNbyjjIk1Droerbnbd-e46fvnsv0Ezg-MiFRVhmLQ7z03XMc7QZ8YOGVZLytPEn_inXf2kWAyRP-U375lPgxoOLbSg8t2ny-iJSWD0jTkZBsNOkL1ZNLCPOgJCdpTTsOxAxAE56/s1600/BCSwagger.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="753" data-original-width="1280" height="235" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhu0tzomVNbyjjIk1Droerbnbd-e46fvnsv0Ezg-MiFRVhmLQ7z03XMc7QZ8YOGVZLytPEn_inXf2kWAyRP-U375lPgxoOLbSg8t2ny-iJSWD0jTkZBsNOkL1ZNLCPOgJCdpTTsOxAxAE56/s400/BCSwagger.jpg" width="400" /></a></div>
<br />
<div style="text-align: center;">
Sorry for the blurry photo, it was a quick photo taken during a presentation.</div>
<br />
As if fantastic APIs weren't good enough, they are also working on adding Webhooks to Business Central. Webhooks are HTTP "callbacks", that enable an application to make outbound calls when certain events occur. Perhaps your ecommerce platform needs to know when a Business Central customer address is updated. Just register a webhook event with Business Central, and it will notify your ecommerce system.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiL7TH5flOmm0DWf0_SWS0MFGvLat8vm79c7Rbbks8wKkZj0NAKVM2t0_tr7rVkI_YClI2NSjCVlNsQRmWzNKUVM_3_vpm0VEKUqxUisykhzw_v5CtowmEs7-z9-pyVryBZGZNuyD0MEAsq/s1600/BCWebhooks1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1112" data-original-width="1600" height="222" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiL7TH5flOmm0DWf0_SWS0MFGvLat8vm79c7Rbbks8wKkZj0NAKVM2t0_tr7rVkI_YClI2NSjCVlNsQRmWzNKUVM_3_vpm0VEKUqxUisykhzw_v5CtowmEs7-z9-pyVryBZGZNuyD0MEAsq/s320/BCWebhooks1.jpg" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5ism9xoZuUxUN76KEoHWRj7IAFS47IAH0SlUtbtEOkvWJpd7fK8p5-ZWINqthQTAYwh75CUIqfap1rsHE5-ddFO-0LaZ92JRxXXPfxF7o_HMq01M3OyP6jt6bDkaXPMHiYZQsddFWpsY2/s1600/BCWebhooks2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="754" data-original-width="1280" height="188" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5ism9xoZuUxUN76KEoHWRj7IAFS47IAH0SlUtbtEOkvWJpd7fK8p5-ZWINqthQTAYwh75CUIqfap1rsHE5-ddFO-0LaZ92JRxXXPfxF7o_HMq01M3OyP6jt6bDkaXPMHiYZQsddFWpsY2/s320/BCWebhooks2.jpg" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfc1tfq79wX5FqqKc4kyC2GaqzUfmE1BaYqrSpJQnEKe9lLWewdYU5Bhvuzc2CgrHI5V49_V9kHr6AD1aCcyZ69_DgjwKqikbVERQmAFvsSpel08ysKlCpTXBZpUSVIARFB8quS357gYl5/s1600/BCWebhooks3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="727" data-original-width="1280" height="181" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfc1tfq79wX5FqqKc4kyC2GaqzUfmE1BaYqrSpJQnEKe9lLWewdYU5Bhvuzc2CgrHI5V49_V9kHr6AD1aCcyZ69_DgjwKqikbVERQmAFvsSpel08ysKlCpTXBZpUSVIARFB8quS357gYl5/s320/BCWebhooks3.jpg" width="320" /></a></div>
<br />
This solves a common problem in the ERP world which is difficult to solve with some ERP systems, such as Dynamics GP.<br />
<br />
And if that weren't enough for you, Microsoft Dynamics GP 2018 R2 was released this week, adding several new features, and support for the new "Intelligent Edge" service that will allow GP customers to upload their ERP data to Business Central to take advantage of the dashboards and analytical capabilities.<br />
<br />
<br />
There are massive learning opportunities from these Microsoft products and services, and no shortage of learning tools. From online documentation and tutorials, to YouTube videos, to the Dynamics Learning Portal, you can learn about these technologies any time, anywhere. And if you want to learn in person, you can attend conferences like Directions NA, Directions EMEA, Dynamics Communities Summit conference, Microsoft Business Applications Summit, Microsoft Ignite conference, and others.<br />
<br />
Tons of new tools and technologies to learn. Tons of opportunities and resources to help you learn.<br />
<br />
I'm very excited to be part of these new generation of Microsoft technologies, and can't wait to learn them!<br />
<br />
<i><br /></i><i>Steve Endow is a Microsoft MVP in Los Angeles. He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.</i><br />
<div class="MsoNormal" style="background: white;">
<br /></div>
<div class="MsoNormal" style="background: white;">
<i>You can also find him on </i><span style="color: #424242; mso-bidi-font-family: Arial; mso-bidi-font-size: 10.0pt;"><a href="https://twitter.com/steveendow"><i><span style="color: #9b8c45; text-decoration-line: none;">Twitter</span></i></a></span><span class="apple-converted-space"><i>, </i></span><span style="color: #424242; mso-bidi-font-family: Arial; mso-bidi-font-size: 10.0pt;"><a href="https://www.youtube.com/steveendow"><i><span style="color: #9b8c45; text-decoration-line: none;">YouTube</span></i></a></span><span class="apple-converted-space"><i>, and </i></span><span style="color: #424242; mso-bidi-font-family: Arial; mso-bidi-font-size: 10.0pt;"><a href="https://plus.google.com/108067157833601200392?rel=author"><i><span style="color: #9b8c45; text-decoration-line: none;">Google+</span></i></a><o:p></o:p></span></div>
<div class="MsoNormal" style="background: white;">
<br /></div>
<u1:p></u1:p><br />
<div class="MsoNormal" style="background: white;">
<span style="color: #424242; mso-bidi-font-family: Arial; mso-bidi-font-size: 10.0pt;"><a href="http://www.precipioservices.com/"><i><span style="color: #9b8c45; text-decoration-line: none;">http://www.precipioservices.com</span></i></a><o:p></o:p></span></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6FTJI-v2vyGiSugi4K3DavkA2HIZ8-Kf2Y963icwuz_ROQITjHrpthENoz5fUe6LOKElsTb4fJC2AKxu4WQu12Amtz4LkAfvtsmwJ0-nLmPTd0bJavL4DKr9e-VUfzzgkmie55XVjfiH1/s1600/MVP+MCITP+Logos+New.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="66" data-original-width="293" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6FTJI-v2vyGiSugi4K3DavkA2HIZ8-Kf2Y963icwuz_ROQITjHrpthENoz5fUe6LOKElsTb4fJC2AKxu4WQu12Amtz4LkAfvtsmwJ0-nLmPTd0bJavL4DKr9e-VUfzzgkmie55XVjfiH1/s1600/MVP+MCITP+Logos+New.jpg" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-5885190463963738302018-09-17T14:24:00.002-07:002019-01-19T11:29:33.819-08:00My Experience with ACH Fraud: My bank account was empty in 3 days<span style="color: red;">My blog has moved! Please visit the new blog at: <a href="https://blog.steveendow.com/">https://blog.steveendow.com/</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">I will no longer be posting to Dynamics GP Land, and all new posts will be at <a href="https://blog.steveendow.com/">https://blog.steveendow.com</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">Thanks!</span><br />
<br />
<br />
<br />
By Steve Endow<br />
<br />
<br />
<i><b>NOTE</b>: For readers outside the US, ACH stands for Automated Clearing House, which is an electronic payment system we use to deposit and withdraw funds from bank accounts. Employers often use ACH to electronically deposit pay checks into employee bank accounts, and companies often use ACH to pay their vendors electronically. Consumers often use ACH to pay their bills--if you want to automatically pay your cable TV or cell phone bill, you send the merchant your bank account information, and they automatically withdraw the funds each month from your bank account. In the US, it's a modern form of "electronic" banking. But for the rest of the world, I suspect it's an archaic, horribly designed system that has zero security.</i><br />
<br />
<br />
<br />
<i><b>Update:</b> On <a href="http://mpolino.com/gp/ach-fraud-steve-endow-had-a-rough-couple-of-days/">his blog post sharing this article</a>, Mark Polino says that there are solutions, similar to Safe Pay / Positive Pay, that can be used to prevent ACH fraud. While that may theoretically be true with some banks, certain types of business bank accounts, some corporate treasury management solutions, and for some payment scenarios (such as outbound payroll), I was told that Bank of America <b><u>Small Business</u></b> bank accounts have no such services that could be used to prevent the type of ACH fraud that I experienced.</i><br />
<i><br /></i>
<i>I asked two different Bank of America employees (one call center rep and one at my branch with over 15 years of experience as a manager at BofA) if there is <u>anything</u> I can do to prevent this type of ACH fraud. They both clearly and definitively said that there is <u>absolutely nothing</u> I can do to prevent such random fraudulent ACH transactions. </i><br />
<i><br /></i>
<i>I asked if they could block <u>all</u> ACH withdrawals on my account. The branch manager said no--he said that there is no way to prevent an ACH withdrawal from hitting my account. The only way he could block withdrawals from my account was the close the account. He did mention that he does have the ability to block ACH withdrawals from a specific merchant ID, such as those that occur with a recurring monthly fee, like a gym membership. But with the ACH fraud I experienced, there were multiple merchant IDs, so that would not have helped me.</i><br />
<i><br /></i>
<i>Trust me, I asked multiple times and pointed out how incredibly absurd the situation was. The Bank of America employees simply shrugged and said that the only solution is to close the compromised account and open a new one. It was a surreal experience.</i><br />
<br />
<br />
<br />
<i><b>Update 2:</b> Reviewing the Bank of America web site (since the employees were of no help), it appears that they have a "<a href="https://www.bankofamerica.com/smallbusiness/checking-accounts/full-analysis-business-checking.go#tab-1">Full Analysis Business Checking</a>" account offering that <u>might</u> have some relevant ACH fraud prevention features. If you maintain account balances of over $60,000, write more than 150 checks, and have more than 200 deposits a month (unclear if those transaction minimums are required), that type of account apparently offers "ACH blocks/authorizations", in addition to Positive Pay. </i><br />
<i><br /></i>
<i>Based on a review of <a href="https://www.bofaml.com/content/dam/boamlimages/documents/articles/ID15_154/ach_authorizations_blocks.pdf">this PDF form</a>, it appears that ACH blocks / authorizations allows you to "whitelist" specific ACH company IDs for your trading partners, authorizing them specifically, as well as specifically blocking certain company IDs. The form also has an option to completely block all ACH transactions against a specific account, something I was told was impossible with my account type.</i><br />
<i><br /></i>
<i>These ACH features might work for situations where you have consistent ACH deposits or withdrawals with trading partners on a specific account, but I don't know if it would be manageable for a company that is receiving many one time ACH payments from customers, or ACH payments from from hundreds of customers. You would need to know the ACH company ID for every customer in advance of their ACH deposit--I don't even know how I would find my own ACH company ID if I were asked for it. Any ACH transaction (deposit and withdrawal) that is not specifically whitelisted is blocked.</i><br />
<i><br /></i>
<i>And it isn't clear if they have an option to manage the company IDs online, or if you have to fill out that form for every change.</i><br />
<i><br /></i>
<i>I currently have no need to park $60,000 in my business bank accounts, so such account features are presumably not available to me.</i><br />
<i><br /></i>
<i>Why can't banks allow me to approve each ACH transaction before it hits my account? Allow me to login to the online banking web site or mobile app, view a list of pending transactions, and approve or deny each one? This isn't rocket science. If the ACH platform cannot support such a workflow, the US banking system is truly the laughingstock of the modern world.</i><br />
<br />
<br />
<br />
<i><b>Update 3:</b> I had to call the bank to get copies of the recent statements for my closed account, since I no longer have access to the account online. During the call, I asked this new rep if there were any options available to prevent the ACH fraud I experienced. He indicated that he is not aware of any features on my Small Business account that would have prevented the fraud, but he mentioned that the bank can place a "Fraud Hold" on an account. This is the first time I had heard of such an option, despite asking about it repeatedly previously. The Fraud Hold results in an account balance of -$888,888.88, which is an indicator to Bank of America folks that the account has been placed on hold. </i><br />
<i><br /></i>
<i>Unfortunately, this rep, and one more Small Business sales rep I spoke with during this call resulted in no additional information or potential services that could have helped me to prevent the ACH fraud. In fact, the sales rep had never heard of an actual case of ACH fraud, so I ended up educating him about the process, and he was shocked by the lack of resources and the process required to resolve the problem.</i><br />
<i><br /></i>
<i>I asked about the "Full Analysis Business Checking" account type, but neither rep had any knowlege of it, as it is apparently handled by a different Treasury Management group that cannot be called directly. I had to request that this secret department give me a call, as a potential sales prospect for their services. And the saga continues...</i><br />
<i><br /></i>
<i><br /></i>
<i><br /></i>
<br />
<br />
I checked my email on Friday morning and saw a pretty standard email alert from my bank.<br />
<br />
<br />
<div class="MsoPlainText">
<span style="font-family: "georgia" , "times new roman" , serif;">Hi Steve, an electronic withdrawal was made above your
chosen alert limit:<o:p></o:p></span></div>
<div class="MsoPlainText">
<br /></div>
<div class="MsoPlainText">
<span style="font-family: "georgia" , "times new roman" , serif;">Amount: $719.60<o:p></o:p></span></div>
<div class="MsoPlainText">
<span style="font-family: "georgia" , "times new roman" , serif;">Type: ELEC DRAFT (ACH)<o:p></o:p></span></div>
<div class="MsoPlainText">
<span style="font-family: "georgia" , "times new roman" , serif;">Account: Business Account *******1234<o:p></o:p></span></div>
<div class="MsoPlainText">
<span style="font-family: "georgia" , "times new roman" , serif;">Merchant: CHASE CREDIT CRD EPAY<o:p></o:p></span></div>
<br />
<div class="MsoPlainText">
<span style="font-family: "georgia" , "times new roman" , serif;">Transaction date: September 07, 2018</span><o:p></o:p></div>
<br />
<br />
Hmmm, that's odd.<br />
<br />
I don't pay my credit card using ACH. And I definitely don't pay my credit card from that particular business bank account.<br />
<br />
I thought to myself: <i>It's finally happening. I've been waiting for it to happen for years, and now it is actually happening.</i><br />
<br />
By Wednesday morning, my bank account was <u>completely empty</u> due to fraudulent ACH withdrawals.<br />
<br />
<a name='more'></a><br />
<br />
<br />
As soon as I realized what was happening on Friday morning, I called my bank and sat on hold for an HOUR. I finally spoke with a nice man who also immediately recognized the problem.<br />
<br />
Yup, my account is being used for ACH fraud.<br />
<br />
Here's a good article explaining the details:<br />
<br />
<a href="https://www.csoonline.com/article/2125833/cyber-attacks-espionage/malware-cybercrime-ach-fraud-why-criminals-love-this-con.html">https://www.csoonline.com/article/2125833/cyber-attacks-espionage/malware-cybercrime-ach-fraud-why-criminals-love-this-con.html</a><br />
<br />
<br />
If you have ever used your business or personal bank accounts to make ACH payments, or receive ACH payments or wire transfers, you are at risk for ACH fraud. (I suppose there could be some risk even if you never use ACH, but I suspect the risk is quite low.)<br />
<br />
Even better: If you have ever written a check, your account is at risk for ACH fraud.<br />
<br />
As soon as you hand your bank account number and routing number to anyone, those two numbers can be used to drain your bank account without your approval.<br />
<br />
Most of the time, the companies that receive your bank account information are trustworthy and deposit or withdraw funds appropriately. But if those two numbers for your bank account are ever compromised, funds can be withdrawn from your account. And there is very little you can do to prevent it.<br />
<br />
Fortunately, I was conceptually aware of this, so years ago I setup a dedicated business account I use exclusively for ACH and wire transfer transactions. And I have a separate savings account into which I can 'sweep' funds, allowing to keep my ACH account balance relatively low to minimize risk.<br />
<br />
In that sense, I was prepared for ACH fraud, but since this is the first time I've actually experienced it, it was a bit stressful.<br />
<br />
<br />
Here are some things I learned.<br />
<br />
Banks behave completely differently than credit card companies when it comes to fraud.<br />
<br />
My credit card company constantly monitors my cards for potential fraud, and they proactively contact me if they detect any fraud. Once fraud is detected, they swiftly shut down the credit card account, immediately credit me for any fraudulent transactions, and promptly mail me a new credit card. In my experience, they have excellent customer service and handle everything.<br />
<br />
My bank, on the other hand, demonstrated that it clearly doesn't care about ACH fraud. The online banking web site offers no options for me to flag transactions as fraud or report the fraud to the bank.<br />
<br />
In order to report ACH fraud, you have to download a PDF form, fill it out, sign it, and the FAX it to the bank. Yes, that is correct--the ONLY way to submit the fraud claim form is via fax. You cannot submit it online. You cannot email it. You can't even mail it. Fax only. That's a pretty clear demonstration of how much my bank cares about ACH fraud.<br />
<br />
The form has no telephone number on it either. So there is no way to call the special ACH fraud department to confirm receipt or check on the status.<br />
<br />
So I naively filled out the form and found a free online fax service I could use to send the fax.<br />
<br />
On Monday morning, the second fraudulent transaction appeared. This told me that this wasn't an accident, and that my account was definitely being targeted. So I made an appointment at my bank for that afternoon.<br />
<br />
I met with the business accounts rep at my bank, and he wasn't the least bit surprised. It happens all the time. And no, there is nothing you can do to prevent it.<br />
<br />
So I spent an hour at the bank while he setup a new business checking account for me.<br />
<br />
Problem solved, right?<br />
<br />
Nope.<br />
<br />
You see, the ACH fraud form requires transaction ID information. Apparently my bank may not process the form for "pending" transactions. Because the second fraudulent ACH transaction was still pending, the bank employee recommended I keep the account open until the transaction cleared so that I could record the transaction ID and submit the second claim form.<br />
<br />
Bad advice.<br />
<br />
Guess what I saw in my Inbox on Tuesday morning?<br />
<br />
Yup, another fraudulent ACH transaction.<br />
<br />
So I filled out the second ACH claim form with the necessary transaction ID information from Monday's transaction, and made another appointment for the bank.<br />
<br />
During my second appointment, the rep agreed that the decision to not close the account was a mistake, so we agreed that the account should be closed.<br />
<br />
To close the account, I had to actually put money INTO the account to cover the overdraft from the most recent fraudulent transaction, as apparently the account cannot be closed with a negative balance. So I transferred funds to the account, and it was finally closed.<br />
<br />
Kind of.<br />
<br />
You see, because my bank apparently has a sense or humor, if one of the fraudulent ACH transactions gets rejected, the funds that were on hold will be deposited back into my closed bank account. And the bank account will AUTOMATICALLY REOPEN. Yes, you read that right. I stared at the bank employee in disbelief as he explained this to me. I literally started laughing at the absurdity of this. The bank employee was so indoctrinated with these procedures that he completely failed to recognize why I was laughing.<br />
<br />
But it gets better. Once the account is closed, I will no longer see it in my online banking, and I will be unable to view the information for the most recent fraudulent transaction required to fill out the third ACH fraud claim form. So that means that I have to make yet another trip to the bank to have the bank employee look up the transaction information and fill out the form for me.<br />
<br />
But, thankfully (I think?), all of the fraudulent transactions cleared and my account remained closed. After one more visit to the bank, all three ACH fraud claims were submitted, and on Friday the bank employee called the top secret ACH fraud department, which confirmed that all 3 claims were received.<br />
<br />
He emailed me to give me the update, and let me know that ACH fraud claims typically take 10 days to be processed from the date of receipt of the claim. Something that the fraud claim form fails to explain.<br />
<br />
<br />
So, let me summarize:<br />
<br />
1. Based on my experience, there is nothing you can do to prevent ACH fraud on your bank account, other than to never share the bank account number. Once ACH fraud occurs, your only option is to close the bank account. (Maybe there are some banks that offer ACH fraud prevention services, but mine did not)<br />
<br />
2. By the time you realize fraudulent transactions are occurring, submit claim forms, and close your account, your bank account may be completely empty. And you may even have to deposit funds to cover overdraft, and you may also be charged overdraft fees.<br />
<br />
3. You will need to setup a new account and make sure to transfer any auto pay / ACH transactions to the new account<br />
<br />
4. It can take at least 10 days to get your money back from the bank<br />
<br />
<br />
This process may vary by bank, but this is the fun experience I had with one of the largest banks in the US.<br />
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><i>Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</i><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<i>You can also find him on </i><a href="https://twitter.com/steveendow"><i>Twitter</i></a><span class="apple-converted-space"><i>, </i></span><a href="https://www.youtube.com/steveendow"><i>YouTube</i></a><span class="apple-converted-space"><i>, and </i></span><a href="https://plus.google.com/108067157833601200392?rel=author"><i>Google+</i></a> <o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="http://www.precipioservices.com/"><i>http://www.precipioservices.com</i></a><o:p></o:p></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s1600/MVP+MCITP+Logos+New.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="103" data-original-width="451" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s320/MVP+MCITP+Logos+New.jpg" width="320" /></a></div>
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-70241571336488807022018-07-02T17:46:00.000-07:002019-01-19T11:29:46.709-08:00Move Forward, Every Day. And Bring Chocolate Cake.<span style="color: red;">My blog has moved! Please visit the new blog at: <a href="https://blog.steveendow.com/">https://blog.steveendow.com/</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">I will no longer be posting to Dynamics GP Land, and all new posts will be at <a href="https://blog.steveendow.com/">https://blog.steveendow.com</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">Thanks!</span><br />
<br />
<br />
By Steve Endow<br />
<br />
<br />
<b>Change Is Hard</b><br />
<br />
It's trite, I know. But it doesn't make it less true.<br />
<br />
I think it's worthwhile to pause and take a moment to consider how difficult change can be. Actually acknowledge it.<br />
<br />
Change can be uncomfortable, scary, and stressful. Change can be costly and arduous and tiring. Change can be inherently destructive.<br />
<br />
It often feels much easier to ignore it, delay it, or pretend it isn't happening. But you can't always avoid change. Sometimes you have time to prepare for change, and sometimes you have no advance notice.<br />
<br />
In the ERP world, consultants see the symptoms of change regularly when customers implement a new ERP system.<br />
<br />
"My old system did X, why can't the new system do X?"<br />
<br />
"I just don't have time to learn this new system. I have too much work to do!"<br />
<br />
"I liked the old system better."<br />
<br />
<br />
<br />
<b>Turnabout is Fair Play</b><br />
<br />
But occasionally, it seems that those pesky consultants get a taste of change.<br />
<br />
Microsoft has changed quite a bit since it acquired Great Plains Software. Great Plains was rebranded as Dynamics GP, and the GP team at Microsoft put a lot of work into the product, adding new features and expanding its capabilities.<br />
<br />
But over the last several years, Microsoft has invested heavily in its 'cloud computing' strategy, with Azure and Software as a Service (SaaS) offerings. As part of the cloud strategy, Microsoft has also invested heavily in ERP software as a service, hosted in Azure.<br />
<br />
Dynamics AX has become Dynamics 365 Finance and Operations, and Dynamics NAV has now, finally, become Dynamics 365 Business Central. Dynamics GP is notably not on the Microsoft Azure ERP SaaS menu. It's our turn to change.<br />
<br />
<br />
<a name='more'></a><br />
<br />
<br />
<b>Ahh, Memories</b><br />
<br />
I've worked with Dynamics GP since 2004. After you work with a product for fourteen years, you get to know it very well. You know it so well that you develop an intuition around the product. It's comfortable. It's familiar. It's stable. You have access to all of the resources and tools you need to work with it. You know how to solve problems. You know how to help people. And you know how to make a living working with it.<br />
<br />
But, it's now 2018, and change is upon us.<br />
<br />
And change is hard. (Did I mentioned that already?)<br />
<br />
Despite all of my knowledge of Dynamics GP, learning Dynamics 365 Business Central feels like starting from scratch. Yes, it's "just" a different flavor of ERP software, but so many things are different. The licensing, the login, the user interface, the terminology, the architecture, the data model, the reporting, the security, the customization, and the development tools. It's all new, and feels very foreign.<br />
<br />
Even after working with Dynamics GP for 14 years, I'm <u>still</u> constantly learning new things about GP, so I am now wondering: How long it is going to take me to become minimally competent with Dynamics 365 Business Central? And then how long to become proficient? And how long to become an 'expert'?<br />
<br />
Months? A year? Multiple years? A decade?<br />
<br />
How many hours need to be invested to reach those milestones? Certainly more than a few hundred for minimally competent. A thousand or more for proficiency? Several thousand to approach expert?<br />
<br />
But I can't just drop everything that I'm doing and study Dynamics 365 Business Central full time. I still need to "work", make money, and continue to support my Dynamics GP customers.<br />
<br />
And then there's my Microsoft MVP status, which requires that I continue to constantly learn, blog, speak at conferences, and participate in community events. And even the MVP program recently underwent a significant change, with a <a href="https://eightwone.com/2018/07/02/mvps-around-the-world-3/">dramatic reduction in renewals for 2018</a> for reasons that are not yet understood outside of Microsoft. So that's another significant change that I'm having to navigate.<br />
<br />
<br />
<b>So now what?</b><br />
<br />
So now that we've acknowledged that change is hard, what do we do?<br />
<br />
Go get a giant piece of chocolate cake and binge watch Netflix in denial? (actually, that sounds very appealing, and tasty, even without the side dish of denial)<br />
<br />
Complain that "it isn't fair"?<br />
<br />
Have anxiety attacks about how we're going to pull off reinventing ourselves and our careers and our income? (Can this be avoided? Anyone know? I'm open to suggestions.)<br />
<br />
<br />
These are probably not great strategies (except for the chocolate cake, I'm pretty sure that's a solid plan).<br />
<br />
The world is moving forward and it isn't stopping for me.<br />
<br />
<br />
<b>Moving Forward</b><br />
<br />
Once the chocolate cake is consumed and it has fortified and steeled you for the journey ahead, it's time to move forward.<br />
<br />
Think about what you need to do to move forward. Act to move forward.<br />
<br />
Sign up for a Dynamics 365 reseller account with an MSP. Sign up for a training class.<br />
<br />
Review the dozens of online courses available on the Microsoft Dynamics Learning Portal.<br />
<br />
Buy an actual paper book and break out the highlighters.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnMsAILskm2LMRr-bjyqNqP-t8uMlmovvP7t6W14wKa9cukOULAWcLX7BuH9mpG-raTmgDWi27ryenPHcVK_e-ACspbzV04bTE-5Y7YH6wVrhns5wOT2nYUu0vx4bEZZYghs0p8xC2fKqF/s1600/ProgrammingNAV.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="488" data-original-width="491" height="317" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnMsAILskm2LMRr-bjyqNqP-t8uMlmovvP7t6W14wKa9cukOULAWcLX7BuH9mpG-raTmgDWi27ryenPHcVK_e-ACspbzV04bTE-5Y7YH6wVrhns5wOT2nYUu0vx4bEZZYghs0p8xC2fKqF/s320/ProgrammingNAV.jpg" width="320" /></a></div>
<br />
Register for a new conference.<br />
<br />
Find a new user group. Lurk on new forums. Follow new people on Twitter. Sign up for new newsletters.<br />
<br />
Start making commitments that move you forward. See opportunities instead of burdens. Start moving in a new direction instead of fighting to stand still.<br />
<br />
Invest in your future.<br />
<br />
<br />
<b>Learn</b><br />
<br />
If you're an ERP consultant, you almost certainly know how to learn. The learning is non-stop, and there is always something to learn. It's a prerequisite for being a good consultant.<br />
<br />
So just start learning something new, a little bit at a time at first. Instead of researching that odd Dynamics GP quirk out of curiosity, redirect that learning into Dynamics 365 Business Central.<br />
<br />
While researching a Dynamics GP ISV solution, also research similar ISV solutions for Business Central.<br />
<br />
It will be slow going at first, and even though it may seem like an insurmountable pile of knowledge to consume, just start the learning process. Start at the beginning.<br />
<br />
Set aside an hour a day to work on an online course. Or better yet, reserve Fridays for learning. It won't be easy to do, but with practice, it can become a habit.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3LrkaDdRMlC47dYnzCykbtcRl-x6hHW7pFIxgrur8PfuclxWOFaARNeXsRdUYBLFHup8bbiNzO-M2fvVP9sBwFmiWwShraUg9WEQytsedg-JVBOn78Oym1m3nQ1_LBHmV8-gY1AnOMGrN/s1600/ProgrammingNAV2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="540" data-original-width="614" height="281" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3LrkaDdRMlC47dYnzCykbtcRl-x6hHW7pFIxgrur8PfuclxWOFaARNeXsRdUYBLFHup8bbiNzO-M2fvVP9sBwFmiWwShraUg9WEQytsedg-JVBOn78Oym1m3nQ1_LBHmV8-gY1AnOMGrN/s320/ProgrammingNAV2.jpg" width="320" /></a></div>
<br />
<br />
Set aside some quiet time. Open the book. Start reading.<br />
<br />
<br />
<b>Be Curious</b><br />
<br />
Learning doesn't have to be arduous. It doesn't have to be unpleasant. It doesn't have to be hard work.<br />
<br />
Learning can be fun. You know those times when you figure out the solution to some puzzling Dynamics GP issue or have an exciting epiphany? Do the same with your new learning.<br />
<br />
Ask a few simple questions and try to find the answers.<br />
<br />
Do some sleuthing to figure out how to perform a familiar Dynamics GP process in Dynamics 365.<br />
<br />
Notice the differences. See the pros and cons. See the benefits and limitations. The strengths and weaknesses.<br />
<br />
Take the years of experience implementing ERP systems for mid-market customers and use that as a filter to find new value that you can offer. Anticipate the concerns and challenges your new customers are going to face. Think about if, and how, your Dynamics GP customers could eventually transition to this new world that you are exploring.<br />
<br />
You'll hear many say that it isn't possible--that Dynamics GP customers with several ISV solutions simply can't migrate to the cloud. Accept that as a challenge.<br />
<br />
<br />
<b>Prepare a Reserve</b><br />
<br />
What if it takes 12 months of part time learning to become proficient? What if it takes 3 years to start to become an "expert"? What if you have to spend extra money to take a few classes and attend a few new conferences?<br />
<br />
What if you have to allocate a day a week to learning instead of producing revenue?<br />
<br />
What if your software sales decline during that time? What if your consulting revenue decreases significantly?<br />
<br />
How long will it take to bring in new customers? How long will it take to build a new practice while maintaining existing GP customers?<br />
<br />
That process will likely be stressful enough, so it is probably prudent to have financial reserves to weather it. Anticipate it. Prepare for it. Don't be surprised by it.<br />
<br />
<br />
<b>New Frontiers</b><br />
<br />
It's a new frontier for Dynamics GP partners and consultants. Some partners started the transition to work with other non-Microsoft products several years ago. Other partners have been building Dynamics 365 Finance & Operations practices for the last few years. And some partners invested in the product formerly known as Dynamics 365 Business Edition and are now adapting to the new Business Central offering. Many will be late to transition. All of us will be learning and adapting.<br />
<br />
Independent of accruing product knowledge and competence, the revenue model has also changed. All the training and learning in the world won't make up for the change from perpetual license sales and generous margins to the new subscription licensing model and reduced partner margins. The steady stream of annual enhancement is now replaced with the smaller revenue stream from customer subscriptions.<br />
<br />
Will this lead to more emphasis on consulting hours and service revenues? Will this new revenue regime put pressure on partners and force consolidation?<br />
<br />
Will the accessibility of Dynamics 365 commoditize mid-market ERP software as well as ERP partners and consultants? Or will customers attempting to purchase Dynamics 365 directly from an MSP encounter difficulties and realize the value that knowledgeable consultants can offer?<br />
<br />
Will the walled garden of SaaS ERP, with restricted access to the back end data and processes produce frustrations for former on-prem customers? Will that in turn produce new opportunities for products, services, and clever workarounds to meet the needs of customers? (Hint: Yes)<br />
<br />
<br />
<b>I Don't Know</b><br />
<br />
How will I fare? Where will I be in 1 year? 3 years? 5 years?<br />
<br />
How will the mid-market accept Dynamics 365 Business Central?<br />
<br />
Will Dynamics GP customers transition to Business Central? Or will they seek other options? How will Business Central compete against other mid-market options, particularly in the US? Will competitive pricing be enough to entice customers to transition?<br />
<br />
I don't know.<br />
<br />
But it seems like Microsoft is investing in Dynamics 365. It seems like they finally have a semi-coherent, albeit currently incomplete, strategy with Dynamics 365 Business Central.<br />
<br />
Regardless, Microsoft is moving forward. Every day.<br />
<br />
For the time being, I'm willing to move forward and continue to follow them.<br />
<br />
<br />
And I'll definitely be bringing chocolate cake.<br />
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<div style="margin-bottom: .0001pt; margin: 0in;">
<i>Steve Endow is a Microsoft MVP in Los Angeles. He is
the owner of Precipio Services, which provides Dynamics GP integrations,
customizations, and automation solutions.</i><o:p></o:p></div>
</div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: inherit;"><br /></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: inherit;"><i>You can also find him on<span class="apple-converted-space"> </span></i><a href="https://plus.google.com/108067157833601200392?rel=author"><i>Google+</i></a><span class="apple-converted-space"><i> </i></span><i>and<span class="apple-converted-space"> </span></i><a href="https://twitter.com/steveendow"><i>Twitter</i></a><o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: inherit;"><br /></span></div>
<span style="font-family: inherit;"><br /></span>
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<i><span style="font-family: inherit;"><a href="http://www.precipioservices.com/">http://www.precipioservices.com</a></span></i><span style="font-size: 13.5pt;"><o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<i><br /></i></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s1600/MVP+MCITP+Logos+New.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="103" data-original-width="451" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s320/MVP+MCITP+Logos+New.jpg" width="320" /></a></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<i><br /></i></div>
<br />
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-37202707713372003132018-07-02T12:29:00.004-07:002019-01-19T11:30:28.738-08:00Free GP Transaction Search v2.0 now available with new RM and SOP search windows!<span style="color: red;">My blog has moved! Please visit the new blog at: <a href="https://blog.steveendow.com/">https://blog.steveendow.com/</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">I will no longer be posting to Dynamics GP Land, and all new posts will be at <a href="https://blog.steveendow.com/">https://blog.steveendow.com</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">Thanks!</span><br />
<br />
<br />
<br />
By Steve Endow<br />
<br />
Version 2.0 of the FREE Dynamics GP Transaction Search is now available!<br />
<br />
A big thanks to <a href="http://www.azurecurve.co.uk/">Ian Grieve</a> for assistance with the development and testing of this new version!<br />
<br />
<br />
For more information and to download compiled releases:<br />
<br />
<div style="text-align: center;">
<a href="https://precipioservices.com/free/gp-transaction-search/">https://precipioservices.com/free/gp-transaction-search/</a></div>
<br />
<br />
Here is a brief video showing the simple installation process and an overview of the search windows:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/08WZOhKWlQQ/0.jpg" frameborder="0" height="266" src="https://www.youtube.com/embed/08WZOhKWlQQ?feature=player_embedded" width="320"></iframe></div>
<br />
<div style="text-align: center;">
<a href="https://www.youtube.com/watch?v=08WZOhKWlQQ">https://www.youtube.com/watch?v=08WZOhKWlQQ</a></div>
<div style="text-align: center;">
<br /></div>
<br />
The latest source code is available on GitHub:<br />
<br />
<div style="text-align: center;">
<a href="https://github.com/steveendow/gp-transaction-search">https://github.com/steveendow/gp-transaction-search</a></div>
<br />
<br />
This new release includes two new search windows:<br />
<br />
<ul>
<li>RM Transaction Search</li>
<li>SOP Transaction Search</li>
</ul>
<br />
<a name='more'></a><br />
<br />
Also, version 2 uses David Musgrave's <a href="https://www.winthropdc.com/products_VSIT.htm">Visual Studio Integration Toolkit</a> to create dedicated custom menus for each search window. (must be downloaded and installed separately)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPQb1DB3lTx8EFKcDZy0EWdQbacQd5WFjVZ-mWSh3kPKyLiDw8YDJN7MrF15tU2fdGh-L_bbQNhgWwbrWkQw18-HUMiZhLhHgeOGhSlOZOm-6XyHHT6gTmF1kiv4ctD9rcn4rYisZplnDy/s1600/SearchCustomMenus.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="199" data-original-width="492" height="161" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPQb1DB3lTx8EFKcDZy0EWdQbacQd5WFjVZ-mWSh3kPKyLiDw8YDJN7MrF15tU2fdGh-L_bbQNhgWwbrWkQw18-HUMiZhLhHgeOGhSlOZOm-6XyHHT6gTmF1kiv4ctD9rcn4rYisZplnDy/s400/SearchCustomMenus.jpg" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEnd1rhJJA3-X-6i3Pv4-LaqcFKGpjlEFLpN-RYapqWPl9_JitX6JVY7GetaazoCxJkje6gM79fPaBXJS1jXqwiibL0-2pcWPo5WH3n6hFLKbz8ab-3XAuh9ROIwLqBxbFW9hmkBAt89Y4/s1600/SearchCustomMenus2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="205" data-original-width="495" height="163" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEnd1rhJJA3-X-6i3Pv4-LaqcFKGpjlEFLpN-RYapqWPl9_JitX6JVY7GetaazoCxJkje6gM79fPaBXJS1jXqwiibL0-2pcWPo5WH3n6hFLKbz8ab-3XAuh9ROIwLqBxbFW9hmkBAt89Y4/s400/SearchCustomMenus2.jpg" width="400" /></a></div>
<br />
The menus also appear on the Sales and Purchasing landing pages in GP.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgoyNErg5TqGgvj_g7n2ZnDEscvVn8_znvH99SmNfOZK5vn3_7IjwrLQMI_Cacbm7J7vv8kOqbCFvNfHmUhdw2qqBghqpl1EAbGsAIkV1ftAu8rPf98W0Xtk8F2oU5qYI96c2ObKtDwG2sK/s1600/SalesLanding.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="269" data-original-width="343" height="249" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgoyNErg5TqGgvj_g7n2ZnDEscvVn8_znvH99SmNfOZK5vn3_7IjwrLQMI_Cacbm7J7vv8kOqbCFvNfHmUhdw2qqBghqpl1EAbGsAIkV1ftAu8rPf98W0Xtk8F2oU5qYI96c2ObKtDwG2sK/s320/SalesLanding.jpg" width="320" /></a></div>
<br />
<br />
Instead of having to click on the new menu items, you can simply press a keyboard shortcut key.<br />
<br />
<div style="text-align: center;">
<b>Ctrl + Shift + P</b> = PM Transaction Search</div>
<div style="text-align: center;">
<br /></div>
<div style="text-align: center;">
<b>Ctrl + Shift + R</b> = RM Transaction Search</div>
<div style="text-align: center;">
<br /></div>
<div style="text-align: center;">
<b>Ctrl + Shift + S</b> = SOP Transaction Search</div>
<br />
<br />
For customers outside of the US, the configuration file now includes a setting to change the name of Customer and Vendor to Debtor and Creditor if desired.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0-WR8nLw66kk1YqDKs92DdlF5NWj7exDVJ4TKyrcecNvPePk15lwBlc4GZQIzwibz-dXx1_uaCSsURLhkrp1cLxi3BQ2r36QNqpEqUq_xetO_BF1mlmvnFhvasDd8BUEFhZ7XfygRozcL/s1600/PMCreditor.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="220" data-original-width="678" height="127" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0-WR8nLw66kk1YqDKs92DdlF5NWj7exDVJ4TKyrcecNvPePk15lwBlc4GZQIzwibz-dXx1_uaCSsURLhkrp1cLxi3BQ2r36QNqpEqUq_xetO_BF1mlmvnFhvasDd8BUEFhZ7XfygRozcL/s400/PMCreditor.jpg" width="400" /></a></div>
<br />
<br />
The RM Transaction Search window is very similar to the PM Transaction Search window, with filters on date, customer, document number, and amount.<br />
<br />
However, the SOP Transaction Search includes a combined filter for Item # and Item Name.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6-E43lNZXMzg4KIEQkZAypxDqEViC50cXwcFgTqzYFnvEeshkg1e70btxD7ceyqaNDwlkyZhxT6nwQIINKajw8A7tLcG5bjSWVOX7-Zsm3uT3xxC5ATD6t78tUwV6XtWNsT86rs22DKcv/s1600/SOPSearch.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="484" data-original-width="1068" height="181" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6-E43lNZXMzg4KIEQkZAypxDqEViC50cXwcFgTqzYFnvEeshkg1e70btxD7ceyqaNDwlkyZhxT6nwQIINKajw8A7tLcG5bjSWVOX7-Zsm3uT3xxC5ATD6t78tUwV6XtWNsT86rs22DKcv/s400/SOPSearch.jpg" width="400" /></a></div>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><span style="background-color: white; color: #424242;">If you are a .NET developer or a Dexterity developer and are interested in helping enhance GP Transaction Search, </span><a href="https://precipioservices.com/contact-us/" style="background-color: white; color: #9b8c45;">please contact me</a><span style="background-color: white; color: #424242;">!</span></span><br />
<span style="font-family: inherit;"><br style="background-color: white; color: #424242;" /></span>
<span style="font-family: inherit;"><span style="background-color: white; color: #424242;">If you have any questions about the search tool, or if you have used it in a real GP environment and have any suggestions for improving it, </span><a href="https://precipioservices.com/contact-us/" style="background-color: white; color: #9b8c45;">please contact me</a><span style="background-color: white; color: #424242;">!</span></span><br />
<span style="font-family: inherit;"><br style="background-color: white; color: #424242;" /></span>
<span style="font-family: inherit;"><br style="background-color: white; color: #424242;" /></span>
<a href="https://precipioservices.com/contact-us/" style="background-color: white; color: #9b8c45;"><span style="font-family: inherit;">https://precipioservices.com/contact-us/</span></a><br />
<span style="font-family: inherit;"><br /></span>
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</span></i></span><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">You can also find him on </span></i></span></span><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="https://twitter.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Twitter</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, </span></i></span></span></span><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="https://www.youtube.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i style="mso-bidi-font-style: normal;"><span style="mso-bidi-font-size: 13.5pt;">YouTube</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, and </span></i></span></span></span><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="https://plus.google.com/108067157833601200392?rel=author"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Google+</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"> <o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="http://www.precipioservices.com/"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">http://www.precipioservices.com</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s1600/MVP+MCITP+Logos+New.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="103" data-original-width="451" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s320/MVP+MCITP+Logos+New.jpg" width="320" /></a></div>
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com1tag:blogger.com,1999:blog-6691994129222744759.post-57861362962170847482018-06-11T10:25:00.004-07:002019-01-19T11:23:27.837-08:00Opening Dynamics GP Windows using .NET and Visual Studio ToolsMy blog has moved! Please visit the new blog at: <a href="https://blog.steveendow.com/">https://blog.steveendow.com/</a><br />
<br />
I will no longer be posting to Dynamics GP Land, and all new posts will be at <a href="https://blog.steveendow.com/">https://blog.steveendow.com</a><br />
<br />
Thanks!<br />
<br />
<br />
<br />
By Steve Endow<br />
<br />
When you develop a Dynamics GP customization, you may want to automatically open Dynamics GP windows to add dramatic flair to your application.<br />
<br />
I mean really, let's be honest, what's cooler than saying your customization lets the user "drill down" into Dynamics GP? (Is that still a buzzword? I may be behind the times)<br />
<br />
Take the GP Transaction Search tool as an example. When a user searches for an AP transaction, I thought it would be handy if the user could right click on a row and view both vendor information and transaction information directly in Dynamics GP.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj5DNLbrDmVTPrYWO_U4u7UDQ3BcvN14JXIJKOWpR7VxYDaMki0-RHS2kIPwR1tKcXSdUCcPE6yNczfficJCbUDHZJJ2M5BhdwNSKqaDZMhDSm2dLiowvbuqP5aIjardyFasyxb6RB_Klso/s1600/PMSearchDrillDown.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="325" data-original-width="500" height="208" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj5DNLbrDmVTPrYWO_U4u7UDQ3BcvN14JXIJKOWpR7VxYDaMki0-RHS2kIPwR1tKcXSdUCcPE6yNczfficJCbUDHZJJ2M5BhdwNSKqaDZMhDSm2dLiowvbuqP5aIjardyFasyxb6RB_Klso/s320/PMSearchDrillDown.jpg" width="320" /></a></div>
<br />
Just because I developed the PM Transaction Search window doesn't mean that I want to (or need to) develop a new window just to view vendor info or transaction info.<br />
<br />
Why not just leverage Dynamics GP windows to view that information?<br />
<br />
I want the user to be able to right click or double click on a row in the grid and view the details in an existing Dynamics GP window.<br />
<br />
Here's a full video with a code review of opening GP windows from .NET using Visual Studio Tools:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/tQwTEGxw-S0/0.jpg" frameborder="0" height="266" src="https://www.youtube.com/embed/tQwTEGxw-S0?feature=player_embedded" width="320"></iframe></div>
<br />
<div style="text-align: center;">
<a href="https://www.youtube.com/watch?v=tQwTEGxw-S0">https://www.youtube.com/watch?v=tQwTEGxw-S0</a></div>
<br />
<br />
Fortunately, adding this drill-down functionality to a VS Tools AddIn for Dynamic GP is relatively easy. <b>For some windows.</b><br />
<br />
For other windows, it requires a slightly more involved process.<br />
<br />
And, unfortunately, for some windows, it may be very difficult or impossible.<br />
<br />
<a name='more'></a><br />
<br />
Let's start with the easy method.<br />
<br />
These samples are from the free and open source code for the GP Transaction Search project, <a href="https://github.com/steveendow/gp-transaction-search">available on GitHub</a>. You are welcome to clone the project from Visual Studio and download all of the code.<br />
<br />
Here is the method for opening the Vendor Inquiry window. Note that this window has a single lookup field--when you open it in GP, you choose a vendor ID, and the vendor's information is displayed.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-7-S1QUJ6THBQQ5_l8saXAxYEEoutJ5uxpBqPYVeVxAxJ2NQh2C8gvrz1E9kymTibpXYk5A9iNlUcEljyfmn8bcX_W8otRAkqdWgfe7EZ-tCTTWHWmeTr9_3f3IccgjreYzH8ov2A8xkN/s1600/GPVendorInquiry.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="290" data-original-width="362" height="256" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-7-S1QUJ6THBQQ5_l8saXAxYEEoutJ5uxpBqPYVeVxAxJ2NQh2C8gvrz1E9kymTibpXYk5A9iNlUcEljyfmn8bcX_W8otRAkqdWgfe7EZ-tCTTWHWmeTr9_3f3IccgjreYzH8ov2A8xkN/s320/GPVendorInquiry.jpg" width="320" /></a></div>
<br />
The process of opening this window using .NET is similar.<br />
<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"> private void OpenPMVendorInquiry(string vendorID)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> {</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> Controller.Instance.Model.PMSearchFocus = true;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> PmVendorInquiryForm pmVendorInquiryForm = Dynamics.Forms.PmVendorInquiry;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> PmVendorInquiryForm.PmVendorInquiryWindow pmVendorInquiryWindow;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> pmVendorInquiryWindow = pmVendorInquiryForm.PmVendorInquiry;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> pmVendorInquiryWindow.Open();</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> pmVendorInquiryWindow.VendorId.Value = vendorID;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> pmVendorInquiryWindow.VendorId.RunValidate();</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> }</span><br />
<br />
<br />
Create an instance of the GP form, then get the window object based on the form, then Open the window, populate the Vendor ID value, then call RunValidate.<br />
<br />
If you've done a few projects with VS Tools for Dynamics GP, this may be familiar.<br />
<br />
But what about this "Zoom" window?<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxnrnQ45wp7Fz-x6qtqTikiOufvWc_XmI4n4HhlAZTROCGoXtBgykZm4czp4kAWdIUxPe-6VIWVKioHRy2RREk0ZM4RLaY1ctdVOnTiXWQL-P-QcRPEp8qpKlAysvgk-gynOuxKK2PCL9r/s1600/PMTrxZoom.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="454" data-original-width="525" height="276" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxnrnQ45wp7Fz-x6qtqTikiOufvWc_XmI4n4HhlAZTROCGoXtBgykZm4czp4kAWdIUxPe-6VIWVKioHRy2RREk0ZM4RLaY1ctdVOnTiXWQL-P-QcRPEp8qpKlAysvgk-gynOuxKK2PCL9r/s320/PMTrxZoom.jpg" width="320" /></a></div>
<br />
It's an 'inquiry' window, but notice that it does not have a lookup field like the Vendor Inquiry window. This makes the zoom window different.<br />
<br />
If you try to populate the Voucher No and Document Type fields on this window using .NET, you'll get a whole lot of nothing. GP will not retrieve or display the data for the transaction.<br />
<br />
Hmmm.<br />
<br />
So how does one open and populate this window using .NET? I didn't know.<br />
<br />
So, I figured I would look at how Dexterity does it.<br />
<br />
First, you need to enable the ScriptDebugger option in your Dex.ini file <a href="https://support.microsoft.com/en-us/help/3077859/how-to-gather-a-dexterity-scriptlog-in-microsoft-dynamics-gp">by following these simple instructions</a>. Once that is done, setup the PM Transaction Inquiry Vendor window with a vendor selected and an invoice selected.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigA0MW0vPVxUdMPwUkpRu5BxHlsZvjrKOATw0_cerXg2aYDXWLWXeFSKZjl5C6NenfNMLh9d4NlRAVOnYBRMTKygHYMvjvH0xRGqwyRfqlKDqkU38H7TK0_b3UM66cnzaBa9UnmrFQEtsc/s1600/PMTrxInquiry2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="516" data-original-width="688" height="240" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigA0MW0vPVxUdMPwUkpRu5BxHlsZvjrKOATw0_cerXg2aYDXWLWXeFSKZjl5C6NenfNMLh9d4NlRAVOnYBRMTKygHYMvjvH0xRGqwyRfqlKDqkU38H7TK0_b3UM66cnzaBa9UnmrFQEtsc/s320/PMTrxInquiry2.jpg" width="320" /></a></div>
<br />
With that setup, click on the Debug menu on the main GP window, and select the last option, Log Scripts:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiXeJ0QHgnIFNQPmM2AkvRc9JzS9vfChWL3NLuYLQOuZoOJcMl7D0gUikDYaKi-tRvoq60bN7FLKgZ66Z4pzuwoLkV9WXyPVEznKe1PB3AybjU09TMJW8Uujp2w6qpHeYUhZOdsO1Qxrqv8/s1600/GPLogScripts.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="528" data-original-width="258" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiXeJ0QHgnIFNQPmM2AkvRc9JzS9vfChWL3NLuYLQOuZoOJcMl7D0gUikDYaKi-tRvoq60bN7FLKgZ66Z4pzuwoLkV9WXyPVEznKe1PB3AybjU09TMJW8Uujp2w6qpHeYUhZOdsO1Qxrqv8/s320/GPLogScripts.jpg" width="156" /></a></div>
<br />
You will be prompted to create a file. I just called it PMInquiry.txt and saved it to my desktop.<br />
<br />
Now that the script logging is enabled, go back to the PM Trx Inquiry Vendor window, with your invoice record selected, and click on the blue Document Number link in the scrolling window. That opens the PM Transaction Entry Zoom window, just like we want.<br />
<br />
Now click on the Debug menu in GP, and click on Log Scripts to stop the logging. Then open your new PMInquiry.txt script log file.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2Qgm0hlS4P2ED1uI28n3yWGXE6s8HmGi4jBT2uPXqyZGsXfHr3XVPvTtMcxpeUdM7wapqvdWL4KPMmxf-7lqHYpGLKKUFrDIAYjkHN_c_HiclvYQ7tMJH9Yd2bH-AfxYkOJYU0jAefs7S/s1600/DexScript.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="685" data-original-width="908" height="241" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2Qgm0hlS4P2ED1uI28n3yWGXE6s8HmGi4jBT2uPXqyZGsXfHr3XVPvTtMcxpeUdM7wapqvdWL4KPMmxf-7lqHYpGLKKUFrDIAYjkHN_c_HiclvYQ7tMJH9Yd2bH-AfxYkOJYU0jAefs7S/s320/DexScript.jpg" width="320" /></a></div>
<br />
It took me a minute to review the script and figure out what is going on, but I eventually saw this line.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"> 'OpenWindow of form PM_Transaction_Entry_Zoom', 1, "00000000000000374", 2, 1, 7814</span><br />
<br />
<br />
That's the window I wanted to open, and the command "OpenWindow" was a nice clue.<br />
<br />
Okay, so that's a Dex command that is opening the window, how does that help me with my .NET project?<br />
<br />
After doing some digging, I found that VS Tools lets you call some Dexterity Procedures. And wouldn't you know it, in .NET, there is a form called PMTransactionEntryZoom, just like what we see in the Dex log.<br />
<br />
And under that form, there is a Procedures option, and under that is an OpenWindow procedure.<br />
<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">Dynamics.Forms.PmTransactionEntryZoom.Procedures.OpenWindow.Invoke(pmTrx.DOCTYPE, pmTrx.CNTRLNUM, pmTrx.DCSTATUS, 1, 7814);</span><br />
<br />
<br />
In order to call the OpenWindow procedure, we use the OpenWindow.Invoke() method, into which we pass in a few parameters.<br />
<br />
The Dexterity script log gives us some clues about the parameters we need to pass in, and Visual Studio Intellisense helps as well.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYWfHPYllh5xglCXKb2SuiysfmcVWQDVkTQo3vaEVL4XMrbaKoBQuSpuJTphzxYvBBoh4kCH_Ud1ITcaWY79b5szq5FmVx-FL0EBOhNnT5nAFN81_Pds4wy7H7XlhhFZmBcoMTfLXNijZG/s1600/VSIntellisense.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="99" data-original-width="890" height="43" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYWfHPYllh5xglCXKb2SuiysfmcVWQDVkTQo3vaEVL4XMrbaKoBQuSpuJTphzxYvBBoh4kCH_Ud1ITcaWY79b5szq5FmVx-FL0EBOhNnT5nAFN81_Pds4wy7H7XlhhFZmBcoMTfLXNijZG/s400/VSIntellisense.jpg" width="400" /></a></div>
<br />
So the parameters are:<br />
<br />
<ol>
<li>NDocType</li>
<li>SVoucher</li>
<li>NDocStatus</li>
<li>NCyView</li>
<li>NCallerId</li>
</ol>
<br />
<br />
The first three are straightforward, and we can supply those from the data in the PM Transaction Search grid. CyView is apparently a Currency View option--I don't know the possible options for that parameter, but since we see a value of 1 in our Dex script, I used that value. And then CallerId is apparently the calling window, so I also used 7814 for that parameter, just like the Dex script.<br />
<br />
Once that single line of code is in place, right clicking or double clicking on a row will open the PM Transaction Entry Zoom window and populate the data.<br />
<br />
But if you use the PM Transaction Inquiry Vendor window to open different transaction types (PM Invoice, PM Payment, and POP Invoice), you will notice that it opens different Zoom windows. To replicate that functionality, I have the PM Transaction Search window open the same windows.<br />
<br />
So for payments, it opens the PmManualPaymentsZoom window.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">Dynamics.Forms.PmManualPaymentsZoom.Procedures.OpenWindow.Invoke(pmTrx.CNTRLNUM, pmTrx.DCSTATUS, 1, 7814);</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEingDrryn-bo3oF9slH2X2AP3DOygE3JONTb2bB8nfuvbAMlKDMgMDyKXNty4Brsknq88wmnsuFbGn9ojqG_b7fG_bJx-OudpdTb1NCHlFKLH0d9haQZFAn9Rou9J3C6NRFKPbUOuIqLOyQ/s1600/VSIntellisense2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="70" data-original-width="928" height="30" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEingDrryn-bo3oF9slH2X2AP3DOygE3JONTb2bB8nfuvbAMlKDMgMDyKXNty4Brsknq88wmnsuFbGn9ojqG_b7fG_bJx-OudpdTb1NCHlFKLH0d9haQZFAn9Rou9J3C6NRFKPbUOuIqLOyQ/s400/VSIntellisense2.jpg" width="400" /></a></div>
<br />
<br />
And then I tried to open the PopInquiryInvoiceEntry window.<br />
<br />
But...<br />
<br />
I couldn't.<br />
<br />
I tried using OpenWindow.Invoke to open that window, but Intellisense told me that the PopInquiryInvoiceEntry window does not have an OpenWindow procedure.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">Dynamics.Forms.PopInquiryInvoiceEntry.Procedures<strike><span style="color: red;">.OpenWindow.Invoke(0, "RCT1107", 2, 3, 1);</span></strike></span><br />
<br />
<br />
It has procedures, but OpenWindow is not exposed to VS Tools.<br />
<br />
And since it's a zoom window with no lookup fields, I can't use the simpler method to open and populate the window.<br />
<br />
So, I was stuck. There are apparently some GP windows you just can't open using VS Tools.<br />
<br />
In theory, it may be possible to use Continuum and pass-through SanScript to open the window, but I didn't have the patience or time to figure out Continuum, so at the moment, the PM Transaction Entry window does not support drill down to POP Invoices. It simply opens the transaction in a PM Invoice Zoom window instead.<br />
<br />
So it is usually possible to open a GP window to add drill down functionality to your .NET application, but just be aware that you may encounter a window that can't be opened properly from .NET.<br />
<br />
<br />
<b>UPDATE:</b> I have received feedback from a few people that explained to me that the PopInquiryInvoiceEntry has an OpenWindow function. Instead of accessing Procedures, just change that to Functions, and OpenWindow will appear for the POP window. Apparently this is due to inconsistencies with how the POP module was developed.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">Dynamics.Forms.PopInquiryInvoiceEntry.<b><span style="color: red;">Functions</span></b></span><span style="font-family: "courier new" , "courier" , monospace;">.OpenWindow.Invoke("RCT1107", 2, 3, 1);</span><br />
<br />
I have tested this and it works great. It will be included in <a href="https://precipioservices.com/free/gp-transaction-search/">GP Transaction Search</a> v3.0.<br />
<br />
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><a href="https://www.blogger.com/null" name="OLE_LINK1"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</span></i></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">You can also find him on </span></i></span></span><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="https://twitter.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Twitter</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, </span></i></span></span></span><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="https://www.youtube.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i style="mso-bidi-font-style: normal;"><span style="mso-bidi-font-size: 13.5pt;">YouTube</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, and </span></i></span></span></span><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="https://plus.google.com/108067157833601200392?rel=author"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Google+</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"> <o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="http://www.precipioservices.com/"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">http://www.precipioservices.com</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s1600/MVP+MCITP+Logos+New.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="103" data-original-width="451" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s320/MVP+MCITP+Logos+New.jpg" width="320" /></a></div>
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com2tag:blogger.com,1999:blog-6691994129222744759.post-35912896221584755162018-06-05T20:16:00.001-07:002019-01-19T11:30:37.548-08:00Dynamics GP Transaction Search v1.0 is Available! For FREE!<span style="color: red;">My blog has moved! Please visit the new blog at: <a href="https://blog.steveendow.com/">https://blog.steveendow.com/</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">I will no longer be posting to Dynamics GP Land, and all new posts will be at <a href="https://blog.steveendow.com/">https://blog.steveendow.com</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">Thanks!</span><br />
<br />
<br />
<br />
By Steve Endow<br />
<br />
A few weeks ago, a question was posted to the GPUG Open Forum asking why the AP Transaction Inquiry by Document was so slow.<br />
<br />
<div style="text-align: center;">
<a href="https://www.gpug.com/communities/community-home/digestviewer/viewthread?GroupId=247&MessageKey=662417f0-4c68-4ad8-b644-c4f628e45442&CommunityKey=4754a624-39c5-4458-8105-02b65a7e929e&tab=digestviewer&ReturnUrl=%2fcommunities%2fcommunity-home%2fdigestviewer%3fListKey%3dc8985617-e1ed-4b37-9427-d2bc0e80cbc1%26CommunityKey%3d4754a624-39c5-4458-8105-02b65a7e929e">AP Inquiry by Document GP2018 so slow</a></div>
<br />
The post explained that the company has so many different vendor accounts for FEDEX that they can't easily search based on vendor ID. So they find it easier to search by vendor Invoice Number.<br />
<br />
Unfortunately, because they have so much transaction history, and because the window pre-loads some data, it takes over 3 minutes for the inquiry window to even be displayed.<br />
<br />
That. Is. Crazy.<br />
<br />
I was...intrigued.<br />
<br />
What would cause a seemingly simple Dynamics GP inquiry window to take 3 minutes just to open? That's just weird.<br />
<br />
So I decided to test the window and trace the SQL activity that occurs when the window performs a search.<br />
<br />
It...was...bad. The PM Transaction Inquiry Document window throws out SQL queries like they're going out of style.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0yS2Q6-Yh9Bm8Qp_fYQwnpkJdxx857Tu7H8oGsnczN4OFebsl7CE3X9mUZZkvdniOSUODrkBWgTm-4Hq1oy4vHQ7vyCaYqpbL5BNw-n20RnXy23xSSkjMpHzLQe6yMz_ToqY9tYmxk6L9/s1600/Oprah.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="366" data-original-width="491" height="238" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0yS2Q6-Yh9Bm8Qp_fYQwnpkJdxx857Tu7H8oGsnczN4OFebsl7CE3X9mUZZkvdniOSUODrkBWgTm-4Hq1oy4vHQ7vyCaYqpbL5BNw-n20RnXy23xSSkjMpHzLQe6yMz_ToqY9tYmxk6L9/s320/Oprah.jpg" width="320" /></a></div>
<br />
Every single PM transaction record that is retrieved by the inquiry window results in at least 3 subsequent queries. FOR. EACH. RECORD.<br />
<br />
After seeing that, I gave up on attempting to "fix" or optimize the window. It wasn't a SQL performance issue or a question of optimization. It's just a terrible design.<br />
<br />
So, what would an ideal PM Transaction Inquiry window look like?<br />
<br />
How about this:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjS02hP-ppBtqdc6Ob6cQod51gc3laHqRX7pnEpvYrKhIafGzuNfzKP57YRzayJwoGg88tN_67uvkLvk8xB73iagLJk7BokfGhCg9Q2I3vNmk9auj9T0yPghc-k1bdTp0xBd5cIN-ixwfe8/s1600/PMTransactionSearch2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="483" data-original-width="1069" height="180" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjS02hP-ppBtqdc6Ob6cQod51gc3laHqRX7pnEpvYrKhIafGzuNfzKP57YRzayJwoGg88tN_67uvkLvk8xB73iagLJk7BokfGhCg9Q2I3vNmk9auj9T0yPghc-k1bdTp0xBd5cIN-ixwfe8/s400/PMTransactionSearch2.jpg" width="400" /></a></div>
<br />
<br />
A window that allows you to search by date range AND vendor ID AND vendor name AND document number AND document amount. <b><u>All simultaneously</u></b>.<br />
<br />
Here is a video explaining the project and demonstrating the PM Transaction Search window that is included in version 1.0:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/R8nvAol7mPA/0.jpg" frameborder="0" height="266" src="https://www.youtube.com/embed/R8nvAol7mPA?feature=player_embedded" width="320"></iframe></div>
<br />
<div style="text-align: center;">
<a href="https://www.youtube.com/watch?v=R8nvAol7mPA">https://www.youtube.com/watch?v=R8nvAol7mPA</a></div>
<br />
<br />
As you type each character, this magical search window calls a SQL stored procedure and updates the search results. <b>In real time</b>.<br />
<br />
To keep the window honest, a status bar at the bottom of the window shows how many records were retrieved, how many milliseconds it took to retrieve the data from SQL, as well as how many milliseconds it took to display the data in the window.<br />
<br />
We're talking real-world accountability here, folks.<br />
<br />
Want to see more info about a transaction? Just double click on the row to drill into GP! Or right click on a row to view the master record inquiry window.<br />
<br />
Don't like the columns that are displayed in the search window data grid? No problem. You can modify the included stored procedure. Want to add a column or two to the data? No problem. Just add a few fields to the procedure.<br />
<br />
Are you so thrilled by the results of your search that you want to share them with your coworkers? No problem. You can click on the Copy button to copy all of the data in the grid and paste it into Excel. Or you can click on the CSV button and save the data to a CSV text file.<br />
<br />
And the GP Transaction Search tool is extremely simple to install. Just run a SQL script to create a stored procedure in each of your GP company databases, then copy 3 files into the GP AddIns subdirectory. It really is that easy.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhu1W5UDclG5yJR-lS1UEdpZPftDJUkX4P8tEEr8yuK25sIYyP-hoaN9GJdbqiLhakUTEtI-mag20QRHeB22Pw_DAhg50utUZNPAl6hQYLqZIOYzjNPcAPkjBS1UBDuyHpwzPzi-BKKzfcR/s1600/PMSearchFiles.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="93" data-original-width="384" height="96" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhu1W5UDclG5yJR-lS1UEdpZPftDJUkX4P8tEEr8yuK25sIYyP-hoaN9GJdbqiLhakUTEtI-mag20QRHeB22Pw_DAhg50utUZNPAl6hQYLqZIOYzjNPcAPkjBS1UBDuyHpwzPzi-BKKzfcR/s400/PMSearchFiles.jpg" width="400" /></a></div>
<br />
<br />
You must be thinking: Wow, with all of those features, that fancy search window must cost a fortune!<br />
<br />
But that's where you're wrong! The Dynamics GP Transaction Search tool is available for FREE!<br />
<br />
<br />
Here is the <b>temporary</b> link to download the search tool from OneDrive:<br />
<br />
<a href="https://1drv.ms/f/s!Au567Fd0af9TogwHx8OKAarDM3Zm">https://1drv.ms/f/s!Au567Fd0af9TogwHx8OKAarDM3Zm</a><br />
<br />
<br />
UPDATE: I now have a dedicated page for the GP Transaction Search in the Free Products sections of my web site at:<br />
<br />
<a href="https://www.precipioservices.com/">https://precipioservices.com/free/gp-transaction-search/</a><br />
<br />
<br />
<br />
But that's not all! In addition to offering the tool for the rock bottom price of Free, I have published the code on GitHub as an open source project so that others can contribute to the project:<br />
<br />
<a href="https://github.com/steveendow/gp-transaction-search">https://github.com/steveendow/gp-transaction-search</a><br />
<br />
<br />
If you are a .NET developer or a Dexterity developer and are interested in helping enhance GP Transaction Search, <a href="https://precipioservices.com/contact-us/">please contact me</a>!<br />
<br />
If you have any questions about the search tool, or if you have used it in a real GP environment and have any suggestions for improving it, <a href="https://precipioservices.com/contact-us/">please contact me</a>!<br />
<br />
<br />
<a href="https://precipioservices.com/contact-us/">https://precipioservices.com/contact-us/</a><br />
<br />
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><a href="https://www.blogger.com/null" name="OLE_LINK1"><i>Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</i></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<i>You can also find him on </i><a href="https://twitter.com/steveendow"><i>Twitter</i></a><span class="apple-converted-space"><i>, </i></span><a href="https://www.youtube.com/steveendow"><i>YouTube</i></a><span class="apple-converted-space"><i>, and </i></span><a href="https://plus.google.com/108067157833601200392?rel=author"><i>Google+</i></a> <o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<i><a href="http://www.precipioservices.com/">http://www.precipioservices.com</a></i><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s1600/MVP+MCITP+Logos+New.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="103" data-original-width="451" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s320/MVP+MCITP+Logos+New.jpg" width="320" /></a></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<br />
<br />
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com4tag:blogger.com,1999:blog-6691994129222744759.post-30543116223035189432018-06-04T10:31:00.001-07:002019-01-19T11:24:03.807-08:00"There's nothing wrong with our SQL Servers or network", says the IT department confidentlyMy blog has moved! Please visit the new blog at: <a href="https://blog.steveendow.com/">https://blog.steveendow.com/</a><br />
<br />
I will no longer be posting to Dynamics GP Land, and all new posts will be at <a href="https://blog.steveendow.com/">https://blog.steveendow.com</a><br />
<br />
Thanks!<br />
<br />
<br />
<br />
By Steve Endow<br />
<br />
Let me share a story about an all-to-common situation in the life of the Dynamics GP consultant or developer.<br />
<br />
You develop an integration, or customization, or implement some software that talks to a SQL Server. We do this stuff every day. Normal, routine, common projects. Just software talkin' to a SQL Server. It's usually so reliable you don't think twice about it.<br />
<br />
Then, after your integration or software has been running just fine for months, without a single issue, you encounter this error on Monday at 1am:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)</span><br />
<br />
<br />
Hmmm, that's odd, we've never had this issue before--the integration has literally never had an issue connecting to the SQL Server. We've had two dozen other errors related to bad data or import errors, but never before have we seen an issue <u>connecting</u> to the SQL Server.<br />
<br />
<br />
"Hey IT manager, we received this SQL Server connection error from our weekly on Monday at 1am. Can you look into what might be causing this?"<br />
<br />
IT Manager, 5 minutes later: "Nothing wrong on our end! No errors, no issues, all of our jobs are running fine. If there was a problem, I would have known about it."<br />
<br />
<br />
Oooookay then.<br />
<br />
<br />
One week later. Monday. 1am. Same error, second week in a row. And this is occurring for two different integrations--one pointing to the production GP company, and the other pointing to the Test GP company.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgGW4L6Zy7YjuluiYUAeNPoBms39pr5FGKKEHqdtevU1pT0vlpXVCdNzPhChu9nNUw8B5uhEFigqrxHtUJlewwgZ9JNcx6hQOqtmn3cDSDoZZIqBWB29Cs3uY_Rgb3qpFDL8YCSYRU1U_E0/s1600/SQLError.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="104" data-original-width="543" height="76" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgGW4L6Zy7YjuluiYUAeNPoBms39pr5FGKKEHqdtevU1pT0vlpXVCdNzPhChu9nNUw8B5uhEFigqrxHtUJlewwgZ9JNcx6hQOqtmn3cDSDoZZIqBWB29Cs3uY_Rgb3qpFDL8YCSYRU1U_E0/s400/SQLError.jpg" width="400" /></a></div>
<br />
When both integrations are run manually at 10am, they both run fine. So this is not a fluke, and it is occurring consistently now.<br />
<br />
<br />
"Hey IT manager, for the second week in a row, we received the SQL Server connection error at 1am. It's occurring with two different integrations, so there is definitely something causing this. Can you please look into this again?"<br />
<br />
<br />
IT Manager, 1 minute later: "I was able to figure out the cause of the problem. We are <b>shutting down</b> our GP SQL Server <b>every night from 10pm to 5am</b>."<br />
<br />
<br />
Face. Palm.<br />
<br />
<br />
I actually encounter this on a regular basis. Fortunately in this case, it turned out to be a blatantly obvious cause--the IT manager had just forgotten about the change to the server maintenance schedule. But once he remembered, we had our explanation.<br />
<br />
But usually, the problem is less obvious and much more difficult to track down.<br />
<br />
It could be a bad switch or network card that causes intermittent SQL connection errors. It could be custom code that only fires at certain times, locking or blocking SQL resources, causing seemingly random SQL command timeouts that get blamed on your software. I've even had a situation where a Veeam backup of a completely different VM caused the host machine to drop network connections for other VMs. (a Veeam bug that I believe has been fixed)<br />
<br />
I've seen all of my custom SQL objects literally disappear every week because a super security conscious corporate customer has a routine that deleted any unapproved SQL objects from the database. And then there's the common case of anti-virus software blocking or deleting an EXE or DLL.<br />
<br />
Modern networks are complex, and when you have dozens or hundreds of things going on, it's usually not easy to identify what might be causing an intermittent or infrequent problem. When hardware and software is normally incredibly reliable, it seems that people are resistant to consider the possibility that something other than your integration or software is causing the problem.<br />
<br />
Just because your software happens to be the one that is logging the error, there seems to be a strange bias that has people blame your software and deny that something else is preventing your software from communicating with the SQL Server.<br />
<br />
I currently work with hundreds of customers, and as a result, I probably see this issue weekly. Unfortunately, if the IT department claims that their systems are working perfectly, in many cases there isn't much that I can do except to add additional error handling, logging, and diagnostic information to my log files to present to the IT department repeatedly. Sometimes it's enough to help a motivated tech do enough research to find the cause. But many times an intermittent non-critical error just gets ignored.<br />
<br />
If you encounter this issue with IT departments, do you have any suggestions? Do you have a technique for making the IT department curious about researching the problem instead of getting defensive? If so, I'm all ears.<br />
<br />
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><a href="https://www.blogger.com/null" name="OLE_LINK1"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</span></i></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">You can also find him on </span></i></span></span><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="https://twitter.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Twitter</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, </span></i></span></span></span><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="https://www.youtube.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i style="mso-bidi-font-style: normal;"><span style="mso-bidi-font-size: 13.5pt;">YouTube</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, and </span></i></span></span></span><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="https://plus.google.com/108067157833601200392?rel=author"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Google+</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"> <o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="http://www.precipioservices.com/"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">http://www.precipioservices.com</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s1600/MVP+MCITP+Logos+New.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="103" data-original-width="451" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s320/MVP+MCITP+Logos+New.jpg" width="320" /></a></div>
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-26594576776533711042018-05-29T21:51:00.002-07:002019-01-19T11:30:46.324-08:00Dramatically Improve Dynamics GP eConnect Performance, But There's a Catch<span style="color: red;">My blog has moved! Please visit the new blog at: <a href="https://blog.steveendow.com/">https://blog.steveendow.com/</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">I will no longer be posting to Dynamics GP Land, and all new posts will be at <a href="https://blog.steveendow.com/">https://blog.steveendow.com</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">Thanks!</span><br />
<br />
<br />
<br />
<br />
By Steve Endow<br />
<br />
If you've developed a Dynamics GP integration using eConnect and .NET, you should be familiar with the eConnect Serialization process, whereby the eConnect Serialization .NET assembly converts your transaction objects and data into an XML document.<br />
<br />
Here's an example of the serialization of a GL transaction.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCiAIDq38v5Mls5x3HFIqs8AWBPDMEwtgeGkzqQeBIliL5F49rxuajCce_L4HDYUurLZxWa-udxF77B1BIPu95sS_JJ6P0AUiyOFhOBqlm5KAA1yVagfOk4cIF6Gp6qM90jOn2I8ZetOmB/s1600/eConnGLTrans.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="280" data-original-width="718" height="124" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCiAIDq38v5Mls5x3HFIqs8AWBPDMEwtgeGkzqQeBIliL5F49rxuajCce_L4HDYUurLZxWa-udxF77B1BIPu95sS_JJ6P0AUiyOFhOBqlm5KAA1yVagfOk4cIF6Gp6qM90jOn2I8ZetOmB/s320/eConnGLTrans.jpg" width="320" /></a></div>
<br />
Notice that this code involves a single GL transaction object, <b>glTrans</b>, with the <b>glTrxHeader </b>and <b>glTransLines </b>assigned to the object properties.<br />
<br />
That single GL transaction is then assigned as a single element to the <b>glType</b> array, which is then converted to XML.<br />
<br />
Wait a minute. Why is the <b>GLTransactionType </b>object, in this case named <b>glType</b>, an array?<br />
<br />
Well, it's an array because eConnect Serialization supports the conversion of <u>multiple transactions</u> into a single XML document.<br />
<br />
What?<br />
<br />
So...why would you want to do that? Why serialize several transactions instead of one transaction at a time?<br />
<br />
Good question!<br />
<br />
Here is a video where I discuss this feature and demonstrate the performance:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/pxq1uohl4Z0/0.jpg" frameborder="0" height="266" src="https://www.youtube.com/embed/pxq1uohl4Z0?feature=player_embedded" width="320"></iframe></div>
<br />
<br />
I asked that same question ages ago when I was developing with eConnect on Dynamics GP 7.5, probably just over 12 years ago. I was using Visual Basic 6 and the eConnect COM components.<br />
<br />
When I saw that eConnect serialization supported transaction arrays, I assumed there must be some reason why that feature existed, so I performed a test to compare the import performance.<br />
<br />
I recall that with VB 6, when serializing and sending individual transactions, I was able to import an average of 20 AP invoice transactions per second, which is actually quite a bit better than what I see with .NET today. And I recall that when I tested serializing and sending multiple transactions at the same time, I did not see a performance benefit. I was therefore puzzled about the functionality.<br />
<br />
While I didn't observe any performance benefit, I did find a downside to sending multiple transactions to eConnect at once. If eConnect encountered an error while importing any of the transactions in the 'batch', the entire batch would be rolled back and no transactions would be saved. So if you send in 100 transactions, and 1 transaction fails to import, nothing is imported.<br />
<br />
And another annoyance: If your batch of 100 transactions had 5 transactions with errors, eConnect would only return 1 error. As soon as the first transaction has an error, eConnect stops, rolls everything back, and returns a single error. Once you fix error #1 and resubmit, eConnect will stop on error #2. So you are forced to deal with the errors one at a time, and in the meantime, zero transactions have been imported. It's not an ideal error handling process.<br />
<br />
So, fast forward to the year 2018, when I was recently asked if eConnect could import multiple transactions in a single call. I initially responded that it can, but that there was no performance benefit or value in doing so.<br />
<br />
But then, for some unknown reason, I figured I should test single vs. multiple transaction submissions and verify my long-held 'belief'.<br />
<br />
So I pulled up my eConnect load testing tool and modified it to support serializing multiple transactions, in addition to one transaction at a time.<br />
<br />
When I saw the results of sending multiple transactions to eConnect in a single submission...<br />
<br />
My...<br />
<br />
Jaw...<br />
<br />
Dropped.<br />
<br />
100 GL JE transactions imported in 17 seconds when sent individually to eConnect.<br />
<br />
But when I submitted a single XML document with 100 journal entries, eConnect imported them in <b>3 seconds</b>.<br />
<br />
WHAT???<br />
<br />
I then imported <b>700 </b>transactions, submitting them all to eConnect in a single import request. They imported in 17 seconds, the same time it took to import just 100 transactions individually.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyK7YiPoPxf3yr5fEeoFlbyeZxsqBBZm5lCIeTSwRNZl7kAV_3X4AFyIYQkhf-8QbTs63yAH3Nz91elFwfZqmlJio_qXvQNQLiaJlQ3iD0QMgeo5JpCaVYv3kidMGlDi6AzWcE0b7S5mO1/s1600/eConnectPerformance2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="593" data-original-width="638" height="371" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyK7YiPoPxf3yr5fEeoFlbyeZxsqBBZm5lCIeTSwRNZl7kAV_3X4AFyIYQkhf-8QbTs63yAH3Nz91elFwfZqmlJio_qXvQNQLiaJlQ3iD0QMgeo5JpCaVYv3kidMGlDi6AzWcE0b7S5mO1/s400/eConnectPerformance2.jpg" width="400" /></a></div>
<br />
That's a <b>7 fold </b>increase in performance, resulting from a minor change in the import code. That is a staggering difference.<br />
<br />
I repeated the tests several times and confirmed that it wasn't a fluke.<br />
<br />
I then tested with AP Invoices and saw the same results. In this test, I was able to import <b>750 </b>AP Invoices in a single submission in the same amount of time as submitting 100 AP Invoices individually. That is cra-zee.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2j3XuB8V6VsgqO4mfeClgfOXAhQHj9k7A76gcapHGxL29_MDSctYur2xgKIipznTJcVt48WrBZZok9KTWIcDsSqATe2192zJNRml-Ust5tbn1rrYG6RQY__USysoBu0cBKHB2pkQixF41/s1600/eConnectPerformance3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="592" data-original-width="637" height="371" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2j3XuB8V6VsgqO4mfeClgfOXAhQHj9k7A76gcapHGxL29_MDSctYur2xgKIipznTJcVt48WrBZZok9KTWIcDsSqATe2192zJNRml-Ust5tbn1rrYG6RQY__USysoBu0cBKHB2pkQixF41/s400/eConnectPerformance3.jpg" width="400" /></a></div>
<br />
<br />
So, based on my new tests with GL JEs and AP Invoices, submitting multiple transactions to eConnect can produce a huuuuuuge performance improvement with the eConnect .NET assemblies.<br />
<br />
My guess is that something changed when the eConnect assemblies were converted to .NET, resulting in a slower performance when importing individual transactions, but faster performance when importing multiple transactions.<br />
<br />
But....<br />
<br />
There is a catch.<br />
<br />
It's the same catch that I found when testing with VB 6 and eConnect COM objects. If there is a single import error, the entire batch will fail to import, and no transactions will be saved. So if you try to import 500 transactions, a single error will roll everything back and you'll need to fix the error before resubmitting. And if there is a second or third error, the entire batch will fail a second and third time. 495 transactions will be held up because of 5 errors. It's a hassle.<br />
<br />
And if you assigned 500 journal entry numbers and the batch of transactions fails to import, you've just burned through 500 JEs. When your auditors review the JEs in Dynamics GP, they'll see a gap of 500 JE numbers and start asking questions. If such errors happen frequently, you'll quickly burn through thousands of transaction numbers. It's not the end of the world, but it's not ideal.<br />
<br />
So, given these drawbacks, what's the conclusion?<br />
<br />
My personal recommendation is that <b>most</b> Dynamics GP users should stick with submitting single transactions to eConnect. For most Dynamics GP users, eConnect imports are not a bottleneck for the business, and I find that it is uncommon for customers to be waiting around for eConnect integrations to complete. Usually, customers are able to import hundreds or thousands of transactions relatively quickly, but are then having to wait for those transactions to post in Dynamics GP, as posting is far slower than importing.<br />
<br />
However, there are some very high volume customers that import tens of thousands or hundreds of thousands of transactions every day, and are either importing or posting transactions non-stop throughout the day. If you have Dynamics GP integrations that collectively take hours to complete, and this technique could save you hours of import time, then I would definitely recommend looking into it. But only if you 1) have the ability to develop pre-validation routines for all of your transactions to minimize errors, and 2) have the ability to deal with errors and reimport the failed transactions, and 3) you can develop a way to manage transaction numbers so that they aren't wasted with each failed import.<br />
<br />
I hope this was informative!<br />
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><a href="https://www.blogger.com/null" name="OLE_LINK1"><i>Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</i></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<i>You can also find him on </i><a href="https://twitter.com/steveendow"><i>Twitter</i></a><span class="apple-converted-space"><i>, </i></span><a href="https://www.youtube.com/steveendow"><i>YouTube</i></a><span class="apple-converted-space"><i>, and </i></span><a href="https://plus.google.com/108067157833601200392?rel=author"><i>Google+</i></a> <o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<i><a href="http://www.precipioservices.com/">http://www.precipioservices.com</a></i><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s1600/MVP+MCITP+Logos+New.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="103" data-original-width="451" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s320/MVP+MCITP+Logos+New.jpg" width="320" /></a></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<br />
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-47730225930375334562018-05-18T08:18:00.003-07:002019-01-19T11:28:19.373-08:00Get Next Dynamics GP SOP Transaction Number Using SQL<span style="color: red;">My blog has moved! Please visit the new blog at: <a href="https://blog.steveendow.com/">https://blog.steveendow.com/</a></span><br />
<span style="color: red;"><br /></span>
<span style="color: red;">I will no longer be posting to Dynamics GP Land, and all new posts will be at <a href="https://blog.steveendow.com/">https://blog.steveendow.com</a></span><br />
<span style="color: red;"><br /></span>
<span style="color: red;">Thanks!</span><br />
<br />
<br />
<br />
By Steve Endow<br />
<br />
As I <a href="https://dynamicsgpland.blogspot.com/2018/05/get-next-dynamics-gp-rm-payment-number.html">mentioned in a recent post</a>, some times it's better to use SQL to retrieve the next document number from Dynamics GP.<br />
<br />
I am currently working on an eConnect SOP Order import and needed to retrieve the next order number from GP.<br />
<br />
Here is my C# code for retrieving the next SOP number from GP using SQL.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_j5qBqJwJzeoiO97DXhYcnXEY9OMDHK_5FNxnzz1D2YH-rwJKzeCcMnqzRbW9LYqjGoJBsZ85OSHkfHcF0NhOnzyOMiN4tmonaPI72le56LUoVGWyQFToF9GdXpu5ZcTGTIOhz0TWvnw4/s1600/taGetSOPNumber.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="187" data-original-width="292" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_j5qBqJwJzeoiO97DXhYcnXEY9OMDHK_5FNxnzz1D2YH-rwJKzeCcMnqzRbW9LYqjGoJBsZ85OSHkfHcF0NhOnzyOMiN4tmonaPI72le56LUoVGWyQFToF9GdXpu5ZcTGTIOhz0TWvnw4/s1600/taGetSOPNumber.jpg" /></a></div>
<br />
The SQL stored procedure is called taGetSopNumber and you need to provide it with the SOP Type and the Doc Type ID of your desired transaction.<br />
<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">public string GetNextSOPNumber(int sopType, string docTypeID)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">{</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> //SOP Type: </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> //1 = Quote</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> //2 = Order</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> //3 = Invoice</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> //4 = Return</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> //5 = Back Order</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> //6 = Fulfillment Order</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> try</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> {</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> SqlConnection gpConn = ConnectionGP();</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> string commandText = "taGetSopNumber";</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> SqlParameter[] sqlParameters = new SqlParameter[5];</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[0] = new SqlParameter("@I_tSOPTYPE", System.Data.SqlDbType.TinyInt);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[0].Value = sopType;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[1] = new SqlParameter("@I_cDOCID", System.Data.SqlDbType.Char, 15);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[1].Value = docTypeID;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[2] = new SqlParameter("@I_tInc_Dec", System.Data.SqlDbType.TinyInt);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[2].Value = 1;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[3] = new SqlParameter("@O_vSopNumber", System.Data.SqlDbType.Char, 21);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[3].Value = string.Empty;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[3].Direction = ParameterDirection.Output;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[4] = new SqlParameter("@O_iErrorState", System.Data.SqlDbType.Int);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[4].Direction = ParameterDirection.Output;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[4].Value = 0;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> string nextNum = string.Empty;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> int recordCount = DataAccess.ExecuteNonQuery(gpConn, Controller.Instance.Model.GPDatabase, CommandType.StoredProcedure, commandText, sqlParameters);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> nextNum = sqlParameters[3].Value.ToString().Trim();</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> return nextNum;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> }</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> catch (Exception ex)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> {</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> Log.Write("An unexpected error occurred in GetNextSOPNumber: " + ex.Message, true);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> return string.Empty;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> }</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">}</span><br />
<br />
<br />
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><a href="https://www.blogger.com/null" name="OLE_LINK1"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</span></i></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">You can also find him on </span></i></span></span><a href="https://twitter.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Twitter</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, </span></i></span></span></span><a href="https://www.youtube.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i style="mso-bidi-font-style: normal;"><span style="mso-bidi-font-size: 13.5pt;">YouTube</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, and </span></i></span></span></span><a href="https://plus.google.com/108067157833601200392?rel=author"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Google+</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"> <o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="http://www.precipioservices.com/"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">http://www.precipioservices.com</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s1600/MVP+MCITP+Logos+New.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="103" data-original-width="451" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s320/MVP+MCITP+Logos+New.jpg" width="320" /></a></div>
<br />
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-74431563475377577582018-05-04T16:15:00.001-07:002018-05-04T16:15:15.480-07:00Consulting is never boring!By Steve Endow<br />
<br />
Today I had to switch between several tasks, and during one of those task switches, my brain put on the brakes.<br />
<br />
<br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><i>My brain: This is crazy!</i></span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><i><br /></i></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><i>Me: What is crazy?</i></span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><i><br /></i></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><i>Brain: This! This is crazy! Switching from SQL queries to Dynamics GP VS Tools to an ASP.NET Core web API for Dynamics GP to working with multiple Azure services. And that's just in the last hour! It's nuts!</i></span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><i><br /></i></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><i>Me: Uh, hello, we do this every day. So that you're not bored, remember?</i></span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><i><br /></i></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><i>Brain: Dude, that doesn't make it any less crazy.</i></span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><i><br /></i></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><i>Me: Noted. I'll blog about it just to make you feel better.</i></span><br />
<br />
<br />
If I actually stop for a moment, step back, and look at all of the things I do, all of the tools I use, and all of the things I have to know and understand to do my job, it is kinda crazy.<br />
<br />
If you're modest, you might think that this is fairly normal, which in some respects it is--lots of people probably do what you do in the consulting world. But if you want to really appreciate how much you really know, try hiring a 20 year old intern and give them a few small projects. You'll quickly realize that the "simple" task you gave the intern requires tons of fundamental knowledge that informs how to perform the task. It probably took you years to develop that fundamental knowledge, and then many more years on top of that to develop competence or mastery.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhioFR7yIhj_aRqRZR2fzgMZSSCrGP83OYOwjz1f_9EIlC5KIIpg602E6AN1xvdLvtefK9eDRXzjhce7hxEtdbjshB8BDt1p5e_1jp8UqXKZ5vSn3oYiqEnJ9_mfg1Wk-N4xfCwD8_nfOTe/s1600/Books1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="335" data-original-width="202" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhioFR7yIhj_aRqRZR2fzgMZSSCrGP83OYOwjz1f_9EIlC5KIIpg602E6AN1xvdLvtefK9eDRXzjhce7hxEtdbjshB8BDt1p5e_1jp8UqXKZ5vSn3oYiqEnJ9_mfg1Wk-N4xfCwD8_nfOTe/s200/Books1.png" width="120" /></a></div>
<br />
<br />
Let's start with SQL. In the Dynamics GP consulting world, a basic understanding of SQL Server and T-SQL is pretty much essential. <br />
<br />
"Hey intern, can you run this query?"<br />
<br />
"What's a query?"<br />
<br />
"It's a way to get data out of SQL Server."<br />
<br />
"SQL Server?"<br />
<br />
"Yes, SQL Server is a relational database."<br />
<br />
"Relational?"<br />
<br />
"Nevermind, just launch Management Studio and connect to the GP SQL instance"<br />
<br />
"GP SQL instance?"<br />
<br />
<br />
These steps may seems obvious, and is probably invisible to you if you've been doing it for years, but every single step requires an entire fundamental skill stack to perform even a basic task.<br />
<br />
So you need to know how to work with Management Studio. How to connect to a SQL instance. How to write some T-SQL. It's a good to understand SQL databases, tables, stored procedures, and views. Maybe triggers and cursors if you're daring. And how about backups and transaction logs and Recovery Model, just in case there's a problem?<br />
<br />
If you're on the bleeding edge, you'll know <a href="https://dynamicsgpland.blogspot.com/2017/09/back-up-your-dynamics-gp-sql-server.html">how to backup SQL Server databases to Azure</a>. Which means you should be familiar with SQL jobs and Azure Storage and backup compression. And Azure is an entire universe of knowledge.<br />
<br />
But back to Dynamics GP. How about SET files and dictionaries and chunk files and shared dictionaries and modified forms and reports and AddIns and Modifier & VBA? And there's all the knowledge around GL, AP, AR, SOP, POP, and IV, not to mention the other ancillary modules like AA, PA, FA, MC, CM, IC, HR, UPR, and others. You know that one checkbox under Tools -> Setup -> Posting -> Posting? Ya, that one that affects whether transaction posting hits the GL? Or what about that option in the SOP Type ID that affects inventory allocation and quantity overrides? Or the hundreds of other options you kinda need to be aware of?<br />
<br />
And naturally, since you're working with an accounting system, it's good to understand debits vs credits and income statement vs balance sheet and cash vs income vs expenses vs assets vs liabilities. And if you're into reporting, there's the entire universe of standard reporting tools and financial reporting tools.<br />
<br />
In my particular line of work, I also need to understanding everything from Excel macros to VBA to VB Script to Integration Manager to eConnect to SmartConnect. I need to know how to use .NET 2.5 through .NET Core 2.0 using Visual Studio 2010 through 2017. I need to thoroughly understand IIS and Kestrel, TCP/IP, ports, firewalls, DNS, HTTPS, TLS, SSH, and nmap. I need to know HMAC, AES, and SHA and have a fairly good understanding encryption.<br />
<br />
I need to be able to glance at XML and JSON and quickly find data issues. I need to know what HTTP verbs and response codes mean, as well as what "idempotent" means (that's actually a word). I need to understand TXT and CSV parsing and the issues related to using Excel files as data sources. I need to be able to review thousands of entries in a log file and figure out why two identical requests were processed 3 milliseconds apart, and that's only after I figure out how to reliably log activity with millisecond precision.<br />
<br />
I need to understand PCI compliance and how to call credit card gateway APIs for CC and ACH tokenization and transaction processing. And then there's the TLS 1.2 upgrade saga--don't get me started on that one.<br />
<br />
And while writing some complex queries years ago, I needed to figure out why they were taking hours to run. So I had to give myself a crash course SQL query optimization so that I didn't kill the SQL Server. Which led to me developing a subspeciality in amateur SQL Server optimization, which can be quite challenging in the Dynamics GP world. And if you're dealing with GP performance, it's helpful to understand virtualization and be familiar with Hyper-V and VMWare and how VM memory settings affect SQL Server.<br />
<br />
And the list goes on and on. It's a really, really long list of stuff you need to learn and know and understand and use on a regular basis.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlAUBvR6vuxG1L0J_p1Xp4ZXn65t9FpH5LMpuy1xq22QFD-W5XUBQt8i_ZrW7Tfl_657utaVy9PgC6amP-pkoNoAVQldt39WZKNPSqJwFMHuij7zGYYtxg87MtOJ24utAtjfZlemlypo9N/s1600/Books2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="398" data-original-width="398" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlAUBvR6vuxG1L0J_p1Xp4ZXn65t9FpH5LMpuy1xq22QFD-W5XUBQt8i_ZrW7Tfl_657utaVy9PgC6amP-pkoNoAVQldt39WZKNPSqJwFMHuij7zGYYtxg87MtOJ24utAtjfZlemlypo9N/s200/Books2.png" width="200" /></a></div>
<br />
It's kinda crazy.<br />
<br />
But that's also why I like it. <br />
<br />
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><a href="https://www.blogger.com/null" name="OLE_LINK1"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</span></i></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">You can also find him on </span></i></span></span><a href="https://twitter.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Twitter</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, </span></i></span></span></span><a href="https://www.youtube.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i style="mso-bidi-font-style: normal;"><span style="mso-bidi-font-size: 13.5pt;">YouTube</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, and </span></i></span></span></span><a href="https://plus.google.com/108067157833601200392?rel=author"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Google+</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"> <o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="http://www.precipioservices.com/"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">http://www.precipioservices.com</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<br /><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s1600/MVP+MCITP+Logos+New.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="103" data-original-width="451" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s320/MVP+MCITP+Logos+New.jpg" width="320" /></a><br />
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com1tag:blogger.com,1999:blog-6691994129222744759.post-47483906759916586142018-05-02T16:04:00.002-07:002018-05-02T16:12:33.950-07:00Get Next Dynamics GP RM Payment Number Using SQLBy Steve Endow<br />
<br />
When you import transactions into Dynamics GP, you often need to get the next transaction number or document number from Dynamics GP.<br />
<br />
In some simple cases, you can leave the document number field blank and <a href="https://dynamicsgpland.blogspot.com/2016/02/econnect-automatic-document-numbering.html">let eConnect get the next number for you</a>, but if you are sending in distributions or Analytical Accounting data for a transaction, you need to assign a document number to those elements before sending the transaction off to eConnect.<br />
<br />
<a href="https://support.microsoft.com/en-gb/help/939274/how-to-retrieve-the-next-document-number-for-receivables-management-do">eConnect does have a method to generate the next number</a>, but there's a big catch: it requires Windows authentication to connect to SQL and get the next document number. This works for some situations where you will be using Windows Authentication for your integration, but I have many situations where only a SQL or GP login will be available.<br />
<br />
In those cases, you can usually directly call the underlying eConnect stored procedures. The problem with this approach is figuring out which stored procedure to call and how to call it. You'd be surprised how challenging this can be, and every time I have to do it, I have to go find some old code because I can't seem to find the correct eConnect stored proc.<br />
<br />
Case in point is the process for generating the next RM payment (cash receipt) number. I looked and looked for the eConnect taRM procedure, but couldn't find it. Why? Because it's inconsistently named "taGetPaymentNumber". Ugh.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1lQzNEmTtdhe5bToBO5FPNJm4BJIU0O0JVBf7pHvoQ2_L4IYcXJwRkjjA7H8Rjw6O_BboGeCxMnxnlb67Mz0093PYO3QeurM5CGxWl7DBX6OxxnlQi4slosInpxfWwTGP1U4JEVnxpPo2/s1600/taGetPaymentNumber.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="222" data-original-width="292" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1lQzNEmTtdhe5bToBO5FPNJm4BJIU0O0JVBf7pHvoQ2_L4IYcXJwRkjjA7H8Rjw6O_BboGeCxMnxnlb67Mz0093PYO3QeurM5CGxWl7DBX6OxxnlQi4slosInpxfWwTGP1U4JEVnxpPo2/s1600/taGetPaymentNumber.jpg" /></a></div>
<br />
I couldn't find anything via Google on this, so, to document this lovely process, here is my C# code for getting the next RM Payment Number using the eConnect stored procedure.<br />
<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"> public static bool GetNextRMPaymentNumber(string gpDatabase, ref string nextPayment)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> {</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> //eConnect method, which uses Windows auth</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> //Microsoft.Dynamics.GP.eConnect.GetNextDocNumbers nextDoc = new Microsoft.Dynamics.GP.eConnect.GetNextDocNumbers();</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> //string nextRMPayment = nextDoc.GetNextRMNumber(Microsoft.Dynamics.GP.eConnect.IncrementDecrement.Increment, Microsoft.Dynamics.GP.eConnect.RMPaymentType.RMPayments, ConnectionStringWindows(gpDatabase));</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> //return nextRMPayment;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> //SQL method</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> string commandText = "taGetPaymentNumber";</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> SqlParameter[] sqlParameters = new SqlParameter[4];</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[0] = new SqlParameter("@I_vDOCTYPE", System.Data.SqlDbType.TinyInt);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[0].Value = 9; //9 = Payment</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[1] = new SqlParameter("@I_vInc_Dec", System.Data.SqlDbType.TinyInt);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[1].Value = 1; //1 = Increment</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[2] = new SqlParameter("@O_vDOCNumber", System.Data.SqlDbType.VarChar, 21);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[2].Direction = ParameterDirection.InputOutput;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[2].Value = string.Empty;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[3] = new SqlParameter("@O_iErrorState", System.Data.SqlDbType.Int);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[3].Direction = ParameterDirection.InputOutput;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sqlParameters[3].Value = 0;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> int recordCount = DataAccess.ExecuteNonQuery(gpDatabase, CommandType.StoredProcedure, commandText, sqlParameters);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> if (int.Parse(sqlParameters[3].Value.ToString()) == 0)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> {</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> if (sqlParameters[2].Value.ToString().Trim() != string.Empty)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> {</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> nextPayment = sqlParameters[2].Value.ToString().Trim();</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> return true;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> }</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> else</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> {</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> nextPayment = string.Empty;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> return false;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> }</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> }</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> else</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> {</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> return false;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> }</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"> }</span><br />
<br />
<br />
I prefer using this method, as it will work whether I am using Windows Auth or SQL auth.<br />
<br />
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</span></i></span><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">You can also find him on </span></i></span></span><a href="https://twitter.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Twitter</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, </span></i></span></span></span><a href="https://www.youtube.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i style="mso-bidi-font-style: normal;"><span style="mso-bidi-font-size: 13.5pt;">YouTube</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, and </span></i></span></span></span><a href="https://plus.google.com/108067157833601200392?rel=author"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Google+</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"> <o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="http://www.precipioservices.com/"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">http://www.precipioservices.com</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-53643765303727479312018-04-20T13:41:00.003-07:002018-04-20T15:43:23.068-07:00The Challenge of Posting Dates with Automated Dynamics GP ImportsBy Steve Endow<br />
<br />
If you are familiar with Dynamics GP, you are likely familiar with the confusion that can be caused by the "Posting Date" feature. Many customers have never opened the additional transaction date window in GP.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_w_jIH8TZZ8lZRgNWNUY0VIIgc6gUvo6FNkD0_oFJscqkl9vXEteYxXzKMOuiWCYUTUEG67AT3WReNWCbNHipyYYAzD1N8V3he8VA7lgAZHE4pQotPiut99z8zGUl_YgtBh1uELk_nGx_/s1600/SalesDateEntry.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="333" data-original-width="489" height="271" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_w_jIH8TZZ8lZRgNWNUY0VIIgc6gUvo6FNkD0_oFJscqkl9vXEteYxXzKMOuiWCYUTUEG67AT3WReNWCbNHipyYYAzD1N8V3he8VA7lgAZHE4pQotPiut99z8zGUl_YgtBh1uELk_nGx_/s400/SalesDateEntry.jpg" width="400" /></a></div>
<br />
A customer calls and asks, "Why did my April invoice post to the GL in March? The invoice is clearly dated April 5!"<br />
<br />
In addition to the confusion between Document Date and Posting Date, there is also the potential confusion caused by Transaction Posting Date vs. Batch Posting Date.<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEib7Aq1n8Kj3DpPnXWNDzc2Zya1k16KaYKftC8jhLoEniAFAK8D_oFc8SeUdtpKZhgAQmnPy0CEQc8-K0Q6Eu7gaexs9-KA2TJyWv9V__54WPgqyb8DMOQc0cFlPzU3R-O_HanDgaeHiDm_/s1600/PostingDateFrom.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="600" data-original-width="574" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEib7Aq1n8Kj3DpPnXWNDzc2Zya1k16KaYKftC8jhLoEniAFAK8D_oFc8SeUdtpKZhgAQmnPy0CEQc8-K0Q6Eu7gaexs9-KA2TJyWv9V__54WPgqyb8DMOQc0cFlPzU3R-O_HanDgaeHiDm_/s400/PostingDateFrom.jpg" width="382" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
As if that isn't enough fun related to dates, things can get particularly interesting and challenging with automated integrations with Dynamics GP.<br />
<br />
The issue typically comes up during month end. If an April 15 invoice is posted to April 16, it is usually not an issue and nobody notices. But if a March 31 invoice is posted to April 1, that can cause issues.<br />
<br />
When a user is entering transactions manually in GP, they can review the invoice, know whether it should be posted to March or April, and set the posting date accordingly. But when an automated integration is importing data, it usually doesn't know which fiscal period a transaction belongs to. It has to rely upon a data field in the source data to tell it what the posting date should be.<br />
<br />
That sounds easy enough, right?<br />
<br />
Unfortunately, it isn't always easy.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhA2YhUfGvL8PbXJrKtPpC7Wbcp9fYWD-39knnFu9xOrI-c4w9JclAZ6Rmj15qXebASNmoZvuC-1S6Mzhgm0azpTonpqaNslzSFYz4ql5j87zmzNd0n6y75uoUmB1DLgQGkEOEbsi0SwsYs/s1600/PostDates1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="152" data-original-width="556" height="108" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhA2YhUfGvL8PbXJrKtPpC7Wbcp9fYWD-39knnFu9xOrI-c4w9JclAZ6Rmj15qXebASNmoZvuC-1S6Mzhgm0azpTonpqaNslzSFYz4ql5j87zmzNd0n6y75uoUmB1DLgQGkEOEbsi0SwsYs/s400/PostDates1.jpg" width="400" /></a></div>
<br />
Above is some sample data from an integration. A single invoice date is provided in the DOCDATE column. And a Batch ID of 2018-04-20 is provided, implying that the transactions are related to April 20. From this information, you could reasonably assume that the transactions should post to the 2018-04 fiscal period.<br />
<br />
But what about this sample data.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEit16f6pwKORpaTOU1uMITyNTOxO5jvVcgt0-ejSmvUL_P4lkeVlOkwFY2hBBuyjKDspTQMgZb-wg8JLaL38579zo3sDHvDYtFnWhF7n8QCcclB-gZNw4yAsoeGJbYzcqGVdChRuz0bpep6/s1600/PostDates2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="147" data-original-width="552" height="106" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEit16f6pwKORpaTOU1uMITyNTOxO5jvVcgt0-ejSmvUL_P4lkeVlOkwFY2hBBuyjKDspTQMgZb-wg8JLaL38579zo3sDHvDYtFnWhF7n8QCcclB-gZNw4yAsoeGJbYzcqGVdChRuz0bpep6/s400/PostDates2.jpg" width="400" /></a></div>
<br />
This morning a concerned and upset customer called me asking "Why did our April invoices post to March??"<br />
<br />
The batch ID of "20180401" indicated that these were April invoices and not March invoices. But as we know, Dynamics GP doesn't care about the batch ID when it comes to posting. The only date that matters is the Posting Date.<br />
<br />
"But we don't import a posting date with our invoices. Only the Invoice (document) date!", the customer responded.<br />
<br />
Good point. The source data only contained DOCDATE, and their SmartConnect map was only setup to import the invoice Document Date field.<br />
<br />
So why did all of their invoices in the 20180401 batch get posted to March 31?<br />
<br />
Well, as I mentioned above, you have to know whether GP is configured to post using the transaction posting date or the batch posting date. And to keep things confusing, it is possible to configure some batches to post using the transaction posting date, and have other batches post using a batch posting date.<br />
<br />
So using the sample data above, why did the 20180401 batch post to March 31?<br />
<br />
When importing transactions using eConnect (or SmartConnect, etc.), if the Batch ID specified for the transaction does not exits, eConnect will create the batch automatically. You don't need to specify additional options--it will just handle it for you.<br />
<br />
<b>And when your Dynamics GP batch type is set to use the Batch Posting Date, guess what eConnect uses as the default value for the Batch Posting Date? The document date.</b><br />
<br />
So in the above sample data, the first invoice that is imported has a Document Date of March 31. So eConnect dutifully creates a new batch with a posting date of March 31. It then imports the invoices into that batch. <b>And all of the invoices in that batch will post to March 31.</b> Even if the invoice date is April 1.<br />
<br />
Okay, so the customer just needs to fix the March 31 dates, right?<br />
<br />
Perhaps it may be that simple. Maybe there was a bug in their source data.<br />
<br />
But what about invoices that are generated on April 1, but related to March? What about a vendor invoice dated April 2 that is received from an external AP system on April 3, but was for a service performed in March? An integration won't know the invoice should be posted to March--the source data would have to provide an additional clue, such as a separate Posting Date or Fiscal Period field.<br />
<br />
I've only encountered a few customers who were able to supply that fiscal period field separate from the document date field. In my experience, it is not common for a source system to know the fiscal period for a transaction--most only have a single transaction date.<br />
<br />
So when designing a transaction import for Dynamics GP, make sure to consider what happens when transactions are dated the last day of the month or first day of the month, and whether transactions related to a <b>prior fiscal period</b> may show up in your source data. It can be surprisingly tricky.<br />
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><i>Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</i><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<i>You can also find him on </i><a href="https://twitter.com/steveendow"><i>Twitter</i></a><span class="apple-converted-space"><i>, </i></span><a href="https://www.youtube.com/steveendow"><i>YouTube</i></a><span class="apple-converted-space"><i>, and </i></span><a href="https://plus.google.com/108067157833601200392?rel=author"><i>Google+</i></a> <o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<i><a href="http://www.precipioservices.com/">http://www.precipioservices.com</a></i><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s1600/MVP+MCITP+Logos+New.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="103" data-original-width="451" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s320/MVP+MCITP+Logos+New.jpg" width="320" /></a></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<br />
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-47141742466372836772018-04-16T20:31:00.002-07:002018-04-30T11:24:06.799-07:00My First SQL Saturday event: It was amazingBy Steve Endow<br />
<br />
The Microsoft SQL Server community is amazing.<br />
<br />
Amazing.<br />
<br />
That's not an exaggeration or platitude.<br />
<br />
On Saturday, I attended my first "<a href="http://www.sqlsaturday.com/">SQL Saturday</a>" event in Orange County, California. I left speechless.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilrbWSUpHuo03FvgCZOqBGorv6wMbN0Sz3Hik3o7oB5ZKrwfjkH4gcl25CwaThSz-NapczoPDGJYI3dPaTVueQNT3s3V0MLZUj4azXRMvXE38xkJqbGPAlzgDK6nzOKItd6sxHIoMFwroy/s1600/sqlsaturday-logo.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="39" data-original-width="264" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilrbWSUpHuo03FvgCZOqBGorv6wMbN0Sz3Hik3o7oB5ZKrwfjkH4gcl25CwaThSz-NapczoPDGJYI3dPaTVueQNT3s3V0MLZUj4azXRMvXE38xkJqbGPAlzgDK6nzOKItd6sxHIoMFwroy/s1600/sqlsaturday-logo.png" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
Several hundred people attended the event at a local college. On a Saturday. I overheard one attendee say that she woke up at 4am and had a 2+ hour drive from San Diego to attend. Presenters flew in from all over the country to speak at the event, with several speakers facing a snow storm and flight cancellations trying to return home. They did this, without compensation, on a Saturday. And some were planning on attending up to 10 other SQL Saturday events across the country.<br />
<br />
And I should mention that the event was free for attendees. Completely free.<br />
<br />
When I arrived at 8am, there was a line of 40 or 50 people waiting to check in. There were lots of volunteers helping people check in, handing out tote bags, re-printing passes, setting up tables, and preparing the event. Before the first session started, they had setup tables with gallons of free coffee, bagels, danishes, and donuts.<br />
<br />
The event is organized by <a href="http://www.pass.org/AboutPASS.aspx">PASS</a>, a non-profit organization that helps support people who use Microsoft data technologies.<br />
<br />
Ten companies sponsored the <a href="http://www.sqlsaturday.com/About.aspx">SQL Saturday</a> event, which has the following mission statement:<br />
<br />
<blockquote class="tr_bq" style="background-color: white; border: 0px; box-sizing: border-box; color: #293338; font-family: "Gotham SSm A", "Gotham SSm B", Arial, sans-serif; font-size: 17px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: 36px; padding: 0px; vertical-align: baseline;">
<b>Our Mission</b><br />
The PASS SQLSaturday program provides the tools and knowledge needed for groups and event leaders to organize and host a free day of training for SQL Server professionals. At the local event level, SQLSaturday events:<br />
<ul style="background-color: white; border: 0px; box-sizing: border-box; color: #293338; font-family: "Gotham SSm A", "Gotham SSm B", Arial, sans-serif; font-size: 17px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; list-style-image: initial; list-style-position: initial; margin: 0px 0px 30px 18px; padding: 0px; vertical-align: baseline;">
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Encourage increased membership for the local user group</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Provide local SQL Server professionals with excellent training and networking opportunities</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Help develop, grow, and encourage new speakers</li>
</ul>
</blockquote>
When I signed up, I didn't know what to expect. I thought it might be a casual user-group style meeting with a few speakers. But it was much more like a full fledged, single day, intense SQL Server conference.<br />
<br />
Several of the speakers that I saw were simply amazing.<br />
<br />
Here are the sessions that I attended:<br />
<br />
<ol>
<li>SQL Database and Query Design for Application Developers</li>
<li>Azure Basics for the DBA</li>
<li>PowerShell for the SQL DBA</li>
<li>Spotlight on SQL Server by Quest Software (vendor presentation)</li>
<li>Data Pages, Allocation Units, IAM Chains</li>
<li>The Query Store and SQL Tuning</li>
<li>Fundamentals That Will Improve Query Performance</li>
</ol>
<br />
<br />
The 6 educational sessions were incredible. I felt I knew a fair amount about 3 of the topics, but still learned a ton in those sessions. And the 3 sessions with topics that were new to me had so much valuable content that I was dizzy by the time the session ended. For example, I learned how the data is structured inside of an 8K data page--down to the byte! WHAT?!?!<br />
<br />
I took pages of notes on my iPad during most of the sessions, as they were all offering real world knowledge, experience, anecdotes, and lessons about how to use different SQL Server features and tools.<br />
<br />
It was 6 solid hours of high quality content presented by SQL Server experts. It was intense, valuable learning, and I was tired at the end of the day.<br />
<br />
It was amazing.<br />
<br />
If you work with SQL Server and have an opportunity to attend a SQL Saturday event, I recommend it.<br />
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><i>Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</i><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<i>You can also find him on </i><a href="https://twitter.com/steveendow"><i>Twitter</i></a><span class="apple-converted-space"><i>, </i></span><a href="https://www.youtube.com/steveendow"><i>YouTube</i></a><span class="apple-converted-space"><i>, and </i></span><a href="https://plus.google.com/108067157833601200392?rel=author"><i>Google+</i></a> <o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="http://www.precipioservices.com/"><i>http://www.precipioservices.com</i></a><o:p></o:p></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s1600/MVP+MCITP+Logos+New.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="103" data-original-width="451" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s320/MVP+MCITP+Logos+New.jpg" width="320" /></a></div>
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com1tag:blogger.com,1999:blog-6691994129222744759.post-70177982716003351302018-03-26T20:44:00.001-07:002019-01-19T11:30:59.162-08:00My Typical Password: Is a 25 character minimum passphrase policy possible?<span style="color: red;">My blog has moved! Please visit the new blog at: <a href="https://blog.steveendow.com/">https://blog.steveendow.com/</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">I will no longer be posting to Dynamics GP Land, and all new posts will be at <a href="https://blog.steveendow.com/">https://blog.steveendow.com</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">Thanks!</span><br />
<span style="color: red;"><br /></span>
<span style="color: red;"><br /></span>
<br />
By Steve Endow<br />
<br />
If you haven't read my prior post about passwords, perhaps read that first:<br />
<br />
<a href="https://dynamicsgpland.blogspot.com/2016/10/how-do-you-choose-your-passwords-and.html">https://dynamicsgpland.blogspot.com/2016/10/how-do-you-choose-your-passwords-and.html</a><br />
<br />
<br />
My "Passphrase Generator" has been working great since then. It isn't perfect, but has been working well enough for me.<br />
<br />
I thought I was doing all the "right" things by using my passphrase generator and using a <a href="https://www.roboform.com/">password manager</a> religiously.<br />
<br />
Using words with a max length of 7 characters, 2 numbers, and 1 symbol, I have been generating passphrases like:<br />
<br />
Briony%4Cobwebs4 (16 chars)<br />
Hyped/5Umber1 (13 chars)<br />
Reecho%6Touzled8 (16 chars)<br />
Tisanes#4Tangles6 (17 chars)<br />
<br />
<br />
I considered these pretty safe passwords.<br />
<br />
But I recently started listening to Kevin Mitnick's book, <a href="https://www.audible.com/pd/Science-Technology/The-Art-of-Invisibility-Audiobook/B01N80UK3J">The Art of Invisibility</a>, on Audible. In that book, Mitnick recommends that people now use passphrases of at least 25 characters.<br />
<br />
25 characters?!?!? (<a href="https://en.wikipedia.org/wiki/Interrobang">interrobang</a>)<br />
<br />
That's crazy!<br />
<br />
But is it?<br />
<br />
Those of us who work with Dynamics GP regularly bemoan the 15 character limit for passwords, as many of our customers have encountered issues with this limit. The customer's IT security policy requires a minimum of 15 characters, and they eventually figure out that their 16+ character passwords don't work in Dynamics GP.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-A21iqEgP0XOrp28ZfblNXTkkm0SeYB562Anleqj2CPSQsUupaGlGKSdScBANMfHn48H0P80biXTJhG0SqBJPYylefiEIbynh6U1UaXhVeQfMY-cWzwFZENk1own8A_XDnZdstCD49Z8t/s1600/GPPassword.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="338" data-original-width="377" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-A21iqEgP0XOrp28ZfblNXTkkm0SeYB562Anleqj2CPSQsUupaGlGKSdScBANMfHn48H0P80biXTJhG0SqBJPYylefiEIbynh6U1UaXhVeQfMY-cWzwFZENk1own8A_XDnZdstCD49Z8t/s1600/GPPassword.jpg" /></a></div>
<br />
So obviously that rules out using 25+ character passwords for Dynamics GP.<br />
<br />
But I'm pretty sure I've run into web sites that would not allow me to have anywhere near 25 characters.<br />
<br />
There's only one way to find out. I just reset my password on these web sites. These weren't the limits of the site, just the length of the long passwords that I randomly generated for each and successfully saved.<br />
<br />
Twitter: 35 characters<br />
Stack Overflow: 35 characters<br />
GPUG.com: 38 characters<br />
Atlassian/Bitbucket: 36 characters<br />
<br />
Wow, moving right along! It looks like a 25 character minimum password might be possible!<br />
<br />
(play record scratch here)<br />
<br />
Then I login to my online banking web site. Major bank. Big bank. Huge bank. Not a relatively tiny web site like GPUG.<br />
<br />
And what do I see?<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjXb9j_VS3yGAK8TfSR8JwuvcKNybdqI-rTOkvU_TesaVge5fhiwXw6BYLd28Y18IArGyOlj4kW8sfWI4dpUGN9peK6RlLPkHCneWogW_PK9mKDVsNhVSMHFivyhYOG1l0vOzoGB4kEszI/s1600/BankPasscode.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="219" data-original-width="345" height="203" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjXb9j_VS3yGAK8TfSR8JwuvcKNybdqI-rTOkvU_TesaVge5fhiwXw6BYLd28Y18IArGyOlj4kW8sfWI4dpUGN9peK6RlLPkHCneWogW_PK9mKDVsNhVSMHFivyhYOG1l0vOzoGB4kEszI/s320/BankPasscode.jpg" width="320" /></a></div>
<br />
20 character max! What??<br />
<br />
Strike one!<br />
<br />
Hmmm, let's check another bank web site. I log in to a smaller bank that I use, but when I try to change the password...and...<br />
<br />
...it doesn't allow me to paste in the password from my Passphrase Generator!<br />
<br />
That is garbage!<br />
<br />
Troy Hunt lays out this entire stupid fake "security" policy of not allowing password pasting <a href="https://www.troyhunt.com/the-cobra-effect-that-is-disabling/">in his excellent blog post here</a>.<br />
<br />
And I see that he shows examples of GE Capital and PayPal and others.<br />
<br />
So that pretty much kills the idea of consistently using 25+ character passwords.<br />
<br />
Could I use really long passwords on sites that allow them, and that allow pasting? Sure. And I may start doing that.<br />
<br />
But clearly there are many sites, particularly the large ones, that have indefensible password length limitations and block the paste function. So for those, you're limited to their arbitrarily short password lengths.<br />
<br />
So I guess that answers my question.<br />
<br />
With that said, will I use 30+ character passwords? Not sure.<br />
<br />
Occasionally I have to manually enter the password on a mobile device, and it is a nightmare to try and type that many characters in a password field. I can barely compose a simple text message on my phone without making a typo, so my password typing success rate is not stellar.<br />
<br />
But I may give it a try. As I reset my passwords going forward, I'll try and use a 25+ character passphrase and see how it goes.<br />
<br />
Hopefully some day my bank will allow more than 20 characters.<br />
<br />
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><a href="https://www.blogger.com/null" name="OLE_LINK1"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</span></i></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">You can also find him on </span></i></span></span><a href="https://twitter.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Twitter</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, </span></i></span></span></span><a href="https://www.youtube.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i style="mso-bidi-font-style: normal;"><span style="mso-bidi-font-size: 13.5pt;">YouTube</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, and </span></i></span></span></span><a href="https://plus.google.com/108067157833601200392?rel=author"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Google+</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"> <o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="http://www.precipioservices.com/"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">http://www.precipioservices.com</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com2tag:blogger.com,1999:blog-6691994129222744759.post-39949965810983721372018-03-13T22:14:00.000-07:002018-03-13T22:18:44.649-07:00Beware of MIN, MAX, and TOP in Dynamics GP SQL queries!By Steve Endow<br />
<br />
A few weeks ago <a href="https://dynamicsgpland.blogspot.com/2018/02/t-sql-max-vs-top-1-which-is-better.html">I started some research</a> to compare the performance of MAX vs. TOP(1) in SQL Server queries.<br />
<br />
After finding some unexpected results, <a href="https://dynamicsgpland.blogspot.com/2018/03/sql-max-vs-top-1-part-2-revenge-of.html">I created a second video</a> showing some odd behavior of MAX and TOP on one particular Dynamics GP table. At that time, I couldn't figure out what was causing the performance issue with the MAX function.<br />
<br />
Well, thanks to some very generous help and amazing insight from <a href="https://littlekendra.com/">Kendra Little</a>, I finally have a definitive explanation for the performance issue.<br />
<br />
<div style="text-align: center;">
<a href="https://youtu.be/oKsFUhPGgKk">https://youtu.be/oKsFUhPGgKk</a></div>
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/oKsFUhPGgKk/0.jpg" frameborder="0" height="266" src="https://www.youtube.com/embed/oKsFUhPGgKk?feature=player_embedded" width="320"></iframe></div>
<br />
<a name='more'></a><br />
<br />
After looking at the IV30500 table and sample queries using MAX and TOP 1, Kendra quickly noticed that the table had ANSI NULLs turned off. I explained some history of Dynamics GP and it's older database design quirks, and she pondered the performance issue further.<br />
<br />
The next morning, she had found the issue. She sent me this query to check the ANSI_PADDING settings on the char fields in the IV30500 table.<br />
<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">select OBJECT_NAME(object_id) AS TableName, name AS ColumnName, is_ansi_padded, * from sys.columns</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">where object_id = OBJECT_ID('IV30500') AND system_type_id = 175</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">GO</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPqV8lvhRNdwXNOTWqQ2EbWPvYyJcxPypUR_mft4pYS-Syd7BgDWeDRoH61ygL0MMBX0fYKxalvTmu4UOIrBp6drwBCIY2pNJKtyrt5hQ5uqiZy33E-W0iU_iurpA0Lb_McUSBvP-ODcBZ/s1600/GPMAX1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="126" data-original-width="408" height="122" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPqV8lvhRNdwXNOTWqQ2EbWPvYyJcxPypUR_mft4pYS-Syd7BgDWeDRoH61ygL0MMBX0fYKxalvTmu4UOIrBp6drwBCIY2pNJKtyrt5hQ5uqiZy33E-W0iU_iurpA0Lb_McUSBvP-ODcBZ/s400/GPMAX1.jpg" width="400" /></a></div>
<br />
<br />
The query shows that is_ansi_padded = 0 for IV30500. But if I run the same query on the IV30500REBUILD table, which was created by SQL Server when I exported data out of IV30500, I get different results.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">select OBJECT_NAME(object_id) AS TableName, name AS ColumnName, is_ansi_padded, * from sys.columns</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">where object_id = OBJECT_ID('IV30500REBUILD') AND system_type_id = 175</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">GO</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXR5AA0P_evuPKDoOwh_SAsyjErnvvxl5BB6K_HZdbPd5u7ySgzqMqE5zwLfypEQ0jWHNbJFtZma7qutl5eBhgqJdCihJfL6MfX-9LEwh3jjilcmQHkNQA-ZrGyAq9KopC_TYAN67gfhZE/s1600/GPMAX2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="112" data-original-width="452" height="98" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXR5AA0P_evuPKDoOwh_SAsyjErnvvxl5BB6K_HZdbPd5u7ySgzqMqE5zwLfypEQ0jWHNbJFtZma7qutl5eBhgqJdCihJfL6MfX-9LEwh3jjilcmQHkNQA-ZrGyAq9KopC_TYAN67gfhZE/s400/GPMAX2.jpg" width="400" /></a></div>
<br />
So why does this matter?<br />
<br />
Well, to understand that, you have to learn a bit more about the ANSI_PADDING setting in SQL Server and how that affects SQL queries. I still don't fully understand the details, but here are some references in case you want to learn more:<br />
<br />
<a href="https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-padding-transact-sql">https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-padding-transact-sql</a><br />
<br />
<a href="https://support.microsoft.com/en-us/help/316626/inf-how-sql-server-compares-strings-with-trailing-spaces">https://support.microsoft.com/en-us/help/316626/inf-how-sql-server-compares-strings-with-trailing-spaces</a><br />
<br />
<a href="https://technet.microsoft.com/en-us/library/ms187403(v=sql.105).aspx">https://technet.microsoft.com/en-us/library/ms187403(v=sql.105).aspx</a><br />
<br />
<br />
The key sentence from the third link is:<br />
<blockquote class="tr_bq">
<span style="color: blue; font-family: "arial" , "helvetica" , sans-serif;">When ANSI_PADDING set to OFF, queries that involve MIN, MAX, or TOP on character columns might be slower than in SQL Server 2000.</span></blockquote>
<br />
Apparently when ANSI PADDING is off for a field, when it needs to evaluate the field for a query, it must sometimes pad spaces on the end of every single row value before it can perform a comparison. As a result it must scan every row in the table, or in an index, before it can fulfill the query.<br />
<br />
This is what I was seeing with the MAX function on the TRXSORCE field of IV30500. Every single record was being returned by the Index Scan.<br />
<br />
The TOP 1 operator apparently does not have to perform this same padding operation, so it is able to simply retrieve one row from the index and return it.<br />
<br />
This is a pretty big deal.<br />
<br />
While I'm guessing that MIN and MAX aren't widely used in Dynamics GP queries, there are certainly some situations where they would be useful. If they are used on a table with a large number of rows, the performance hit will be significant.<br />
<br />
In some cases, using TOP 1 may help, but as the key sentence above states, even the TOP operator may trigger the same performance issue in some queries.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjOppigbYoJE3c6SPYH_dXIjKbo94EmoaGNZfGxYmwSSHwCqQsWyLEokviAWoCYswilXz_5bNFfKurzDfuFkNIH56TZasTYg0H2aETBD4sXDr4H-WNhVRINUV6KCf_hk8n7Ligpx_U8jWq/s1600/GPMAX3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="169" data-original-width="466" height="145" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjOppigbYoJE3c6SPYH_dXIjKbo94EmoaGNZfGxYmwSSHwCqQsWyLEokviAWoCYswilXz_5bNFfKurzDfuFkNIH56TZasTYg0H2aETBD4sXDr4H-WNhVRINUV6KCf_hk8n7Ligpx_U8jWq/s400/GPMAX3.jpg" width="400" /></a></div>
<br />
If you are using MIN, MAX, or TOP and see an Index Scan in your execution plan that returns every row in the table, that may be a sign that you are encountering this issue.<br />
<br />
In the video above, I show one way to modify a specific field to turn ANSI_PADDING on, but I don't know that I would recommend this in a production Dynamics GP environment. It may work fine, but you'll have to be careful to perform the update after every Dynamics GP release or service pack, as any tables re-scripted by Dynamics GP will likely revert back to having ANSI_PADDING off.<br />
<br />
And that, mercifully, finally solves the riddle of the poor performance of MAX in Dynamics GP.<br />
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><i>Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</i><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<i>You can also find him on </i><a href="https://twitter.com/steveendow"><i>Twitter</i></a><span class="apple-converted-space"><i>, </i></span><a href="https://www.youtube.com/steveendow"><i>YouTube</i></a><span class="apple-converted-space"><i>, and </i></span><a href="https://plus.google.com/108067157833601200392?rel=author"><i>Google+</i></a> <o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="http://www.precipioservices.com/"><i>http://www.precipioservices.com</i></a><o:p></o:p></div>
<br />
<br />
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-72696318348105609192018-03-07T21:00:00.001-08:002018-03-07T21:00:31.550-08:00SQL MAX vs TOP 1: Part 2! The Revenge of IV30500!By Steve Endow<br />
<br />
I just can't let it go. I need to know. I need answers. I need to solve the mystery. The riddle. The enigma.<br />
<br />
Why does the MAX function sometimes perform very poorly compared to TOP 1?<br />
<br />
I really thought that "MAX vs TOP 1" was a simple question. An easy question. A spend 10 seconds on Stack Overflow and get the answer type of question.<br />
<br />
But I just couldn't leave it alone and had to go and test it for myself. And open a veritable Pandora's Box of SQL riddles.<br />
<br />
In Part 1 of this series, I delved into how MAX and TOP 1 behave in several random queries, and I ended on a query that showed MAX performing quite poorly compared to TOP 1.<br />
<br />
After that video, I stumbled across an even simpler query that produced an even more dramatic performance difference, where MAX performed miserably. But I couldn't figure out why.<br />
<br />
In this video, I discuss what I learned about the query and the specific Index Scan that is causing the MAX query to performing so poorly. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<iframe width="320" height="266" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/WlAxbrZ9DaI/0.jpg" src="https://www.youtube.com/embed/WlAxbrZ9DaI?feature=player_embedded" frameborder="0" allowfullscreen></iframe></div>
<br />
Here's the recap:<br />
<br />
When querying some fields, such as the IV30500 POSTEDDT, with no WHERE clause, both MAX and TOP 1 perform virtually the same, with both using a very efficient Index Scan. 50% vs 50% relative costs.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgligv9n2ASGrGLBxh9r18hQcUp3_PExOc7UMZIuPUsGLhPSPmhVUb4lGh9vW7KTfseOyxp3XKy0WwNkD-_5znFPoP8Rbn4dec0zXoBZJqI4Fs7wZt43km9CGNeIESYUrgaMF6S-xgW7XHl/s1600/MAXvsTOP2a1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="652" data-original-width="890" height="292" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgligv9n2ASGrGLBxh9r18hQcUp3_PExOc7UMZIuPUsGLhPSPmhVUb4lGh9vW7KTfseOyxp3XKy0WwNkD-_5znFPoP8Rbn4dec0zXoBZJqI4Fs7wZt43km9CGNeIESYUrgaMF6S-xgW7XHl/s400/MAXvsTOP2a1.jpg" width="400" /></a></div>
<br />
<br />
But when I query the TRXSOURCE field, with no WHERE clause, MAX shows 100% relative cost, whereas TOP 1 shows 0% relative cost.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhS8sYZB0fdX7jjPN9egveLx7INiRd8URFpEyrMk-6S-PZ9BIzQ5mnOIXnfnSbMt0me6ryeapx-saP153PKSjMrk5DoW6EIJ1cE83QLoN5vA98P1q6XyQ3Yi5Lr-1599SjhKtUD0FU1SdLN/s1600/MAXvsTOP2a.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="630" data-original-width="1002" height="251" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhS8sYZB0fdX7jjPN9egveLx7INiRd8URFpEyrMk-6S-PZ9BIzQ5mnOIXnfnSbMt0me6ryeapx-saP153PKSjMrk5DoW6EIJ1cE83QLoN5vA98P1q6XyQ3Yi5Lr-1599SjhKtUD0FU1SdLN/s400/MAXvsTOP2a.jpg" width="400" /></a></div>
<br />
<br />
What???<br />
<br />
The POSTEDDT query uses an Index Scan. The TRXSOURCE query uses an Index Scan. But for some reason with the TRXSOURCE query, MAX is much more costly.<br />
<br />
I stared at this result for a few hours trying to figure out why. I eventually found this tiny little detail.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhe3IARAIc32DP4JqoJ2LZ6ZMq5sp03rTWxlwGfMq-TfEKKJ0oMfScGkkv1MbnmdhPYLkXcIYFSgFbGr5HfL_86roRaPjrQ6FmklePWeHAS8afu0JUDE5rNCzdmNGKLWppp3rdT92CKBTzQ/s1600/MAXvsTOP2b.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="432" data-original-width="833" height="206" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhe3IARAIc32DP4JqoJ2LZ6ZMq5sp03rTWxlwGfMq-TfEKKJ0oMfScGkkv1MbnmdhPYLkXcIYFSgFbGr5HfL_86roRaPjrQ6FmklePWeHAS8afu0JUDE5rNCzdmNGKLWppp3rdT92CKBTzQ/s400/MAXvsTOP2b.jpg" width="400" /></a></div>
<br />
Notice that the Actual Number of Rows for the MAX Index Scan is 147,316. That's every row in the table.<br />
<br />
By contrast, the TOP 1 Index Scan has Actual Number of Rows = 1.<br />
<br />
What is going on?<br />
<br />
For some reason, the MAX is having to scan the ENTIRE AK1IV30500 index. It isn't getting much benefit from the index.<br />
<br />
But why?<br />
<br />
Unfortunately, I don't yet know.<br />
<br />
"Maybe SQL is caching the query execution plan?"<br />
<br />
Apparently not. I tried DBCC FREEPROCCACHE, and saw no change.<br />
<br />
<br />
"Maybe your statistics are stale and need to be updated?"<br />
<br />
Nope. I tried UPDATE STATISTICS WITH FULLSCAN. No change<br />
<br />
<br />
"C'mon Steve, clearly you need to re-index!"<br />
<br />
Did that. I tried DBREINDEX on the specific AK1IV30500 index, as well as the entire IV30500 table. No change in the execution plan.<br />
<br />
<br />
I didn't find any standard maintenance task that changed the behavior of the MAX Index Scan.<br />
<br />
As a last resort, I used the Import/Export wizard to export all of the data from the IV30500 table into a new table that I called IV30500REBUILD. I then ran scripts to create all of the same indexes on the REBUILD table, making it identical to the original IV30500 table.<br />
<br />
I then ran the MAX and TOP 1 queries on the new REBUILD table.<br />
<br />
And like magic, the MAX Index Scan returned just one row.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUt0QGJzMvH_IkCBnxM4IxZC9NuZgCRyLwqKM15OLFeDl7SPh5zWpn5VDsRd_47T4lmXv_msCLONs8R3mZg6-ZnTAK1IOmoj2xLdTAyxfXzRxOE7u78JEaMGJlfntbVbkAzuTxmfd08YJU/s1600/MAXvsTOP2c.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="588" data-original-width="951" height="246" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUt0QGJzMvH_IkCBnxM4IxZC9NuZgCRyLwqKM15OLFeDl7SPh5zWpn5VDsRd_47T4lmXv_msCLONs8R3mZg6-ZnTAK1IOmoj2xLdTAyxfXzRxOE7u78JEaMGJlfntbVbkAzuTxmfd08YJU/s400/MAXvsTOP2c.jpg" width="400" /></a></div>
<br />
Same table structure.<br />
<br />
Same data.<br />
<br />
Same indexes.<br />
<br />
But the Index Scan on the new REBUILD table behaves properly.<br />
<br />
So there is apparently something about my IV30500 that is causing this problem, and rebuilding the entire table resolves it. But rebuilding a table isn't exactly a typical SQL maintenance task, so it's not really a solution.<br />
<br />
But this is way past my SQL skill level, so I don't yet know what conventional maintenance task might be able to achieve the same results.<br />
<br />
I've asked for help from a true SQL expert, so I'm hoping that she will assist and help me figure out this mystery.<br />
<br />
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><a href="https://www.blogger.com/null" name="OLE_LINK1"><i>Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</i></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<i>You can also find him on </i><a href="https://twitter.com/steveendow"><i>Twitter</i></a><span class="apple-converted-space"><i>, </i></span><a href="https://www.youtube.com/steveendow"><i>YouTube</i></a><span class="apple-converted-space"><i>, and </i></span><a href="https://plus.google.com/108067157833601200392?rel=author"><i>Google+</i></a> <o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="http://www.precipioservices.com/"><i>http://www.precipioservices.com</i></a><o:p></o:p></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s1600/MVP+MCITP+Logos+New.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="103" data-original-width="451" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s320/MVP+MCITP+Logos+New.jpg" width="320" /></a></div>
<br />
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-82654674962516692342018-03-03T17:03:00.000-08:002018-03-03T17:46:05.812-08:00"Can you add just one more little feature?": A Story About Software and Home ImprovementBy Steve Endow<br />
<div>
<br /></div>
<div>
<br /></div>
<div>
Wife: "Steve, can you install an exhaust fan in the small bathroom?"</div>
<div>
Steve: "Sure, hunny, no problem. I just ordered the fan and I'll call Sam to install it."</div>
<div>
<br /></div>
<div>
Customer: "Can you add this simple little feature to our application?"</div>
<div>
Developer: "Sure, no problem. I'll get right on that."</div>
<div>
<br /></div>
<div>
<b><i>No big deal.</i></b></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Sam: "Steve, I cut a hole in the bathroom ceiling for the fan, but something is strange. There's an extra layer of drywall on the ceiling, and it's not attached properly and it's sagging."</div>
<div>
Steve: "Hmmm, that doesn't look right. Let's remove the extra drywall and see what the prior homeowner was covering up."</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQsTMhBIIzac0CGptwSGQT3YZEJxZHM0ovi_u5cVukZH_W2JSUEG6H54tQmAZKRJE87JSWqUyVQlt60Q-108kXQKOf97dD8yep0lBS3AyzEBCEu7zPgMTm1vXHlFgHLkE1uqisnERAh6HA/s1600/House1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="761" data-original-width="1050" height="231" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQsTMhBIIzac0CGptwSGQT3YZEJxZHM0ovi_u5cVukZH_W2JSUEG6H54tQmAZKRJE87JSWqUyVQlt60Q-108kXQKOf97dD8yep0lBS3AyzEBCEu7zPgMTm1vXHlFgHLkE1uqisnERAh6HA/s320/House1.jpg" width="320" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Customer: "So how's that new simple little feature coming along?"</div>
<div>
Developer: "Well, I looked through the code, and the original developers didn't design the software to handle this feature, so it's going to require some redesign of the customization."</div>
<div>
<br /></div>
<div>
<b><i>I think the project scope just changed.</i></b></div>
<div>
<a name='more'></a></div>
<div>
<br />
<br /></div>
<div>
Sam: "Steve, I think I found what the extra drywall was covering up. It looks like the old bath tub and old toilet on the second floor that we replaced 3 years ago were leaking."</div>
<div>
Steve: "Okay, so there is probably some old water damage?"</div>
<div>
Sam: "Well...I think it's a little more than that. Looks like lots of mold and some termite damage."</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsM8NKE5mCwb2fD-h6UDkpXlceVRVa5acZ2Ayh-tV3ukHC67eXb35EB6OckH34U-6jG7RTJ-5CboM_7CY64DMYUYThQgPRmBE47LxYg2pteClzjC7y3Eas1cW89Z3DzJ1HQVUAiA1gii-f/s1600/House1b.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="846" data-original-width="583" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsM8NKE5mCwb2fD-h6UDkpXlceVRVa5acZ2Ayh-tV3ukHC67eXb35EB6OckH34U-6jG7RTJ-5CboM_7CY64DMYUYThQgPRmBE47LxYg2pteClzjC7y3Eas1cW89Z3DzJ1HQVUAiA1gii-f/s320/House1b.jpg" width="220" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Customer: "So when you say that it will require some redesign, what does that mean?"</div>
<div>
Developer: "Well, the customization wasn't designed to handle the new functionality, the database tables don't have a field to store data for the new feature, and the user interface doesn't have space for the new feature. Those are the items I've found so far."</div>
<div>
<br /></div>
<div>
<b><i>Requirements are shifting...and growing...</i></b></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1XBV5RFFmrJVPlj0RYrV7IBEEikFS8ishC2DPivO-aawuyrTse_ureh5TlhUfrOzEk4XhHAH2vF_KMzit44rJGQTx3u7I7BDqMx-griC4F2dY-FWESI5HuhsCvrKU-ZfQoNV4HeQkUy98/s1600/House3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="555" data-original-width="881" height="201" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1XBV5RFFmrJVPlj0RYrV7IBEEikFS8ishC2DPivO-aawuyrTse_ureh5TlhUfrOzEk4XhHAH2vF_KMzit44rJGQTx3u7I7BDqMx-griC4F2dY-FWESI5HuhsCvrKU-ZfQoNV4HeQkUy98/s320/House3.jpg" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div>
<br /></div>
<div>
Steve: "So how's it looking?"</div>
<div>
Sam: "Well, I removed the entire bathroom ceiling and one wall. There's quite a bit of termite damage, so we're going to need to replace several studs and rebuild the wall."</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiXpL75x1Ec9YfRi8EGhqDm-ZwBExVGLUUxBGc9uFqMHp8Gq0Rte4YoQxryFPmknsImb9QpZ7gFtjcOuaWWSf-xtzWYxWpe9GInsj-dZkSeF3moSa-jHaRfpo91uSvRkhATZov7JalGgoi8/s1600/House4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="857" data-original-width="629" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiXpL75x1Ec9YfRi8EGhqDm-ZwBExVGLUUxBGc9uFqMHp8Gq0Rte4YoQxryFPmknsImb9QpZ7gFtjcOuaWWSf-xtzWYxWpe9GInsj-dZkSeF3moSa-jHaRfpo91uSvRkhATZov7JalGgoi8/s320/House4.jpg" width="234" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Customer: "So we need to modify the database tables to store the new data, modify the user interface to handle the new data entry, and write a little bit of code?"</div>
<div>
Developer: "Well, after additional review of the old code, I think some of the code needs to be rewritten to meet your new requirements."</div>
<div>
<br /></div>
<div>
<b><i>The magnitude is just starting to sink in.</i></b></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Sam: "It looks like the water damage extends in the back wall behind the sink and vanity. I know you were trying to keep the vanity, but we have to rip it all out."</div>
<div>
Steve: (audible sigh) "Um...okay..."</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4KpHLrocL3HY4ztQR9KFGpCLHvUWJYwZGxcE7072ApULDceYs33doVlwYHmxm7oxwS8Qpg2VCaqI7C2fO34AQ_f-VwiQOdkYFr7R1J1NyGEWvsyYY2lX_7En7-9r1IwjyDeW0GKrxZX98/s1600/House6.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="853" data-original-width="544" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4KpHLrocL3HY4ztQR9KFGpCLHvUWJYwZGxcE7072ApULDceYs33doVlwYHmxm7oxwS8Qpg2VCaqI7C2fO34AQ_f-VwiQOdkYFr7R1J1NyGEWvsyYY2lX_7En7-9r1IwjyDeW0GKrxZX98/s320/House6.jpg" width="204" /></a></div>
<div>
<br /></div>
<div>
Customer: "So how much of the code do you need to rewrite?"</div>
<div>
Developer: "I think it's going to be easier to just write new code from scratch. Retrofitting the existing code will take longer and we'll have to deal with other problems and limitations."</div>
<div>
<br /></div>
<div>
<b><i>The full scope finally emerges.</i></b></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Steve: "So this morning, this started as a simple project to install a bathroom fan. But by lunch time, it turned into a complete gut and rebuild of the entire bathroom, down to the studs."</div>
<div>
Sam: "Ya, pretty much."</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUpvQYhXtxgOwwPA2I3UlVofxMIP9opl3h_iifqD_euMTjrpSGzzVilkSpBKl6Q4wa556gpUqcjCM7jxVSeAfAIdI6LKL9vX5MWLPWUDFygMy9u0xBB9V8tjZzxereu57MDuyllsBE8vKw/s1600/House7.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="823" data-original-width="590" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUpvQYhXtxgOwwPA2I3UlVofxMIP9opl3h_iifqD_euMTjrpSGzzVilkSpBKl6Q4wa556gpUqcjCM7jxVSeAfAIdI6LKL9vX5MWLPWUDFygMy9u0xBB9V8tjZzxereu57MDuyllsBE8vKw/s320/House7.jpg" width="229" /></a></div>
<div>
<br /></div>
<div>
<div>
Customer: "So you're saying in order to add this one feature, we need to rewrite the entire application from scratch?"</div>
<div>
Developer: "Ya, pretty much."</div>
</div>
<div>
<br /></div>
<div>
<b><i>Acceptance.</i></b></div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhU-9J0864YFQCWZ7y5M0-iB-U9eG0ziocBYNz5pikEh0Gdy9zC7S9Ti8O6szJAsd_ecZbtk98x3md1Mnt6Ve1QaQcKoQfeKpjARdzWckkwVgaDkq2Ejyo9h8FhU-yrZ-BDojzJUhl4WP_3/s1600/Money.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="422" data-original-width="548" height="246" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhU-9J0864YFQCWZ7y5M0-iB-U9eG0ziocBYNz5pikEh0Gdy9zC7S9Ti8O6szJAsd_ecZbtk98x3md1Mnt6Ve1QaQcKoQfeKpjARdzWckkwVgaDkq2Ejyo9h8FhU-yrZ-BDojzJUhl4WP_3/s320/Money.jpg" width="320" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><i>Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</i><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<i>You can also find him on </i><a href="https://twitter.com/steveendow"><i>Twitter</i></a><span class="apple-converted-space"><i>, </i></span><a href="https://www.youtube.com/steveendow"><i>YouTube</i></a><span class="apple-converted-space"><i>, and </i></span><a href="https://plus.google.com/108067157833601200392?rel=author"><i>Google+</i></a> <o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="http://www.precipioservices.com/"><i>http://www.precipioservices.com</i></a><o:p></o:p></div>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-25272677141071342862018-03-02T10:51:00.001-08:002019-01-19T11:31:17.445-08:00Sample Dynamics GP eConnect XML for RM Apply (RMApplyType / taRMApply)<span style="color: red;">My blog has moved! Please visit the new blog at: <a href="https://blog.steveendow.com/">https://blog.steveendow.com/</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">I will no longer be posting to Dynamics GP Land, and all new posts will be at <a href="https://blog.steveendow.com/">https://blog.steveendow.com</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">Thanks!</span><br />
<br />
<br />
By Steve Endow<br />
<br />
A customer asked for sample XML for the RMApplyType / taRMApply eConnect transaction type. I couldn't find one handy during a search, so I had to cobble together some .NET code and generate the XML.<br />
<br />
I'm wondering if there is an easier way to generate the sample eConnect XML. In theory, eConnect Requester with the eConnect Outgoing Service can send certain XML documents to MSMQ, but that is a hassle to setup properly, and I don't know that all transaction types are supported by eConnect Requester--such RMApplyType.<br />
<br />
<br />
So, here is a sample Dynamics GP eConnect XML document for RM Apply (RMApplyType / taRMApply)<br />
<br />
<br />
<div class="MsoNormal">
<span style="font-family: "consolas";"><?xml
version="1.0"?><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"><eConnect
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <RMApplyType><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";">
<eConnectProcessInfo xsi:nil="true" /><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";">
<taRequesterTrxDisabler_Items xsi:nil="true"
/><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";">
<taAnalyticsDistribution_Items xsi:nil="true"
/><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";">
<taRMApply><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <APTODCNM>SALES100001</APTODCNM><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";">
<APFRDCNM>PYMNT100001</APFRDCNM><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";">
<APPTOAMT>123.45</APPTOAMT><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";">
<APFRDCTY>9</APFRDCTY><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";">
<APTODCTY>1</APTODCTY><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";">
<DISTKNAM>0</DISTKNAM><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";">
<APPLYDATE>2017-04-12</APPLYDATE><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";">
<GLPOSTDT>2017-04-12</GLPOSTDT><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";">
</taRMApply><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> </RMApplyType></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"></eConnect><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"><br /></span></div>
<br />
<div>
Discount Taken, Apply Date, and GL Post Date are optional, and do not have to be assigned or included in the XML if they are not needed.</div>
<div>
<br /></div>
<div>
Here is the eConnect documentation on taRMApply.</div>
<div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhx9dr496k5RIb3Co7JchZWHnxRNclKfYemE0isf4mgcscoZlzw75_orCGED3yT3eEi9giIZ6WrftF5xqfwtHNpWX5RfvMGD4L3jyoBFAfJP8Q5LCtDNNGmFc_AmszY_LA3lSDEj73okHiw/s1600/taRMApply.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="579" data-original-width="793" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhx9dr496k5RIb3Co7JchZWHnxRNclKfYemE0isf4mgcscoZlzw75_orCGED3yT3eEi9giIZ6WrftF5xqfwtHNpWX5RfvMGD4L3jyoBFAfJP8Q5LCtDNNGmFc_AmszY_LA3lSDEj73okHiw/s400/taRMApply.jpg" width="400" /></a></div>
</div>
<div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><a href="https://www.blogger.com/null" name="OLE_LINK1"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</span></i></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">You can also find him on </span></i></span></span><a href="https://twitter.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Twitter</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, </span></i></span></span></span><a href="https://www.youtube.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i style="mso-bidi-font-style: normal;"><span style="mso-bidi-font-size: 13.5pt;">YouTube</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, and </span></i></span></span></span><a href="https://plus.google.com/108067157833601200392?rel=author"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Google+</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"> <o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="http://www.precipioservices.com/"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">http://www.precipioservices.com</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
</div>
<div>
<br /></div>
Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com12tag:blogger.com,1999:blog-6691994129222744759.post-21968088911874061452018-02-27T22:19:00.001-08:002019-10-29T10:47:26.926-07:00I give away my source code to my customers<span style="background-color: white; color: red; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;">My blog has moved! Please visit the new blog at: <a href="https://blog.steveendow.com/" style="color: #9b8c45; text-decoration-line: none;">https://blog.steveendow.com/</a></span><br style="background-color: white; color: #424242; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;" /><span style="background-color: white; color: red; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><br /></span><span style="background-color: white; color: red; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;">I will no longer be posting to Dynamics GP Land, and all new posts will be at <a href="https://blog.steveendow.com/" style="color: #9b8c45; text-decoration-line: none;">https://blog.steveendow.com</a></span><br style="background-color: white; color: #424242; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;" /><span style="background-color: white; color: red; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;"><br /></span><span style="background-color: white; color: red; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px;">Thanks!</span><br />
<br />
<br />
<br />
By Steve Endow<br />
<br />
Dynamics GP partners and customers often hire me to develop custom Dynamics GP integrations, GP customizations, Visual Studio Tools AddIns for GP, or even custom web APIs for Dynamics GP.<br />
<br />
I develop the solution, usually using .NET, then I prepare a deployment guide and deployment package that can be installed on the customer's servers. The solution is tested, I fix some bugs and refine the solution, I prepare new deployments that get installed, and once everything looks good, the customer goes live.<br />
<br />
Everybody's happy, and I'm all done, right?<br />
<br />
Except for one critical piece.<br />
<br />
Any guesses?<br />
<br />
What about the source code?<br />
<br />
"What about the source code?", you might reply.<br />
<br />
I diligently check in my source code to Git, and the code is pushed to an online Git repository for safe keeping and accessibility. And I have backups of backups of backups, both on site and off site. Great. So I'm all done, right?<br />
<br />
Not really.<br />
<br />
What if I disappear? What if I win the lottery? What if I decide that this whole modern civilization thing is overrated and go live off the grid?<br />
<br />
<a name='more'></a><br />
<br />
In reality, I usually work with customers for years. I've worked with customers as they've upgraded from GP 10 to 2010 to 2013 to 2016. I've worked with customers where most of the accounting department and IT staff have changed. I've worked with customers as they cycle through multiple Dynamics GP partners. I've worked with customers that have been acquired, gone out of business, and migrated to other ERP systems.<br />
<br />
I'm not planning on disappearing, but I've seen what happens when a Dynamics GP developer does disappear. One guy just vanished and the customer couldn't locate him. More often, developers leave a Dynamics GP partner and nobody else at the partner knows where to find the customer's code.<br />
<br />
And then there's the case where customers switch GP partners, and 2 years after the switch they realize that they don't have the source code for a GP customization or integration. This happens all the time.<br />
<br />
So, <b>I give my source code to my customers</b>. If I'm working with a partner, I send the source code to the project manager after each release. If I'm working with a customer, I usually send the source code to someone in IT.<br />
<br />
"But gasp! It's YOUR intellectual property! You legally own the code! Why would you give away your source code!", someone might ask.<br />
<br />
My customers don't hire me to type funny words and inscrutable symbols into a development tool. They aren't paying me to accumulate priceless intellectual property (that actually has no commercial value). And they definitely aren't hiring me so that I can hold them hostage when they upgrade Dynamics GP or switch partners and need an updated version of their integration or customization.<br />
<br />
I like to think that my customers hire me because I provide them with value, and the value I provide isn't a Git repository or zip file containing source code. Sure, the code has value, but there are far better developers out there they could hire if all they needed was a coder.<br />
<br />
Can you guess what happens when I give away my code to my customers? When I send off that email with a link to download my precious source code?<br />
<br />
<b>Absolutely. Nothing.</b><br />
<br />
Nothing happens. It's completely uneventful.<br />
<br />
Customers don't hire a cheaper developer. Customers don't stop hiring me. Customers don't try and maintain the code themselves so that they don't have to pay me. Sometimes they consider supporting the code, but then they realize they don't want to maintain another project. The last thing an overworked IT department wants is to inherit someone else's code that involves the ERP system and debits and credits--no joke.<br />
<br />
But can you guess what happens over time?<br />
<br />
Any questions about "code ownership" disappear. Concerns over access to source code disappear. Tensions around risk and critical dependencies dissolve. Partners and customers feel more comfortable. I think I have better relationships with my customers as a result.<br />
<br />
And take a guess what happens when the partner or customer encounter situations where another developer <u>won't</u> provide the source code. They notice. They <u>really</u> notice.<br />
<br />
"Wait a minute. Steve sends me his source code regularly and it's no big deal. But this other developer <u>refuses</u> to send me his source code."<br />
<br />
When this happens, the partner is not happy and the customer feels exposed or at risk. It changes the relationship.<br />
<br />
So strangely, giving away my code has become a small competitive advantage. Tentative prospects sometimes ask me, "Steve, will this project include delivery of source code?" Absolutely! Once I deliver the code for the first release, concerns disappear.<br />
<br />
Yes, I'm sure there are caveats for some organizations or some situations, and lawyers will gladly argue about IP and contracts and liability and blah blah blah for $500 an hour, but in the real world, the mid-market companies I work with are just looking to get things done. The last thing anyone wants is to involve a lawyer, and nobody wants to worry or even care about source code. I'm not building the next Azure or Axapta or VC backed killer app.<br />
<br />
So when I hear stories about a GP partner who refuses to give source code to a customer who has switched to a new partner, I seriously wonder, "What in the world are they thinking?"<br />
<br />
In the meantime, I'm working on making my customers happy.<br />
<br />
It seems to be working.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOpT0Topy1NfBUmMo4ftZRU9Wzp99v4PH9qOi9uw0BxK_K_giH6Q4sNrV7efZc0jwYhuGeM6tNX6MaDEVyUWyVWuiwdz-EgmjxsOtGWhVIV_5E6_LJGoteSO8wT4tPdijzs3qET7FN0Z3M/s1600/SourceCode.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="262" data-original-width="574" height="182" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOpT0Topy1NfBUmMo4ftZRU9Wzp99v4PH9qOi9uw0BxK_K_giH6Q4sNrV7efZc0jwYhuGeM6tNX6MaDEVyUWyVWuiwdz-EgmjxsOtGWhVIV_5E6_LJGoteSO8wT4tPdijzs3qET7FN0Z3M/s400/SourceCode.jpg" width="400" /></a></div>
<br />
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><a href="https://www.blogger.com/null" name="OLE_LINK1"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</span></i></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">You can also find him on </span></i></span></span><a href="https://twitter.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Twitter</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, </span></i></span></span></span><a href="https://www.youtube.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i style="mso-bidi-font-style: normal;"><span style="mso-bidi-font-size: 13.5pt;">YouTube</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, and </span></i></span></span></span><a href="https://plus.google.com/108067157833601200392?rel=author"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Google+</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"> <o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="http://www.precipioservices.com/"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">http://www.precipioservices.com</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s1600/MVP+MCITP+Logos+New.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="103" data-original-width="451" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s320/MVP+MCITP+Logos+New.jpg" width="320" /></a></div>
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-59665250483542795682018-02-25T19:08:00.004-08:002018-02-25T19:44:51.211-08:00T-SQL: MAX vs. TOP 1 - Which is better??By Steve Endow<br />
<br />
If you need to get the largest value for a field, should you use MAX in your query? Or should you use TOP 1 with ORDER BY?<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCcYk5La6tVvHEC9UPlqAKzhHu07_yTYvGpXtJh42Kgq_2yT_Qj2RetYE_fkpL_AvYIYmDVFKXmw5EpE2IPRni8wvroDO3NZp7CHxuOl27dXfYtZAG79jYPErj5RIMzwVqjmQFNUzTB_Fa/s1600/MAXvsTOP1Example.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="652" data-original-width="1302" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCcYk5La6tVvHEC9UPlqAKzhHu07_yTYvGpXtJh42Kgq_2yT_Qj2RetYE_fkpL_AvYIYmDVFKXmw5EpE2IPRni8wvroDO3NZp7CHxuOl27dXfYtZAG79jYPErj5RIMzwVqjmQFNUzTB_Fa/s400/MAXvsTOP1Example.jpg" width="400" /></a></div>
<br />
<br />
Which is better? Which is faster? Is that always true?<br />
<br />
Do you think you know the answer?<br />
<br />
Place your bets, and then check out my video below, where I compare MAX vs TOP 1 on several Dynamics GP tables.<br />
<br />
The results may surprise you!<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/kv5Sl14aGQ0/0.jpg" frameborder="0" height="266" src="https://www.youtube.com/embed/kv5Sl14aGQ0?feature=player_embedded" width="320"></iframe></div>
<br />
<br />
Did I miss anything or make any mistakes in my testing? Are there other considerations when choosing between MAX vs. TOP 1?<br />
<br />
<a name='more'></a><br /><br />
Here are the queries that I used in my testing. Note that your results will vary depending on how much data you have in your tables and your SQL Server version.<br />
<br />
<br />
<div class="MsoNormal">
<span style="font-family: Consolas;">--MAX vs TOP 1 with ORDER
BY<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SET STATISTICS IO ON<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT MAX(DOCDATE) AS
DOCDATE FROM PM30200 WHERE VENDORID = 'ACETRAVE0001'<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT TOP 1 DOCDATE FROM
PM30200 WHERE VENDORID = 'ACETRAVE0001' ORDER BY DOCDATE DESC <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SET STATISTICS IO OFF<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SET STATISTICS IO ON;<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">WITH cteMaxDate (DOCDATE)
AS<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">(<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;"> SELECT MAX(DOCDATE) FROM PM10000 WHERE VENDORID =
'ACETRAVE0001' <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;"> UNION <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;"> SELECT MAX(DOCDATE) FROM PM20000 WHERE VENDORID =
'ACETRAVE0001' <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;"> UNION<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;"> SELECT MAX(DOCDATE) FROM PM30200 WHERE VENDORID = 'ACETRAVE0001'
<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">)<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT MAX(DOCDATE) AS
DOCDATE FROM cteMaxDate;<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">WITH cteMaxDate2
(DOCDATE) AS<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">(<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;"> SELECT TOP 1 DOCDATE FROM PM10000 WHERE VENDORID =
'ACETRAVE0001' ORDER BY DOCDATE DESC<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;"> UNION <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;"> SELECT TOP 1 DOCDATE FROM PM20000 WHERE VENDORID =
'ACETRAVE0001' ORDER BY DOCDATE DESC<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;"> UNION<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;"> SELECT TOP 1 DOCDATE FROM PM30200 WHERE VENDORID =
'ACETRAVE0001' ORDER BY DOCDATE DESC<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">)<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT MAX(DOCDATE) AS
DOCDATE FROM cteMaxDate2;<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SET STATISTICS IO OFF;<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT COUNT(*) FROM
SEE30303 --73,069 records<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT TOP 10 * FROM
SEE30303<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SET STATISTICS IO ON;<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT MAX(DATE1) AS
DATE1 FROM SEE30303 WHERE ITEMNMBR IN ('ARM', 'FTRUB', 'A100', '24X IDE')<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT TOP 1 DATE1 FROM
SEE30303 WHERE ITEMNMBR IN ('ARM', 'FTRUB', 'A100', '24X IDE') ORDER BY DATE1
DESC <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SET STATISTICS IO OFF;<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SET STATISTICS IO ON;<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT MAX(DATE1) AS
DATE1 FROM SEE30303 <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT TOP 1 DATE1 FROM
SEE30303 ORDER BY DATE1 DESC <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SET STATISTICS IO OFF;<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SET STATISTICS IO ON;<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT MAX(DATE1) AS
DATE1 FROM SEE30303 OPTION (MAXDOP 1) <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT TOP 1 DATE1 FROM
SEE30303 ORDER BY DATE1 DESC OPTION (MAXDOP 1)<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SET STATISTICS IO OFF;<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT COUNT(*) AS Rows
FROM IV30500<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT TOP 100 * FROM
IV30500<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SET STATISTICS IO ON;<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT MAX(POSTEDDT) AS
POSTEDDT FROM IV30500 --OPTION (MAXDOP 1) <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT TOP 1 POSTEDDT
FROM IV30500 ORDER BY POSTEDDT DESC --OPTION (MAXDOP 1)<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SET STATISTICS IO OFF;<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SET STATISTICS IO ON;<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT MAX(POSTEDDT) AS
POSTEDDT FROM IV30500 WHERE ITEMNMBR IN ('ARM', 'FTRUB', '100XLG') AND POSTEDDT
BETWEEN '2017-01-01' AND '2017-12-31' --OPTION (MAXDOP 1) <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT TOP 1 POSTEDDT
FROM IV30500 WHERE ITEMNMBR IN ('ARM', 'FTRUB', '100XLG') AND POSTEDDT BETWEEN
'2017-01-01' AND '2017-12-31' ORDER BY POSTEDDT DESC --OPTION (MAXDOP 1)<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SET STATISTICS IO OFF;<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SET STATISTICS IO ON;<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT MAX(TRXSORCE) AS
POSTEDDT FROM IV30500 WHERE ITEMNMBR IN ('ARM', 'FTRUB', '100XLG') <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SELECT TOP 1 TRXSORCE
FROM IV30500 WHERE ITEMNMBR IN ('ARM', 'FTRUB', '100XLG') ORDER BY TRXSORCE
DESC <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">SET STATISTICS IO OFF;<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">USE [TWO]<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">GO<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">CREATE NONCLUSTERED INDEX
NCI_IV30500_ITEMNMBR<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">ON [dbo].[IV30500]
([ITEMNMBR])<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">INCLUDE ([TRXSORCE])<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">GO<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">USE [TWO]<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">GO<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">DROP INDEX
IV30500.NCI_IV30500_ITEMNMBR<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas;">GO<o:p></o:p></span></div>
<br />
<div class="MsoNormal">
<br /></div>
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</span></i></span><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">You can also find him on </span></i></span></span><a href="https://twitter.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Twitter</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, </span></i></span></span></span><a href="https://www.youtube.com/steveendow"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i style="mso-bidi-font-style: normal;"><span style="mso-bidi-font-size: 13.5pt;">YouTube</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span class="apple-converted-space"><i><span style="color: black; mso-bidi-font-size: 13.5pt;">, and </span></i></span></span></span><a href="https://plus.google.com/108067157833601200392?rel=author"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">Google+</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"> <o:p></o:p></span></span></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"></span></span><a href="http://www.precipioservices.com/"><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><i><span style="mso-bidi-font-size: 13.5pt;">http://www.precipioservices.com</span></i></span></span></a><span style="mso-bookmark: OLE_LINK1;"><span style="mso-bookmark: OLE_LINK2;"><span style="color: black; mso-bidi-font-size: 13.5pt;"><o:p></o:p></span></span></span></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s1600/MVP+MCITP+Logos+New.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="103" data-original-width="451" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s320/MVP+MCITP+Logos+New.jpg" width="320" /></a></div>
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0tag:blogger.com,1999:blog-6691994129222744759.post-18317510252279082382018-02-25T16:19:00.003-08:002019-01-19T11:31:33.288-08:00How to improve Dynamics GP with a little bit of VBA<span style="color: red;">My blog has moved! Please visit the new blog at: <a href="https://blog.steveendow.com/">https://blog.steveendow.com/</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">I will no longer be posting to Dynamics GP Land, and all new posts will be at <a href="https://blog.steveendow.com/">https://blog.steveendow.com</a></span><br />
<span style="color: red;"><br /></span><span style="color: red;">Thanks!</span><br />
<br />
<br />
By Steve Endow<br />
<br />
I've had a few Dynamics GP customers that purchase software from me every few years, and a few of them have mailed checks to my old mailing address from 4 years ago. How can this happen?<br />
<br />
Well, the Dynamics GP Payables Transaction Entry window does not display the vendor Remit To address, so verifying the vendor address is not an obvious natural step in the invoice entry process. Yes, there is a link to open the Vendor Address Maintenance window, but what if internal controls prevents the user who enters vendor invoices from editing vendor addresses? The user would need to go through a separate process to verify the vendor address...for every invoice. Not ideal.<br />
<br />
How can VBA help?<br />
<br />
In just a few minutes, VBA can be added to the Payables Transaction Entry window to check if the vendor has not had a transaction in over 60 days, prompt the user to verify the vendor address, and even open the Vendor Inquiry window to review the current Remit To address in Dynamics GP.<br />
<br />
It's really easy!<br />
<br />
Here's a video discussing the background and walking through the entire process of adding the VBA to Dynamics GP.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/nM6sh_603yk/0.jpg" frameborder="0" height="266" src="https://www.youtube.com/embed/nM6sh_603yk?feature=player_embedded" width="320"></iframe></div>
<br />
<br />
<br />
First, within Dynamics GP, you add the desired windows to Visual Basic by clicking on Tools -> Customize -> Add Current Window to Visual Basic.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJlifnyaQ0E4i_SfGdh_UGzJmsWqXB26R8Zpfa9EMMoo7HdMjzaI2DHl9AsbWkip-K8a6k9IHMjkqIYjtpFxtINnfJT-6N5L02okzcuBXYJbaf5byF4TkuKRe7Wxmim4lhbDnTmrIdQIoS/s1600/VBAAddWindow.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="369" data-original-width="881" height="166" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJlifnyaQ0E4i_SfGdh_UGzJmsWqXB26R8Zpfa9EMMoo7HdMjzaI2DHl9AsbWkip-K8a6k9IHMjkqIYjtpFxtINnfJT-6N5L02okzcuBXYJbaf5byF4TkuKRe7Wxmim4lhbDnTmrIdQIoS/s400/VBAAddWindow.jpg" width="400" /></a></div>
<br />
After the window is added, I click on Add Fields to Visual Basic and then click on the Vendor ID field.<br />
<br />
Since I am assuming that the user entering invoices will not have access to edit vendors or vendor addresses, I'm going to add the Vendor Inquiry window to Visual Basic and add 3 fields on the Inquiry window to VB: Vendor ID, Address ID, and the Address ID Next button.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxW1Qd3i8SxEclHmo_VK1aX110pgBDiLREKVObGcoH9dBxAKUO6CjsPStgMezjpMlSJ9gHPgZxlKSWEH4O_JSPsJq-ZXOBRiM-5Uum6uLnRfwAMTKDsgCtr9fWCHbEIt2KCcrBSNPhxfz_/s1600/VBAVendorInquiry.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="353" data-original-width="639" height="220" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxW1Qd3i8SxEclHmo_VK1aX110pgBDiLREKVObGcoH9dBxAKUO6CjsPStgMezjpMlSJ9gHPgZxlKSWEH4O_JSPsJq-ZXOBRiM-5Uum6uLnRfwAMTKDsgCtr9fWCHbEIt2KCcrBSNPhxfz_/s400/VBAVendorInquiry.jpg" width="400" /></a></div>
<br />
Once I have those windows and fields added to Visual Basic, I press CTRL+F11 to open the Dynamics GP Visual Basic Editor. If you don't have access to the VB Editor, you may not be licensed to use it, or you may not have permissions--in which case, talk with your GP administrator or GP partner.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhL5PpDYq3jgaxEJiAAtCUlgJhHzBCIHiZ2LPJsAY_RS-HNWwgl-Ezf-6JZyBEMfPx7u5oDlTdk2CXzIRRLRcjN4VzySlSP73wImBVyiDejDAe4hxtj7QtJIpRPwOn2vD_MVIrq4h7Y5gHs/s1600/VBAEditor.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="560" data-original-width="1078" height="207" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhL5PpDYq3jgaxEJiAAtCUlgJhHzBCIHiZ2LPJsAY_RS-HNWwgl-Ezf-6JZyBEMfPx7u5oDlTdk2CXzIRRLRcjN4VzySlSP73wImBVyiDejDAe4hxtj7QtJIpRPwOn2vD_MVIrq4h7Y5gHs/s400/VBAEditor.jpg" width="400" /></a></div>
<br />
In the VB Editor window, I'll select the PayablesTransactionEntry window on the left, then select the VendorID field and the AfterUserChanged event.<br />
<br />
I wrote the simple VBA code below to demonstrate how you can quickly and easily add some VBA to add some valuable functionality to Dynamics GP to save users time and improve data entry.<br />
<br />
The code finds the most document date for any vendor transaction in Dynamics GP, and if that date is over 60 days ago, it opens the Vendor Inquiry window and displays the vendor Remit To address for the user to review and verify.<br />
<br />
<br />
<div class="MsoNormal">
<span style="font-family: "consolas";">Private Sub
VendorID_AfterUserChanged()<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> Dim strVendorID As String<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> Dim strSQL As String<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> Dim dtLastDocDate As Date<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> strVendorID = VendorID.Value<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> 'Find the most recent document date for the
vendor<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> strSQL = "SELECT
COALESCE(MAX(DOCDATE), '1900-01-01') AS DOCDATE FROM PM00400 WHERE VENDORID =
'" & strVendorID & "'"<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> Set oConn = UserInfoGet.CreateADOConnection<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> oConn.DefaultDatabase =
UserInfoGet.IntercompanyID<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> Set rsResult = oConn.Execute(strSQL)<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> strLastDocDate =
Trim(rsResult.Fields("DOCDATE").Value)<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> rsResult.Close<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> 'Get the Remit To Address ID for the vendor<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> strSQL = "SELECT VADCDTRO FROM PM00200
WHERE VENDORID = '" & strVendorID & "'"<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> Set rsResult = oConn.Execute(strSQL)<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> strRemitID =
Trim(rsResult.Fields("VADCDTRO").Value)<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> rsResult.Close<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> oConn.Close<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> dtLastDocDate = CDate(strLastDocDate)<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> 'If Doc Date is 1/1/1900, vendor has no
transactions<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> If dtLastDocDate =
CDate("1900-01-01") Then<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> Exit Sub<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> Else<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> intDays = DateDiff("d",
dtLastDocDate, DateTime.Date)<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> 'If the last doc date is > X days
ago, display a dialog<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> If intDays > 60 Then<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> msgResult = MsgBox("This
vendor has not had a transaction since " & strLastDocDate & "
(" & intDays & " days ago)." & vbNewLine &
vbNewLine & "Please review the current vendor Remit To address and
compare to the invoice address", vbOKOnly, "Verify Vendor
Address")<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> VendorInquiry.Open<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> VendorInquiry.VendorID.Value =
strVendorID<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> VendorInquiry.Activate<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> VendorInquiry.Show<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> While VendorInquiry.AddressID.Value
<> strRemitID<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> VendorInquiry.NextButtonWindowArea.Value
= 1<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> Wend<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> End If<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "consolas";"> End If<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<br />
<div class="MsoNormal">
<span style="font-family: "consolas";">End Sub<o:p></o:p></span></div>
<br />
<br />
In just a few minutes, you can have this customization running in Dynamics GP without any additional development tools.<br />
<br />
If you have more complex requirements, you can easily add more advanced functionality using VBA. If you prefer using a separate development tool, you could also develop this customization using .NET or Dexterity, but the appeal of VBA is its simplicity and ease of use.<br />
<br />
So if you have some small problem or additional business requirement that you'd like to handle in Dynamics GP, VBA might come in handy.<br />
<br />
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<a href="https://www.blogger.com/null" name="OLE_LINK2"></a><a href="https://www.blogger.com/null" name="OLE_LINK1"><i>Steve Endow is a Microsoft MVP in
Los Angeles. He is the owner of Precipio Services, which provides
Dynamics GP integrations, customizations, and automation solutions.</i></a><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<i>You can also find him on </i><a href="https://twitter.com/steveendow"><i>Twitter</i></a><span class="apple-converted-space"><i>, </i></span><a href="https://www.youtube.com/steveendow"><i>YouTube</i></a><span class="apple-converted-space"><i>, and </i></span><a href="https://plus.google.com/108067157833601200392?rel=author"><i>Google+</i></a> <o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<br />
<div style="margin-bottom: .0001pt; margin: 0in;">
<i><a href="http://www.precipioservices.com/">http://www.precipioservices.com</a></i><o:p></o:p></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s1600/MVP+MCITP+Logos+New.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="103" data-original-width="451" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhouA0ZuLZfMVTNMgmQdT2D3pXta12MnM1gstslaPtEUNjuxv6G81kIbp7vVYPUNVg-wzzW5Ua1bHwoTu35hsBJKILw1h9W9rjPoaVQH_ZiVYkuKIuwxLKSiBCDuYMi6QOOFhyQmEb5gLeU/s320/MVP+MCITP+Logos+New.jpg" width="320" /></a></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<br />Steve Endowhttp://www.blogger.com/profile/03950475674093020502noreply@blogger.com0