An issue may arise when a custom field is added to a Progress entity, and that same field is added to a summary screen in Sage CRM. Fields added to Progress tables need to also be added ("mirrored") to the parent entity.
Consider the following example:
An administrator adds a new field (case_tracking) to the CaseProgress secondary entity. Once this has been added, the field can be added to the Case Detail screen (CaseDetailBox).
Once this is done, an attempt to view, edit or add a Case record will result in a SQL error. On a cloud install, the following error message will be displayed:
SQL unexpected error has occurred
On checking the ewaresql.log, the following errors will be logged on creating a case:
Jun 18 2015 10:37:55.825 16152 8704 1 execsql,time,sql,errormsg 32 INSERT INTO Cases(case_primarycompanyid,case_primarypersonid,case_referenceid,case_rmanbr,case_foundver,case_slaid,case_slaseverity,case_description,case_productarea,case_source,case_customerref,case_targetver,case_salesordernbr,case_priority,case_assigneduserid,case_channelid,case_stage,case_status,case_problemtype,case_solutiontype,case_problemnote,case_solutionnote,case_closed,case_trackingnumber,case_Secterr,case_CreatedBy,case_CreatedDate,case_UpdatedBy,case_UpdatedDate,case_TimeStamp,case_SLACloseBy,case_SLAAmberCloseBy) VALUES (643,NULL,N'30-10207',NULL,NULL,NULL,NULL,N'Test',NULL,N'Telephone',NULL,N'CaseTargetVer2',NULL,N'Normal',30,6,N'NewCase',N'In Progress',NULL,NULL,N'test',N'test',null,N'12345641ABC',-2147483640,30,'20150618 10:37:55',30,'20150618 10:37:55','20150618 10:37:55',NULL,NULL)
Invalid column name 'case_trackingnumber'
This error occurs because the new custom field does not exist on the parent entity.
The issue can be resolved by adding the custom field to the parent entity under Administration | Customisation | <entity> | Fields. The exact same column name and properties will need to be selected for the mirrored field.
The root cause of this issue relates to how entity tracking works in the context of a workflow. Fields created on the Progress table for an entity (in this case CaseProgress) should also be created (“mirrored”) on the primary entity. This is becauase the Progress table is just used to record changes to an entity as it progresses through a workflow – it’s not the primary place where data on the entity is stored.
In example, the field was created on the Progress table, but it wasn't created on the main table for the entity. When the field was added to the Case Summary screen, CRM tried to load the data from the field on the main table so that it could display the data. This resulted in the error.