Thursday, November 29, 2018

How long does it take to import a Dynamics GP GL Journal Entry with LOTS of lines?

By Steve Endow

I received a question about the performance of eConnect when importing large GL Journal Entries, such as a JE with 1,000 lines.

In some prior eConnect / GP load testing, I had only imported JEs with up to 500 lines, so I didn't know the answer.

I just fired up my Batch Load Test import tool and tested some eConnect imports of GL JEs with lots of lines.

Here's what I found.  Your import times may vary depending on your environment, but this is some baseline data to consider.

Importing a single JE with these line counts took:

500 lines:  2 seconds
1,000 lines:  4 seconds
2,000 lines:  8 seconds
4,000 lines:  16 seconds
8,000 lines:  31 seconds
10,000 lines:  39 seconds
15,000 lines:  54 seconds
20,000 lines:  77 seconds



So it looks like eConnect can pretty easily import a standard JE with quite a few lines without any issues.

CAN you import a JE with 20,000 lines?  Yes.

SHOULD you import a JE with 20,000 lines?  I would not recommend it as a routine process.  Break up the data into more manageable JE sizes.  If nothing else, it will make reconciliations and research more manageable.


Note that this is a standard GL JE without Analytical Accounting.  In my experience, eConnect performance for a JE with AA is horrible.  Definitely keep your JEs small and your batches small when importing with AA data.


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 and YouTube




Friday, November 16, 2018

My source code control is better than yours!

By Steve Endow

Today I received an email from someone who had just inherited a GitHub account from a former employee.  He asked, "What do I do with this?"

His organization is not a "development" shop.  They support business systems, ERP systems, SQL Servers, SharePoint, PowerApps, SSRS, etc.  They don't code C# and JavaScript and Python.

So the GitHub repositories under this account were being used to store various files that are ostensibly "source code", but they were not Visual Studio projects with 6 branches and 5 developers flinging code around.  They were files like SQL scripts.

One question was: How do I access and work with SQL scripts in GitHub?  Can I use SQL Server Management Studio to access the files in GitHub?

Interesting question.  I don't think SSMS can work with GitHub, but...

At GPUG Summit 2018 in Phoenix, Jonathan Cox demonstrated during the SQL Server shootout that VS Code can be used to work with SQL scripts, and it can also work with GitHub.  So in theory, you could use VS Code to work with SQL scripts in GitHub.

But what about the other file types that are not related to VS Code?

Thursday, November 15, 2018

.NET code to create a Dynamics GP batch with eConnect

By Steve Endow

I just posted a story about how I discovered that sometimes I need to explicitly create Dynamics GP batches using eConnect in order to control the batch posting date.

Here's the code that I used.

First, I check to see if the batch exists.


 public static bool BatchExists(string database, int series, string batchSource, string batchID)   
 {  
   try  
   {  
     string commandText = "SELECT COUNT(*) AS RecordCount FROM dbo.SY00500 WHERE SERIES = @SERIES AND BCHSOURC = @BCHSOURC AND BACHNUMB = @BACHNUMB"; // AND GLPOSTDT = @GLPOSTDT";  
     SqlParameter[] sqlParameters = new SqlParameter[3];  
     sqlParameters[0] = new SqlParameter("@SERIES", System.Data.SqlDbType.Int);  
     sqlParameters[0].Value = series;  
     sqlParameters[1] = new SqlParameter("@BCHSOURC", System.Data.SqlDbType.VarChar, 15);  
     sqlParameters[1].Value = batchSource.Trim();  
     sqlParameters[2] = new SqlParameter("@BACHNUMB", System.Data.SqlDbType.VarChar, 15);  
     sqlParameters[2].Value = batchID.Trim();  
     string result = DataAccess.ExecuteScalar(database, CommandType.Text, commandText, sqlParameters);  
     int recordCount = Convert.ToInt32(result);  
     if (recordCount > 0)  
     {  
       return true;  
     }  
     else  
     {  
       return false;  
     }  
   }  
   catch (Exception ex)  
   {  
     Log.Write("An unexpected error occurred in DataAccess.BatchExists: " + ex.Message, true);  
     return false;  
   }  
 }  


If the batch does exist, I make sure to update the posting date.

Sometimes you have to explicitly create Dynamics GP Batches with eConnect

By Steve Endow

You just can't know everything.

Maybe I never took the time to look at this one field when using that particular Dynamics GP batch posting setting, and never knew about this quirk.

Maybe I did know about this quirk at some point over the last 12 or 13 years working with eConnect, but forgot about it.

Either way, I didn't know about it when I needed to know about it.

