Retrieve data from a conversion table
The concept of a conversion table in Link is that you can visually design a table with rows and columns in the Link GUI, which suits your needs.
The functional usage of conversion tables is when you have a transformation map that needs to e.g., substitute values or insert values if certain conditions are met. Then you can lookup values in the conversion table based on provided columns / values and get the desired value returned from the table, and that value can be put into the desired field in your transformation map.
Updating the values in a conversion table is a manual process which is done directly in the Link GUI to ease the maintenance task, and it is something that does not require a developer.
How to retrieve a conversion table value using a visual map
If you have your development environment prepared for Link development and have chosen to do a visual map, then you can simply use the provided functoids for retrieving values from Link conversion tables.
First drag and drop the “EdiPortal Distribution Lookup” functoid onto the map canvas.
Then drag and drop the “Conversion Table Value Extractor” functoid to the map canvas, where you place it to the right of the “Ediportal Distribution Lookup” functoid.
Drag a connection from “EdiPortal Distribution Lookup” to the “Conversion Table Value Extractor” functoid.
Double click the “Conversion Table Value Extractor” functoid.
The provided sampleconfiguration will be explained below.
Input[0] is the necessary value from the “EdiPortal Distribution Lookup”.
Input[1] has the value of ‘3’. This means that Link looks for a global (shared across partners) conversion table, instead of looking for a conversion table connected/configured on the sender-partner (‘1’) or on the receiver-partner (‘2’).
Input[2] is the precise name of the conversion table as it is named in Link.
Input[3] is the name of the column that holds the value that you would like to have returned. You use Input[4] and Input[5] to provide values in order to be able to locate the desired value in Input[3].
Input[4] is the name of the column that you use as the basis for the lookup.
Input [5] is the actual value that you search for in the column specified in Input[4] to be able to get the desired value located in the column specified in Input[3].
How to retrieve a conversion table value programmatically using XSL
If you have your development environment prepared for Link development and have decided on a programmatic approach, when doing mappings, then you can still use conversion tables. Please refer to the paragraph “How to retrieve a conversion table value using a visual map” if you are new to the concept of conversion tables.
The assumption here is that you have a code basis that consists of an .xsl file and an _extxml.xml file in your project.
Double click the .xsl file and specify these namespaces in the <xsl:stylesheet… part:
xmlns:ScriptNS0="http://schemas.microsoft.com/BizTalk/2003/ScriptNS0"
xmlns:ScriptNS1="http://schemas.microsoft.com/BizTalk/2003/ScriptNS1"
Now double click the _extxml.xml file.
It must contain these two extensionobjects.
<ExtensionObjects>
<ExtensionObject Namespace="http://schemas.microsoft.com/BizTalk/2003/ScriptNS0" AssemblyName="EdiPortal.Common.MappingFunctoids.EdiPortalMappingFunctoids, Version=1.0.0.0, Culture=neutral, PublicKeyToken=7685f304a6e7e148" ClassName="Ebex.EdiPortal.Common.MappingFunctoids.EdiPortalMappingFunctoids.DistributionLookup" />
<ExtensionObject Namespace="http://schemas.microsoft.com/BizTalk/2003/ScriptNS1" AssemblyName="EdiPortal.Common.MappingFunctoids.EdiPortalMappingFunctoids, Version=1.0.0.0, Culture=neutral, PublicKeyToken=7685f304a6e7e148" ClassName="Ebex.EdiPortal.Common.MappingFunctoids.EdiPortalMappingFunctoids.DistributionConversionTableExtractor" />
</ExtensionObjects>
Now the basic references are in place, and it is time to look at how the XSL code must look like.
The example below is a different example than the one shown in the paragraph with the visual map. The use case is to retrieve a company id from an existing conversion table.
First, we create a variable to hold the value corresponding to what the “EdiPortal Distribution Lookup” functoid would return.
<xsl:variable name="lookupDistribution" select="ScriptNS0:LookupDistribution()" />
Next, we create a variable the queries a specific conversion table. The configuration will be explained below.
<xsl:variable name="companyId" select="ScriptNS1:ExtractValue(string($lookupDistribution) , "3" , "StoreId Conversion" , "AX3 company id" , "SiteCoreId" , string(StoreServiceRequest/PreferredStore/SiteCoreStoreId/text()))" />
This parameter ScriptNS1:ExtractValue(string($lookupDistribution) will hold the necessary distribution value.
This parameter "3" has the value of ‘3’. This means that Link looks for a global conversion table (shared across partners), instead of looking for a conversion table connected/configured on the sender-partner (‘1’) or on the receiver-partner (‘2’).
This parameter "StoreId Conversion" is the precise name of the conversion table as it is named in Link.
This parameter "AX3 company id" is the name of the column that holds the value that you would like to have returned
This parameter "SiteCoreId" is the name of the column that you use as the basis for the lookup.
This parameter string(StoreServiceRequest/PreferredStore/SiteCoreStoreId/text()) is the actual value that you search for in the column specified in "SiteCoreId" to be able to get the desired value located in the column specified in "AX3 company id".