Skip to main content

[DSP / CranSoft] Case Sensitivity Workaround

Caroline St.John
  • Updated

Issue

Sometimes a legacy system uses case sensitivity where two legacy values (e.g. zX01 and ZX01) need to map to different SAP® values. Even SAP configuration can itself be case sensitive (e.g. kgs and KGS are different values). Case-sensitive target values will cause errors during the target value refresh because the configuration tables are not collated to be case sensitive by default.  

Since the DSP® application is not case sensitive, and requires that it's databases use a case insensitive collation, these different case-sensitive values will not be recognized and only one value will be loaded in the legacy value / target value tables. If the source table / field collation is different for the application, DSP returns an error when refreshing the legacy values. Often this may be a Primary Key violation error due to multiple values differing only in their casing being interpreted in a case insensitive way as a single value.

Please Note:
There are service pages that automatically refresh legacy values and target values.  Some projects opt to turn these off, since the automated refreshes can drop values if they are run at the wrong time.  You can view them on the Admin > Resources > Service Pages page.


​
When service pages are not automatically refreshing legacy and target values, the refreshes are kicked off manually within Map.

To manually refresh individual tables in Map: 
1. Select Configuration > Value Mapping (Config) in the Navigation pane.
2. Click the Refresh icon for the desired table.

Workaround

You must manually refresh the values for the case-sensitive configuration tables or you will receive errors during the target value refresh.

NOTE: Changes must be made on a column-by-column basis and include all tables the column appears in. For example, you must change the language columns MAKT.SPRAS, T002.SPRAS and ttMAKT.SPRAS to be case sensitive. Otherwise the report tvMAKT_SPRAS_NotInT002_DetailSel will fail because one field (T002.SPRAS) is case sensitive and the other one is not (ttMAKT.SPRAS).

To refresh Value Mapping legacy values:

  1. Alter the case-sensitive configuration table's (e.g. cMap.ttValueMappingSourceValue) LegacyValue field collation to be case sensitive. Only individual columns should be made case sensitive, not entire tables or databases. 
  2. Refresh the Value Mapping values.
  3. Alter the configuration table's LegacyValue field collation back to the original case insensitivity.

This pulls in the values and allows users to assign them in Value Mapping.

The Value Mapping Update rules will now display some duplicate values. For example, both 'A' and 'a' being mapped to 'B' and 'C' will not work. To complete this workaround, you'll need to change the Value Mapping Update rules manually to handle the collation.

To refresh Value Mapping target values:

  1. Alter the collation of dbo.ttValueMappingCheckTableValuecolumns TargetValue, TargetValue1, TargetValue2, TargetValue3, TargetValue4, TargetValue5, and Description, since all fields are included in the list view webValueMappingCheckTableValueList.

NOTE: Description is used in the view in the same field as TargetValue, in position 13, so changing its collation is necessary to avoid collation issues when opening the mappings page.

  1. Alter the collation of dbo.ttValueMappingSourceValue.TargetValue. For example, you may need to use Latin1_General_CS_AS on all collation changes.
  2. Drop the index IX_ttValueMappingSourceValue_waveValeuMappingCheckTableIDValue​ and create it again.
  3. Alter the collation of columns in the ttValueMappingSourceValue table.

Related articles