Monday, November 26, 2018

New Blog at steveendow.com

If you are following this blog through an RSS feed or other automated system, please update your feed to:

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!

Thursday, October 4, 2018

I'm a kid in the Microsoft Candy Store

My 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!



View the original post at:  https://blog.steveendow.com/2018/10/im-kid-in-microsoft-candy-store.html


By Steve Endow

It's a fantastic time to be working with Microsoft products.

Let's start with Microsoft Azure.  Open this web page and read the list.

https://azure.microsoft.com/en-us/services/

Just look at that list.  It's pretty long, so you'll need to scroll down.  And scroll some more.  Keep scrolling.  And scrolling.



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.


Next, there is the Power Platform.

Monday, September 17, 2018

My Experience with ACH Fraud: My bank account was empty in 3 days

My 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!



By Steve Endow


NOTE: 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.



Update:  On his blog post sharing this article, 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 Small Business bank accounts have no such services that could be used to prevent the type of ACH fraud that I experienced.

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 anything I can do to prevent this type of ACH fraud.  They both clearly and definitively said that there is absolutely nothing I can do to prevent such random fraudulent ACH transactions.  

I asked if they could block all 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.

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.



Update 2:  Reviewing the Bank of America web site (since the employees were of no help), it appears that they have a "Full Analysis Business Checking" account offering that might 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.  

Based on a review of this PDF form, 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.

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.

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 currently have no need to park $60,000 in my business bank accounts, so such account features are presumably not available to me.

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.



Update 3:  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.  

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 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 checked my email on Friday morning and saw a pretty standard email alert from my bank.


Hi Steve, an electronic withdrawal was made above your chosen alert limit:

Amount: $719.60
Type: ELEC DRAFT (ACH)
Account: Business Account *******1234
Merchant: CHASE CREDIT CRD EPAY

Transaction date: September 07, 2018


Hmmm, that's odd.

I don't pay my credit card using ACH.  And I definitely don't pay my credit card from that particular business bank account.

I thought to myself:  It's finally happening.  I've been waiting for it to happen for years, and now it is actually happening.

By Wednesday morning, my bank account was completely empty due to fraudulent ACH withdrawals.

Monday, July 2, 2018

Move Forward, Every Day. And Bring Chocolate Cake.

My 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!


By Steve Endow


Change Is Hard

It's trite, I know.  But it doesn't make it less true.

I think it's worthwhile to pause and take a moment to consider how difficult change can be.  Actually acknowledge it.

Change can be uncomfortable, scary, and stressful.  Change can be costly and arduous and tiring.  Change can be inherently destructive.

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.

In the ERP world, consultants see the symptoms of change regularly when customers implement a new ERP system.

"My old system did X, why can't the new system do X?"

"I just don't have time to learn this new system. I have too much work to do!"

"I liked the old system better."



Turnabout is Fair Play

But occasionally, it seems that those pesky consultants get a taste of change.

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.

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.

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.


Free GP Transaction Search v2.0 now available with new RM and SOP search windows!

My 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!



By Steve Endow

Version 2.0 of the FREE Dynamics GP Transaction Search is now available!

A big thanks to Ian Grieve for assistance with the development and testing of this new version!


For more information and to download compiled releases:



Here is a brief video showing the simple installation process and an overview of the search windows:




The latest source code is available on GitHub:



This new release includes two new search windows:

  • RM Transaction Search
  • SOP Transaction Search

Monday, June 11, 2018

Opening Dynamics GP Windows using .NET and Visual Studio Tools

My 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!



By Steve Endow

When you develop a Dynamics GP customization, you may want to automatically open Dynamics GP windows to add dramatic flair to your application.

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)

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.


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.

Why not just leverage Dynamics GP windows to view that information?

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.

Here's a full video with a code review of opening GP windows from .NET using Visual Studio Tools:




Fortunately, adding this drill-down functionality to a VS Tools AddIn for Dynamic GP is relatively easy.  For some windows.

For other windows, it requires a slightly more involved process.

And, unfortunately, for some windows, it may be very difficult or impossible.

Tuesday, June 5, 2018

Dynamics GP Transaction Search v1.0 is Available! For FREE!

My 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!



By Steve Endow

A few weeks ago, a question was posted to the GPUG Open Forum asking why the AP Transaction Inquiry by Document was so slow.


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.

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.

That. Is. Crazy.

