If an ODBC connection fails due to error returning the following error, it may be that the firm may have deleted a client from their external system and will not update into HowNow.
Follow the steps below to edit the Custom SQL script within the data connection.
Check the Custom Script
From the Contacts tab, click on External Data Connection
On the first Connection in the list, click Next until the Custom screen is displayed
check if the following is listed in the code editor:
Update Clients
Set Phone = Coalesce(RTRIM(AreaPhone) || ' ', '') || Phone,
Fax = Coalesce(RTRIM(AreaFax) || ' ', '') || Fax,
Mobile = Coalesce(RTRIM(AreaMobile) || ' ', '') || Mobile
Where IsExternal = 'Y';
Commit;
If the above script is found,new fields will need to be created for Phone, Fax and Moble ie TempPhone, Temp Fax, TempMobile in the Display Field Editor. This will create a temporary update field which will be read to update the relevant HowNow field, ie Phone, Fax, Mobile. Follow the steps below to create the new custom fields,update the Main Scripts, and then the Custom Script and then test the data connection.
Create Custom Fields
From the HowNow Display Field Editor, click New and then create the following fields
TempPhone
TempFax
TempMobile
Update the Main Scripts
From the Contacts tab click on the External Data Connection and then check each main SQL script within each connection and replace the following fields with the new 'temp' fields
Phone = TempPhone
Fax = TempFax
Mobile = TempMobile
Update the Custom Script
Once all of the main scripts have been updated, you will now need to modify the custom script as Follows:
Update Clients
Set Phone = Coalesce(RTRIM(AreaPhone) || ' ', '') || TempPhone,
Fax = Coalesce(RTRIM(AreaFax) || ' ', '') || TempFax,
Mobile = Coalesce(RTRIM(AreaMobile) || ' ', '') || TempMobile
Where IsExternal = 'Y';
Test the output before running a data refresh