Fix Teamleader Incorrect Custom Field Values

[forums]

Problem

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

The value ‘24 KwH’ is not a valid decimal.
Input string was not in a correct format.

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. Although not easily reproducible, it is also possible to enter text values in a number custom field.

Approaches

To enable retrieval of Teamleader custom field values with Invantive SQL there are two approaches:

1. Correct custom field values in Teamleader: make the values adhere to the data type specified.
2. Disable custom field domain value validation: map everything to text.

Correct Custom Field Values in Teamleader

On-premise Products

To correct the custom field values in Teamleader with on-premise products 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.

Invantive Cloud

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

• Open the Teamleader database using the Online SQL Editor of Invantive Cloud.
• Trigger the error using a query.
• Click on the menu on “System messages”.
• Search for itgendid043 and itgendid044.
• Check the value of the “Last Natural Key”.
• Correct the data in Teamleader through the user interface.
• Run the query again.
• As an alternative: run the SQL script attached.
• And follow the instructions as instructed with on-premise.

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:

Note that this completely disables all validation on custom field values and interpret all values as text. You will need to manually cast all custom field values to the intended data types.