I was...intrigued.

What would cause a seemingly simple Dynamics GP inquiry window to take 3 minutes just to open?  That's just weird.

So I decided to test the window and trace the SQL activity that occurs when the window performs a search.

It...was...bad.  The PM Transaction Inquiry Document window throws out SQL queries like they're going out of style.


Every single PM transaction record that is retrieved by the inquiry window results in at least 3 subsequent queries.  FOR.  EACH.  RECORD.

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.

So, what would an ideal PM Transaction Inquiry window look like?

How about this:



A window that allows you to search by date range AND vendor ID AND vendor name AND document number AND document amount.  All simultaneously.

Here is a video explaining the project and demonstrating the PM Transaction Search window that is included in version 1.0:




As you type each character, this magical search window calls a SQL stored procedure and updates the search results.  In real time.

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.

We're talking real-world accountability here, folks.

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.

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.

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.

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.



You must be thinking: Wow, with all of those features, that fancy search window must cost a fortune!

But that's where you're wrong!  The Dynamics GP Transaction Search tool is available for FREE!


Here is the temporary link to download the search tool from OneDrive:

https://1drv.ms/f/s!Au567Fd0af9TogwHx8OKAarDM3Zm


UPDATE: I now have a dedicated page for the GP Transaction Search in the Free Products sections of my web site at:

https://precipioservices.com/free/gp-transaction-search/



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:

https://github.com/steveendow/gp-transaction-search


If you are a .NET developer or a Dexterity developer and are interested in helping enhance GP Transaction Search, please contact me!

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, please contact me!


https://precipioservices.com/contact-us/




You can also find him on Twitter, YouTube, and Google+







Monday, June 4, 2018

"There's nothing wrong with our SQL Servers or network", says the IT department confidently

My 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!



By Steve Endow

Let me share a story about an all-to-common situation in the life of the Dynamics GP consultant or developer.

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.

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:

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)


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 connecting to the SQL Server.


"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?"

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."


Oooookay then.


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.


When both integrations are run manually at 10am, they both run fine.  So this is not a fluke, and it is occurring consistently now.


"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?"


IT Manager, 1 minute later:  "I was able to figure out the cause of the problem.  We are shutting down our GP SQL Server every night from 10pm to 5am."


Face.  Palm.


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.

But usually, the problem is less obvious and much more difficult to track down.

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)

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.

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.

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.

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.

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.




You can also find him on Twitter, YouTube, and Google+




Tuesday, May 29, 2018

Dramatically Improve Dynamics GP eConnect Performance, But There's a Catch

My 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!




By Steve Endow

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.

Here's an example of the serialization of a GL transaction.


Notice that this code involves a single GL transaction object, glTrans, with the glTrxHeader and glTransLines assigned to the object properties.

That single GL transaction is then assigned as a single element to the glType array, which is then converted to XML.

Wait a minute.  Why is the GLTransactionType object, in this case named glType, an array?

Well, it's an array because eConnect Serialization supports the conversion of multiple transactions into a single XML document.

What?

So...why would you want to do that?  Why serialize several transactions instead of one transaction at a time?

Good question!

Here is a video where I discuss this feature and demonstrate the performance:



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.

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.

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.

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.

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.

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.

But then, for some unknown reason, I figured I should test single vs. multiple transaction submissions and verify my long-held 'belief'.

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.

When I saw the results of sending multiple transactions to eConnect in a single submission...

My...

Jaw...

Dropped.

100 GL JE transactions imported in 17 seconds when sent individually to eConnect.

But when I submitted a single XML document with 100 journal entries, eConnect imported them in 3 seconds.

WHAT???

I then imported 700 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.


That's a 7 fold increase in performance, resulting from a minor change in the import code.  That is a staggering difference.

I repeated the tests several times and confirmed that it wasn't a fluke.

I then tested with AP Invoices and saw the same results.  In this test, I was able to import 750 AP Invoices in a single submission in the same amount of time as submitting 100 AP Invoices individually.  That is cra-zee.



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.

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.

But....

There is a catch.

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.

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.

So, given these drawbacks, what's the conclusion?

My personal recommendation is that most 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.

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.

I hope this was informative!



You can also find him on Twitter, YouTube, and Google+






Friday, May 18, 2018

Get Next Dynamics GP SOP Transaction Number Using SQL

My 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!



By Steve Endow

