For one-to-one mapping of Look-up type fields (including user-type fields), Excel spreadsheets-based mapping followed by XSLT script works perfectly.

In an Excel spreadsheet, add field values in correct pair from the Source and Target systems. For example, look at the screenshot below:

Once the required set of paired data is added in the Excel spreadsheet, edit the XSLT script given below as per the instructions that follow.

<TargetFieldName>
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xslUtils="http://com.opshub.utils.xslutils.excel.ExcelSheetValueLookup">
    <xsl:variable name="xPathVariable" select="SourceXML/updatedFields/Property/SourceFieldName"/>
    <xsl:choose>
       <xsl:when test="$xPathVariable!=''">
          <xsl:value-of select="xslUtils:lookup('C:\\Program Files\\OpsHub_External_Data\\SprintProjectData.xls','ExcelSheetName','A','B',xPathVariable)"/>
       </xsl:when>
    </xsl:choose>
</TargetFieldName>
Note: In the above XSLT script, value A and B represent the columns from the Excel spreadsheet, where column A holds the data for source system and column B holds the data for target system.

In the XSLT script, replace the SourceFieldName with actual source field name and replace the TargetFieldName with actual target field name. You can take the field names of source and target from the default XSL that is generated by OpsHub Integration Manager by following below steps:

  • Expand field mapping to view advance mapping options at the field level.
  • Click SourceFieldName against the mapped field.
  • Select Forward direction if you want to edit XSL script from System 1 to System 2 and select Backward direction if you want to edit XSL script from System 2 to System 1.
  • XSL script will open in a text editor as shown in the screenshot below.
  • The script shown above is for Status field, but you should open the XSL for the field for which you are doing Excel spreadsheets-based mapping. Here, Status is the field name in the source system and State is the field name in the target system.
<Status>
<xsl:value-of xmlns:xsl="http://www.w3.org/1999/XSL/Transform" select="SourceXML/updatedFields/Property/State"/>
</Status>
  • Now, replace the path C:\\Program Files\\OpsHub_External_Data\\SprintProjectData.xls with the actual path.
  • The XSLT script then needs to be pasted in the Edit Transformation XSL pop-up shown below. Make sure you have selected the correct direction (backward/forward) before pasting the XSL script.
one-to-one mapping