Configure a Database Query Transporter Data Feed with a Multi-Select Values List for Archer

[KB-Sync1]

Article Number

000030602

Applies To

Product(s): Archer
Version(s): All Versions
Primary Deployment: On Premises

---

Description

This article explains how to configure a Database Query Transporter Data Feed with a Multi-Select Values List.

Resolution

There are two ways to import multiple values into a values list field with a Database query data feed

Method 1: Create separate rows in the source database for each values list value. 

e.g., If you have a values list field called Color and you want to import the three values Red, Blue and Green for one record, your database would need three rows. Each would have the same value for the application's key field, with different values for the Color field:

Key Field    Color
12345        Red
12345        Blue
12345        Green

1. In the Data Feed configuration, on the Data Map tab, configure the values list with the setting Append data to list-based fields.
2. When the first row is processed, the record 12345 will be added, with the value Red in the values list field.
3. When the second row is processed, record 12345 will be updated and Blue will be appended to the existing value in the field.
4. Processing of the third row will again update the record and add the last value to the end of the list.

Method 2:  Add a customized XML transform to the Data Feed. 

As an alternative to the above, you can add a custom transform to the Data Feed on the Navigation tab to modify the structure of the source XML data.  An example is shown below.  In this transform, you would replace the string FIELDNAME with the actual name of your values list field.
 

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl" xmlns="http://www.archer-tech.com/">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="NewDataSet">
<ArcherRecords>
  <xsl:apply-templates select="Table"/>
</ArcherRecords>
</xsl:template>
<xsl:template match="*">
<xsl:element name="{local-name()}">
  <xsl:copy-of select="@*"/>
  <xsl:apply-templates/>
</xsl:element>
</xsl:template>
<xsl:template match="NewDataSet/Table/FIELDNAME">
  <xsl:call-template name="outputValues">
      <xsl:with-param name="ename" select="local-name()"/>
    <xsl:with-param name="list" select="."/>
    <xsl:with-param name="del">;</xsl:with-param>
  </xsl:call-template>
</xsl:template>
<xsl:template name="outputValues">
<xsl:param name="list"/>
<xsl:param name="del"/>
<xsl:param name="ename"/>
<!-- GET EVERYTHING IN FRONT OF THE FIRST DELIMETER -->
<xsl:variable name="first" select="substring-before($list,$del)"></xsl:variable>
<!-- STORE ANYTHING LEFT IN ANOTHER VARIABLE -->
<xsl:variable name="remaining" select="substring-after($list,$del)"></xsl:variable>
<xsl:choose>
  <xsl:when test="$first != ''">
      <xsl:element name="{$ename}">
      <item>
            <xsl:value-of select="$first"/>
      </item>
      </xsl:element>
    <!-- CHECK TO SEE IF ANYTHING IS LEFT -->
    <xsl:if test="$remaining">
    <!-- CALL THE TEMPLATE AGAIN USING THE NEW VARIABLEFOR THE PARAMETER -->
    <xsl:call-template name="outputValues">
      <xsl:with-param name="list" select="$remaining"></xsl:with-param>
      <xsl:with-param name="del">;</xsl:with-param>
        <xsl:with-param name="ename" select="$ename"></xsl:with-param>
    </xsl:call-template>
    </xsl:if>
  </xsl:when>
  <xsl:otherwise>
      <xsl:element name="{$ename}">
      <item>
            <xsl:value-of select="$list"/>
      </item>
      </xsl:element>
  </xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>