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.


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+