Fix Teamleader Incorrect Custom Field Values

Problem

An error with code itgendid044 or itgendid043 can be raised during retrieval of teh value of a Teamleader custom field, such as:

The value ‘24 KwH’ is not a valid decimal.

Similar errors can occur on dates that fall outside the regular UNIX epoch time range such as January 1 of the year 20.000.

This is caused by Teamleader allowing very wide ranges of values which are normally considered outside the allowed range for a number or date.

Solution

To enable use of Teamleader with Invantive SQL there are two options:

  1. Correct custom field values in Teamleader.

  2. Disable custom field domain value validation.

Correct Custom Field Values in Teamleader

To correct the custom field values in Teamleader you can perform the following steps:

  • Start Invantive Query Tool.
  • Log on to Teamleader using user name and password (OAuth) or using an API key.
  • Run the SQL script attached.
  • The script generates an Excel sheet with custom field values violating the data type constraints.
  • Increase the limit on number of rows to handle all rows by chaing MAX_ROW_COUNT_CLAUSE.
  • Manually look up each record and correct the value to fall within the range of the custom field.
  • Empty the disk cache, typically located at %USERPROFILE%\invantive\cache\http\USER\shared\app_teamleader_eu.
  • Re-run the script regularly to make sure no new invalid values occur or await an Invantive SQL triggered validation.

Disable Custom Field Domain Value Validation

The interpretation of custom field values corresponding to the specified data type can be disabled. This is only recommended for experienced developers. Upon log on, set the attribute ‘force-custom-field-to-string’ to True. This is also possible from the log on screen:

image

Note that this completely disables all validation on custom field values. You will need to manually cast all custom field values to the intended values.