While I was creating the Journal – Standard for this series, it was not all smooth sailing. I received an error message while testing the integration:
I’ve done a lot of work previously with Integration Manager and this type of error would not have been produced. The source file looks like the below:
One solution to this would be to always enter a zero in the blank cells, but this would mean extra work for the clients. So, my preferred solution needs to be one which does not involve extra effort for the client, which means it needs to be handled by SmartConnect.
This is possible by way of a calculated Additional Field of a type of Calculation. To create the column, from the SmartConnect Mapping window, click the Additional Columns button, click Columns and select Calculated.
The VB.NET code required to return 0 when the Credit field has not set is not all that complicated:
If (String.IsNullOrEmpty(_CREDIT)) Then Return 0
Else Return _CREDIT
Create a similar calculated Debit field:
The final step is to change the mapping to use the new calculated field instead of the ones direct from the data source:
With the calculated fields mapped, the integration successfully ran.