As I mentioned in a recent post, some times it's better to use SQL to retrieve the next document number from Dynamics GP.

I am currently working on an eConnect SOP Order import and needed to retrieve the next order number from GP.

Here is my C# code for retrieving the next SOP number from GP using SQL.



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.


public string GetNextSOPNumber(int sopType, string docTypeID)
{
    //SOP Type:  
    //1 = Quote
    //2 = Order
    //3 = Invoice
    //4 = Return
    //5 = Back Order
    //6 = Fulfillment Order

    try
    {
        SqlConnection gpConn = ConnectionGP();

        string commandText = "taGetSopNumber";

        SqlParameter[] sqlParameters = new SqlParameter[5];
        sqlParameters[0] = new SqlParameter("@I_tSOPTYPE", System.Data.SqlDbType.TinyInt);
        sqlParameters[0].Value = sopType;
        sqlParameters[1] = new SqlParameter("@I_cDOCID", System.Data.SqlDbType.Char, 15);
        sqlParameters[1].Value = docTypeID;
        sqlParameters[2] = new SqlParameter("@I_tInc_Dec", System.Data.SqlDbType.TinyInt);
        sqlParameters[2].Value = 1;
        sqlParameters[3] = new SqlParameter("@O_vSopNumber", System.Data.SqlDbType.Char, 21);
        sqlParameters[3].Value = string.Empty;
        sqlParameters[3].Direction = ParameterDirection.Output;
        sqlParameters[4] = new SqlParameter("@O_iErrorState", System.Data.SqlDbType.Int);
        sqlParameters[4].Direction = ParameterDirection.Output;
        sqlParameters[4].Value = 0;

        string nextNum = string.Empty;
        int recordCount = DataAccess.ExecuteNonQuery(gpConn, Controller.Instance.Model.GPDatabase, CommandType.StoredProcedure, commandText, sqlParameters);
        nextNum = sqlParameters[3].Value.ToString().Trim();

        return nextNum;
    }
    catch (Exception ex)
    {
        Log.Write("An unexpected error occurred in GetNextSOPNumber: " + ex.Message, true);
        return string.Empty;
    }
}





You can also find him on Twitter, YouTube, and Google+





Friday, May 4, 2018

Consulting is never boring!

By Steve Endow

Today I had to switch between several tasks, and during one of those task switches, my brain put on the brakes.


My brain:  This is crazy!

Me:  What is crazy?

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!

Me:  Uh, hello, we do this every day. So that you're not bored, remember?

Brain:  Dude, that doesn't make it any less crazy.

Me:  Noted.  I'll blog about it just to make you feel better.


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.

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.



Let's start with SQL.  In the Dynamics GP consulting world, a basic understanding of SQL Server and T-SQL is pretty much essential. 

"Hey intern, can you run this query?"

"What's a query?"

"It's a way to get data out of SQL Server."

"SQL Server?"

"Yes, SQL Server is a relational database."

"Relational?"

"Nevermind, just launch Management Studio and connect to the GP SQL instance"

"GP SQL instance?"


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.

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?

If you're on the bleeding edge, you'll know how to backup SQL Server databases to Azure.  Which means you should be familiar with SQL jobs and Azure Storage and backup compression.  And Azure is an entire universe of knowledge.

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?

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.

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.

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.

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.

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.

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.


It's kinda crazy.

But that's also why I like it. 




You can also find him on Twitter, YouTube, and Google+





Wednesday, May 2, 2018

Get Next Dynamics GP RM Payment Number Using SQL

By Steve Endow

When you import transactions into Dynamics GP, you often need to get the next transaction number or document number from Dynamics GP.

In some simple cases, you can leave the document number field blank and let eConnect get the next number for you, 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.

