I’m using SmartConnect 2018/21 to create a Dynamics GP Bank Reconcilliation Deposit without Receipts integration.
The map is pretty simple, but when I run it, it fails with the error:
Cannot insert the value NULL into column ‘DepAmt’,table ‘TWO.dbo.CM10100’; column does not allow nulls. INSERT fails. The statement has been terminated.
If you’d like to watch a video explanation of this, see below or click here.
Reviewing the mapping, I’ve included the required fields and have provided the Deposit Amount, which is not set to NULL in either the data or the calculation. As I’m providing the actual Deposit Amount, the insert NULL error is puzzling. Why is the integration failing?
How to Fix the “Cannot Insert the value NULL” Error
The “Required” fields in Dynamics GP (or really any ERP) are tricky to navigate.
Because most of the transaction types in Dynamics GP can handle multiple types of documents in the same API call, some fields will default (and thus not be strictly required to be mapped). Some are required to be populated for one document type and not another, and would also not be displayed as required.
Because we are providing the Deposit Amount and passing a non-NULL value, that tells us that the eConnect proc is calculating it for us instead and using the calculated value.
The only reason for that would be is that eConnect is processing a Deposit with Receipts in this integration.
Looking at the eConnect SDK for Deposit Header node:
Because the DEPTYPE was not mapped, eConnect used the default, which is “Deposit with Receipt”.
For a Deposit with Receipt, eConnect calculates the Deposit Amount from the receipts integrated on the ‘Create Deposit Line’ node. Because this was meant to be a Deposit without Receipts, that wasn’t used, and there are no “receipts” to total.
That said, the eConnect proc for the ‘create deposit header’ node – taBRBankDepositsHeader – is slightly buggy as the variable used should have been initialized to zero so that if this were to happen, a 0 would be inserted instead of getting this NULL error.
Now that the Deposit Type is established as being incorrect for our purposes, the solution is easy: explicitly set the Deposit Type to “Deposit Without Receipts”.
Here in the mapping, we can see that it is now set to that List Option value, and the Deposit Option is also set to Enter/Edit. That was the default value, but it is good to set that value so that we can see the intent behind the integration.
The integration ran without errors and reviewing the document in GP showed the expected values provided in the integration.
If you have any questions regarding how to fix the “Cannot insert the value NULL into Column DepAmt” error, please reach out to support@eonesolutions.com.
