Introduction

In certain circumstances you may encounter an issue where the transaction list cannot be displayed and instead is replaced by a large red cross:

Note: This article describes a resolution to a specific case of an issue that can also manifest in other elements of Credit Hound and Credit Hound Manager. While the underlying reasons behind the issue are the same, the method for resolving the issue depends on where the red cross is displayed. 

DO NOT blindly follow this guide in cases where a red cross appears anywhere else in the software. Instead please contact Draycir support.

Cause

This is caused by the data to be displayed having a TAB character in it. The interface frameworks recognise this character as a new field character, which means that the data is misaligned compared to the expected data format. As a result Credit Hound is unable to present the data in a meaningful way, so errors with the crossed red lines as above and also with an error "Object reference not set to an instance of an object"

In the database, the TAB character is stored by its ASCII character code of 9 (see https://en.wikipedia.org/wiki/Control_character#In_ASCII ) We can use the SQL function CHAR(9) to search for a TAB character.

Solution

As the issue is caused by extraneous TAB characters in the Credit Hound database, we need to locate and remove them from the SQL database.

Important: This procedure involves running SQL queries directly against both the Credit Hound database and the Sage 200 database. You MUST back up the databases prior to running any of the queries below. Please also ensure that you understand what each query is doing before running. If you have any concerns regarding these queries, please consult a SQL administrator or Draycir support. Draycir accept no responsibility for any data loss or corruption aring from using or misusing the queries below.

On the Credit Hound COMP000x database we run the SELECT query below to establish whether the issue is, as we believe, rogue TAB characters (ASCII code 9) in one of the fields:

SELECT HostID
FROM Transaction_Table
WHERE
CHARINDEX(CHAR(9), TransactionTypeName) > 0 OR
CHARINDEX(CHAR(9), TransactionTypeShortName) > 0 OR
CHARINDEX(CHAR(9), ReferenceNo) > 0 OR
CHARINDEX(CHAR(9), SecondRef) > 0 OR
CHARINDEX(CHAR(9), URN) > 0 OR
CHARINDEX(CHAR(9), CurrencyHostReference) > 0 OR
CHARINDEX(CHAR(9), AccountRef) > 0 OR
CHARINDEX(CHAR(9), AdditionalRef) > 0

If this query returns no records, you do not have the issue as described in this KBA, in that instance please contact Draycir support for further assistance. 

If you see records being returned, we know that one of the eight fields searched contains an unwanted TAB. We can establish which field by removing fields from the query until we narrow down the field concerned, Let us assume that in this case it is SecondRef.

As SecondRef is not a field that the Credit Hound user can edit, the unwanted TAB characters must be being synchronised from the Sage 200 data.

This table shows which Sage 200 data fields map to which Credit Hound fields during a synchronisation:

So we need to check the field SLPostedCustomerTran.SecondReference in the Sage 200 database (Reminder: you MUST back up this database before running any of the queries below) for the TABs. 

We start with the following SELECT query:

SELECT [SLPostedCustomerTranID],[SecondReference]
FROM [<SAGE 200 DATABASE>].[dbo].[SLPostedCustomerTran]
where CHARINDEX(CHAR(9),Secondreference) > 0

This should return the same Transaction IDs that we saw in the Credit Hound database. 

We now run the following UPDATE query: 

IMPORTANT: At this point we are modifying the SAGE 200 data:

UPDATE SLPostedCustomerTran
set
SecondReference = REPLACE (SecondReference, CHAR(9),'')
where
CHARINDEX(CHAR(9),secondreference)>0

The above query looks for any TAB characters and replaces them with a null text string, effectively removing them from the data.

With this query run, we need to perform a full synchronisation from Sage 200 to Credit Hound and the erroneous transactions should be updated with the corrected one from the Sage database.

If, following the synchronisation, we still see the red cross we will need to run one last query on the Credit Hound COMP000x database to remove the TABs from the affected transactions manually:

UPDATE Transaction_table
set
SecondRef = REPLACE (SecondRef, CHAR(9),'')
where
CHARINDEX(CHAR(9),SecondRef)>0