eConnect does have a method to generate the next number, 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.

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.

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.


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.


        public static bool GetNextRMPaymentNumber(string gpDatabase, ref string nextPayment)
        {
            //eConnect method, which uses Windows auth
            //Microsoft.Dynamics.GP.eConnect.GetNextDocNumbers nextDoc = new Microsoft.Dynamics.GP.eConnect.GetNextDocNumbers();
            //string nextRMPayment = nextDoc.GetNextRMNumber(Microsoft.Dynamics.GP.eConnect.IncrementDecrement.Increment, Microsoft.Dynamics.GP.eConnect.RMPaymentType.RMPayments, ConnectionStringWindows(gpDatabase));
            //return nextRMPayment;

            //SQL method
            string commandText = "taGetPaymentNumber";

            SqlParameter[] sqlParameters = new SqlParameter[4];
            sqlParameters[0] = new SqlParameter("@I_vDOCTYPE", System.Data.SqlDbType.TinyInt);
            sqlParameters[0].Value = 9;  //9 = Payment
            sqlParameters[1] = new SqlParameter("@I_vInc_Dec", System.Data.SqlDbType.TinyInt);
            sqlParameters[1].Value = 1;  //1 = Increment
            sqlParameters[2] = new SqlParameter("@O_vDOCNumber", System.Data.SqlDbType.VarChar, 21);
            sqlParameters[2].Direction = ParameterDirection.InputOutput;
            sqlParameters[2].Value = string.Empty;
            sqlParameters[3] = new SqlParameter("@O_iErrorState", System.Data.SqlDbType.Int);
            sqlParameters[3].Direction = ParameterDirection.InputOutput;
            sqlParameters[3].Value = 0;

            int recordCount = DataAccess.ExecuteNonQuery(gpDatabase, CommandType.StoredProcedure, commandText, sqlParameters);

            if (int.Parse(sqlParameters[3].Value.ToString()) == 0)
            {
                if (sqlParameters[2].Value.ToString().Trim() != string.Empty)
                {
                    nextPayment = sqlParameters[2].Value.ToString().Trim();
                    return true;
                }
                else
                {
                    nextPayment = string.Empty;
                    return false;
                }
            }
            else
            {
                return false;
            }

        }


I prefer using this method, as it will work whether I am using Windows Auth or SQL auth.



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.

You can also find him on Twitter, YouTube, and Google+


Friday, April 20, 2018

The Challenge of Posting Dates with Automated Dynamics GP Imports

By Steve Endow

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.


A customer calls and asks, "Why did my April invoice post to the GL in March? The invoice is clearly dated April 5!"

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.


As if that isn't enough fun related to dates, things can get particularly interesting and challenging with automated integrations with Dynamics GP.

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.

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.

That sounds easy enough, right?

Unfortunately, it isn't always easy.


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.

But what about this sample data.


This morning a concerned and upset customer called me asking "Why did our April invoices post to March??"

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.

"But we don't import a posting date with our invoices. Only the Invoice (document) date!", the customer responded.

Good point.  The source data only contained DOCDATE, and their SmartConnect map was only setup to import the invoice Document Date field.

So why did all of their invoices in the 20180401 batch get posted to March 31?

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.

So using the sample data above, why did the 20180401 batch post to March 31?

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.

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.

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.  And all of the invoices in that batch will post to March 31.  Even if the invoice date is April 1.

Okay, so the customer just needs to fix the March 31 dates, right?

Perhaps it may be that simple.  Maybe there was a bug in their source data.

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.

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.

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 prior fiscal period may show up in your source data.  It can be surprisingly tricky.


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.

You can also find him on Twitter, YouTube, and Google+







Monday, April 16, 2018

My First SQL Saturday event: It was amazing

By Steve Endow

The Microsoft SQL Server community is amazing.

Amazing.

That's not an exaggeration or platitude.

On Saturday, I attended my first "SQL Saturday" event in Orange County, California.  I left speechless.


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.

And I should mention that the event was free for attendees.  Completely free.

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.

The event is organized by PASS, a non-profit organization that helps support people who use Microsoft data technologies.

Ten companies sponsored the SQL Saturday event, which has the following mission statement:

Our Mission
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:
  • Encourage increased membership for the local user group
  • Provide local SQL Server professionals with excellent training and networking opportunities
  • Help develop, grow, and encourage new speakers
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.

Several of the speakers that I saw were simply amazing.

Here are the sessions that I attended:

  1. SQL Database and Query Design for Application Developers
  2. Azure Basics for the DBA
  3. PowerShell for the SQL DBA
  4. Spotlight on SQL Server by Quest Software (vendor presentation)
  5. Data Pages, Allocation Units, IAM Chains
  6. The Query Store and SQL Tuning
  7. Fundamentals That Will Improve Query Performance


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?!?!

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.

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.

It was amazing.

If you work with SQL Server and have an opportunity to attend a SQL Saturday event, I recommend it.


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.

You can also find him on Twitter, YouTube, and Google+