An alternate solution for Value Mapping Bulk Upload in CPI
Purpose For Value Mapping:
It is combination of keys and values. You can use a key to lookup the corresponding value and vice versa. For example country code is key (AU) and country name is value (Australia).
Few Points to Remember:
Value mapping does not allow duplicate entries.
- Once you deploy a value mapping it can be reused in different mapping and different packages.
- It is case sensitive, so use the correct agency and identifier name in message mapping.
- If multiple keys have a single value, we can concatenate the keys in value mapping by using any one characters (,|_-). Example: country code: AU, State code: NSW=Australia. Then we can add one group: Group1:AU_NSW: Australia.
- If different keys need to be assigned the same value, you can add a counter at the end of values. Example: Country codes (AU,AUS) and country name (Australia). So we need to add two group: Group1:AU=Australia_1 Group2:AUS=Australia_2.
To achieve Bulk Upload of Value mapping successfully, we need to do the following:
- Generate unique group id for each records.
- Merge multiple source values to one target value.
- Remove duplicates
- Generate XML structure for value mapping
Step 1: Create value mapping in Package and set the source/target agency and identifier.
Step 2: Add one value mapping entry by click Add button:
Step 3: Deploy the value mapping.
Step 4: Download the value mapping from package to local directory.
Step 5: Extract the download file in local directory
Step 6: Open the value mapping XML(value_mapping.xml) document. Please note that file name and XML tag’s name should not renamed.
Step 7: It contains one group with unique group id. If you want to add one more group you need one more unique group id. But our case 2000 records are there, so we need 2000 unique group id.
Step 8: Create excel sheet1 sheet and copy the below values from value_mapping.xml:
- Excel cell number A1 to A2000: Paste xml tags <group id=”xyz and remove last four digit.
- Excel cell number B1 to B2000: Add counter from 1 to 2000.
- Excel cell number C1 to C2000: Add value “>
- Excel cell number D1: Concatenate rows “=A1&B1&C1”, so you will get group id xml tag.
Refer the below screenshot:
Step 9: Create excel sheet2 and maintain the below values:
- Excel cell number A1 to A2000 : Add key
- Excel cell number B1 to B2000 : Add Values
- Excel cell number C1 to C2000 : Paste values from sheet1 row D1 to D2000 . It contains the group id xml tag.
- Excel cell number D1 to D2000 : Copy from value_mapping.xml <entry><agency>SourceAgencyName</agency><schema>SourceSchemaName</schema><value>. Refer below screenshot:
- Excel cell number E1 to E2000 :Copy from value_mapping.xml </value><entry><agency>TargetAgencyName</agency><schema>TargetSchemaName</schema><value>.Refer below screenshot:
- Excel cell number F1 to F2000 :Copy from value_mapping.xml </value></group>.Refer below screenshot:
- Excel cell number G1 to G2000 : Add formula for concatenate in excel “C1&D1&A1&E1&B1&F1”. So you will get complete XML tag of group id in column G.
Refer the below screenshot for more details:
Step 10: Copy the value from column G in the excel sheet and paste to the value_mapping.xml between <vm> XML tags like below and save the file:
Step 11: ZIP below files:
Step 12: Select view meta data in valuemapping and click edit button in CPI:
Step 13: Select the new zip file and click OK:
Step 14: When you open the Value mapping , you will find the entire list of 2000 entries created in our excel available to use in the Value mapping table.
Step 15: Now you can use the value mapping in iFlows.
- In this way, we can upload Bulk Value Mapping entries in CPI without the need of any additional groovy script or CSV file upload.
- Following the above method allows us to upload Bulk Value Mapping entries in CPI without coding any additional groovy script or uploading a CSV file.