2357167 - Legacy Picklist changes after Instance Sync

SAP Knowledge Base Article - Public

2357167 - Legacy Picklist changes after Instance Sync

Symptom

Every time we sync a picklist, then all the options in the picklist on target instance are deleted and we insert the options received from source. When an option is created then a new optionId is generated for that option. But if the existing optionIds(before sync) in target were being referred from some other data using that picklist (eg business rules), then those references will become invalid after the sync, because new optionIds will be generated for all the options in target.

Environment

Success Factors Employee Central - Picklist Management

Reproducing the Issue

  • Options which exist on both source and target will be updated, not deleted and inserted. This means that optionIds won’t change
  • Options which exist on target but not on source will not be deleted
  • Validate that for the picklist being synced, all existing options for that picklist should have non null external_code on both source and target. If not it will be displayed as an error in instance sync report and that picklist won’t be synced
  • Validate that for the picklist being synced, the external_code for options are unique within the picklist on both source and target. If not it will be displayed as an error in instance sync report and that picklist won’t be synced.

Cause

The root cause of the problem is that we are deleting and inserting the options instead of updating them. For updating the option we need to identify every option using a unique and immutable business key. But we don’t have such a business key for option. The only unique key is optionId, but since it is a surrogate key its value will be different for same option in source and target.

Resolution

The picklist table contains the column “external_code “ which can be used as the unique, immutable business key.  Then we can update the options based on external_code column.

However Platform team needs to make external_code unique and immutable field and write upgrade script to populate it in existing data. We are not sure when Platform team is going to make those changes, but we are going to change the instance sync code to update options using external_code as business key assuming that it will be made unique and immutable. But since we know it is not, so we will put our own validations in instance sync code to ensure the non nullability and uniqueness of external_code.

Example

Before Sync: Consider that we have the following picklist in source

PicklistId

OptionId

 External Code

OptionValue

Country

1

  usa

USA

Country

2

  ind

India

Country

3

  ger

Germany

And following picklist in target. Notice that there is a typo in the value “Indya”

PicklistId

OptionId

 External Code

OptionValue

Country

22

  usa

USA

Country

23

  ind

Indya

Country

24

  chn

China

 After Sync: Picklist it will look like this in target

PicklistId

OptionId

  External Code

OptionValue

Country

22

   usa

USA

Country

23

   ind

India

Country

24

   chn

China

Country

25

   ger

Germany

After this is implemented, then anyone executing instance sync for picklist will most likely see an error saying that external_code is null or non unique.  The consultant will then have to assign external_codes to picklist options in source and target to be able to perform instance sync.While doing so the consultants should ensure that they assign the same external_code to the same option in both source and target. Otherwise picklist sync won’t be accurate.

Keywords

KBA , LOD-SF-EC , Employee Central , Problem

Product

SAP SuccessFactors HCM Core 1605