Dynamics GP – Payables Payments not updating the Bank Management module

Recently I ran across an interesting issue affecting the bank management module following an upgrade from Dynamics GP 2010 to Dynamics GP 2018 R2. (I think the bank management module is more widely used here in the UK)

A client reported that when posting a EFT payables payment run the payments weren’t hitting the bank management reconciliation window. i.e. by EFT I mean they select “EFT” on the payables batch as per below:

I tested posting a batch of payment using the option “Cheque” on the batch header and this updated the bank management reconciliation fine.

After some troubleshooting I found this was because “Use Cheque Numbers” was selected on the chequebook as per below. (this option is available via “Cards > Financial > Chequebook > EFT Bank > Payables Options”)

When I selected “Use EFT Numbers” the bank management module is updated.

I’m not sure if this is a change in functionality as this client used to work fine prior to the upgrade.

I have tested Dynamics GP 2016 however I get the same behaviour. It seems “Use EFT Numbers” has to be selected for the bank management module to be updated.

I hope this helps anyone else who stumbles across this issue.

Thanks for reading.

Brief overview and comparison of how summary values are stored and calculated in Dynamics GP, Dynamics NAV and Dynamics 365 Business Central

Introduction

There was an interesting conversation on twitter over the weekend with regards summary values and Dynamics 365 Business Central. Much like myself the person asking the question came from a Dynamics GP background and was wondering where in the database Dynamics 365 Business Central stored summary values i.e. GL period balances.

Now this is one area of the system that I really like about Dynamics NAV and Dynamics 365 Business Central so I thought I’d write this up.

The Dynamics GP way

Dynamics GP stores summary values in a variety of different summary tables. For example the GL open years transactional data is stored in one table (GL20000) and its summarised in a summary balance table (GL10110).

This means you can quickly access the summary values in an enquiry window. For example below is a GL enquiry window showing the GL period balances for a cash account.

What’s great is as this window is accessing a summary table rather than summing and sub totalling the main transaction table the performance is great, so you have access to summary data very quickly. i.e. you could 10 million rows of transactional data for this GL code but its all nicely summarised into a handful of rows in the summary table.

As I mentioned this provides fantastic performance however anyone who has worked with Dynamics GP for any length of time will realise the one issue that comes with storing data in this way. Unfortunately if there is an issue when posting a transaction “sometimes” the summary values aren’t updated and therefore they become “out of sync” with the actual transaction data i.e. if you were to manually sum the transaction data it wouldn’t match the summary data. This can cause problems and prompt awkward conversations with clients as they (understandably) ask “why” this is the case, and (even worse) how they can prevent this in the future. (I hate it when this question crops up)

*** A wider discussion here would be why Dynamics GP doesn’t update all the SQL tables (transaction table and summary table) using one SQL transaction which would negate this issue however that is beyond of the scope of this article.

To compensate for this Dynamics GP has “reconcile” features for nearly all ledgers so you can re-align the summary balances with the actual transactions. For example below is the “reconcile” utility for the General Ledger. Running this will recalculate all the summary balances based on the transactional data and update the summary table I mentioned earlier.

The Dynamics NAV \ Dynamics 365 Business Central way

The first thing to say is that Dynamics NAV and Dynamics 365 Business Central doesn’t store summary values in specific SQL tables like Dynamics GP. Instead it uses something called “Flow fields” for the summary totals which are implemented using SQL Indexed views. (therefore technically the summary values are materialised in the database just not in actual SQL tables). In the Dynamics NAV and Dynamics 365 Business Central world they are referred to as SIFT Indexes.

So for example, when you view the GL balances in Dynamics NAV as per the screen shot below the system is running queries using the SQL indexed views to present the summary values and therefore performance is still extremely good. (technically its the SQL optimiser that will choose to use this index when asked to return the summary values for this page because its much more efficient)

Digging a little deeper if you go into the Dynamics NAV development environment you can see how the SQL indexed views (SIFT Indexes) are implemented.

For example if I select the GL entry table and click “Design”

And then select “View > Keys” to look at the indexes

You can now see the keys (Indexes) on the left and some of the keys have an associated “SumIndexField” (i.e. a SIFT Index which is implemented via a SQL Indexed View). In the highlighted example an SQL indexed view is created summing the fields Amount, Debit Amount, Credit Amount, Additional-Currency Amount, Add.-Currency Debit Amount, Add.-Currency Credit Amount and grouping by GL Account No and Posting Date.

Therefore if I open SQL Management Studio and browse to my Dynamics NAV database I can see all the SQL indexed views on the GL Entry table (there are four which matches the number of enabled keys with SumIndexFields columns in my development environment)

Finally if I take a look at the definition of the SQL indexed view named “CRONUS UK Ltd_$G_L Entry$VSIFT$1” we can see this is implementing the key that I highlighted earlier

CREATE VIEW [dbo].[CRONUS UK Ltd_$G_L Entry$VSIFT$1]
WITH schemabinding
AS
SELECT “17”.”g_l account no_”,
“17”.”posting date”,
Count_big(*) AS “$Cnt”,
Sum(“17″.”amount”) AS “SUM$Amount”,
Sum(“17″.”debit amount”) AS “SUM$Debit Amount”,
Sum(“17″.”credit amount”) AS “SUM$Credit Amount”,
Sum(“17″.”additional-currency amount”) AS
“SUM$Additional-Currency Amount”,
Sum(“17″.”add_-currency debit amount”) AS
“SUM$Add_-Currency Debit Amount”,
Sum(“17″.”add_-currency credit amount”) AS
“SUM$Add_-Currency Credit Amount”
FROM dbo.”cronus uk ltd_$g_l entry” “17”
GROUP BY “17”.”g_l account no_”,
“17”.”posting date”

I suppose you could argue the one draw back on this is that whenever a transaction is posted in Dynamics NAV and Dynamics 365 Business Central then SQL has extra CPU and disk work to do as it maintains the SQL indexed views associated with the transaction tables.

Conclusion

Although I love so many things about Dynamics GP I do think Dynamics NAV and Dynamics 365 Business Central has the edge here. By allowing the database to handle the maintenance of summary values through the implementation of SQL indexed views the data can never go out of sync with the summary values.

Thanks for reading.

Dynamics GP – Error Msg “A fatal error occurred while creating an SSL client credential. The internal error state is 10013” with the Web Client

A client reported the following error when trying to access the Dynamics GP web client:

Dynamics GP Web client – An unexpected error has occurred

The first thing I checked was the event viewer which highlighted the following error in the system log:

A fatal error occurred while creating an SSL client credential. The internal error state is 10013

I then checked through the system log and found this first starting happening 4 days ago which coincided with a server reboot.

As I knew nothing had changed with the GP environment I asked the clients IT department if any maintenance or other changes had occurred prior to the reboot and they informed me they had disabled both SSL v2 and SSL v3, TLS 1.0 and TLS 1.1.

I knew the issue had to be related to these changes however as this isn’t my area of expertise I called on the assistance of a colleague who after researching themselves advised me to add the following reg keys via an elevated command prompt

reg add HKLM\SOFTWARE\Microsoft\.NETFramework\v4.0.30319 /v SchUseStrongCrypto /t REG_DWORD /d 1 /reg:32

reg add HKLM\SOFTWARE\Microsoft\.NETFramework\v4.0.30319 /v SchUseStrongCrypto /t REG_DWORD /d 1 /reg:64

I then recycled the application pool in IIS and restarted all web client related service and this now works.

To confirm the server operating system was Windows Server 2012 R2 and they are using Dynamics GP 2016.

Hope this helps someone else facing a similar issue in future.

Thanks for reading.