Last week I had a call with a customer to try and troubleshoot a weird posting date issue.  They import a bunch of AP Invoices every Monday morning into dozens of batches, review the batch that Monday or Tuesday, and then post the batch.  Simple, right?

But when they were reconciling the GL for their month end close, they were seeing AP Invoice transactions post to prior weeks and prior fiscal periods.  An invoice imported on November 5 posted to October 30.  Another invoice imported on November 5 posted to November 1.

It was weird.

We looked at their GP posting settings for Payables Transaction Entry, and it looked pretty normal.

Typical Batch Posting Date

The customer is using the Posting Date from the Batch.  Okay, so that rules out an issue with the invoices posting based on the Transaction posting date.

But, that doesn't explain why invoices imported at the same time on Monday morning would post to different dates.

Thursday, November 8, 2018

I created my first PowerApp! It's a Conference Badge Scanner!

By Steve Endow

I love attending conferences.  They are a rare opportunity to meet the faces behind the emails and voices and spend time with friends that you only get to see once or twice a year.

At the GPUG Summit 2018 conference in Phoenix last month, I had a great time, but there was one thing that I noticed was really frustrating me.

After a session, attendees would have questions or need assistance with a Dynamics GP issue, and I would promise to email them some information.  Sometimes I would hand them one of my business cards, sometimes they would hand me theirs.  If we had a pen handy, one of us would scribble something on the card to remind us what we talked about and prompt a follow up email.

Unfortunately, I have plenty of evidence that this process just doesn't work well.  Everyone is running around to different sessions at the conference, there are tons of distractions, we run out of business cards, the cards get stuffed into a backpack, and by the time we get home from the conference, exhausted, the last thing we want to do is sift through a pile of business cards with cryptic notes on them and try to remember what we were supposed to do, who we were supposed to contact, and which conversation went with which business card.

At the Summit conference, there were a lot of sessions on PowerApps and Flow, so I started thinking...

And I tweeted:


I had never used PowerApps and had never created a PowerApp before, so I didn't know if this was possible, and if it was possible, how difficult it would be.

Turns out, it is definitely possible.  And although it was a little more challenging than I thought, now that I've done it, relative to other things I've done, it's not that difficult.  And considering what the app can do, it's amazing how easy it is to build.

And here it is--my very own PowerApps Conference Badge Scanner!


So what do we have here?

In the upper left is the live view of the cell phone camera. When the app is open on my phone, I can take a photo of a conference attendee's badge.



In the upper right is a photo of the badge.  Because conference badges can have lots of other noisy text besides the attendee name, I found that it is best if I take a photo of just the text that I want scanned.

Monday, October 15, 2018

Dynamics GP 2018 R2 displays full screen by default?

By Steve Endow

I recently installed Dynamics GP 2018 R2 on my laptop.  The install went smoothly and GP works fine.

But I noticed something odd, and annoying.  Every time I launched GP 2018 R2, the application window would open maximized.  Before the login prompt appeared, the GP application window would fill the screen.  I thought maybe it launched in full screen on the first launch, but then once I resized the window and restarted, it would remember the window size settings.

But that didn't work.  I resized the GP application window, closed it, then relaunched it.  Nope--it would relaunch in full screen again.  I tried Run As Administrator to see if that made a difference.  Nope.  It would always launch maximized.

This was noticeable because I have never seen this behavior in GP before.

So where would this behavior be controlled?  Very likely in the Dex.ini file, of course.

Sure enough, as soon as I scanned the Dex.ini file, I saw the setting.


The likely culprit:  WindowMax=TRUE.

I've never noticed this setting, and didn't know it existed until today.  So I changed the setting to FALSE and restarted GP.

Presto, Dynamics GP launched with a non-maximized window, just like normal.  Problem solved.

Just to make sure I wasn't imagining things, I checked my GP 2018 install, and I see that the Dex.ini has the WindowMax setting, but the default value is FALSE.  I checked a GP 2016 install, and the default value is also FALSE.

I'm not sure if the default value for GP 2018 R2 is WindowMax=TRUE, or if that flag is only set to true when you choose to install the Web Client components.

But if you see this behavior, and you want to change it, now you know!


You can also find him on TwitterYouTube, and Google+




Thursday, October 4, 2018

I'm a kid in the Microsoft Candy Store

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

(Thanks to David Gersten for the idea for this blog post!)

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 really, really, long

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.

How many digits can a Business Central Amount field actually support?

 by Steve Endow (If anyone has a technical explanation for the discrepancy between the Docs and the BC behavior, let me know!) On Sunday nig...