Importing business objects with m-to-n relation
 | To avoid errors while importing, the Part of Search key? field must be set to Yes on at least one field in M-to-N business objects. |
This example describes an import of an excel file with questionnaire and it’s (single select) questions.
Questions can be linked to multiple questionaires so there is an m-to-n link table (QuestionnaireQuestion) between the questionaire and the question.
This QuestionnaireQuestion m-to-n table has, next to links to questionaire and question also a field for the Sequence number.
The excel file is flat and looks like this:
The excel should be transformed to create a three level xml file. This is because three Business objects needs to be populated.
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/
1999/XSL/Transform">
<xsl:output method="xml" />
<xsl:template match="Questionnaire">
<Questionnaire>
<QNCode><xsl:value-of select="QNCode" /></QNCode>
<QNDescription><xsl:value-of select="QNDescription" />
</QNDescription>
<QNDomain><xsl:value-of select="QNDomain" /></QNDomain>
<QN_QS_LINK>
<Question>
<QSCode><xsl:value-of select="QSCode" /></QSCode>
<QSDescription><xsl:value-of select="QSDescription" />
</QSDescription>
<QSMandatory><xsl:value-of select="QSMandatory" />
</QSMandatory>
<QSType><xsl:value-of select="QSType" /></QSType>
</Question>
<SequenceNumber><xsl:value-of select="SequenceNumber" />
</SequenceNumber>
</QN_QS_LINK>
</Questionnaire>
</xsl:template>
</xsl:stylesheet>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/
1999/XSL/Transform">
<xsl:output method="xml" />
<xsl:template match="Questionnaire">
<Questionnaire>
<QNCode><xsl:value-of select="QNCode" /></QNCode>
<QNDescription><xsl:value-of select="QNDescription" />
</QNDescription>
<QNDomain><xsl:value-of select="QNDomain" /></QNDomain>
<QN_QS_LINK>
<Question>
<QSCode><xsl:value-of select="QSCode" /></QSCode>
<QSDescription><xsl:value-of select="QSDescription" />
</QSDescription>
<QSMandatory><xsl:value-of select="QSMandatory" />
</QSMandatory>
<QSType><xsl:value-of select="QSType" /></QSType>
</Question>
<SequenceNumber><xsl:value-of select="SequenceNumber" />
</SequenceNumber>
</QN_QS_LINK>
</Questionnaire>
</xsl:template>
</xsl:stylesheet>
The document wil have 4 workers:
• ExcelReaderWorker
• XSLWorker
• XMLToPOJOConvertor
• PlanonWriterWorker
And three business object definitions:
• Questionnaire
• Question
• QN_QS_LINK
QN_QS_LINK refers to the QuestionnaireQuestion m-to-n link table. The questions are (in this example) all added as singleSelectQuestions.
Next to adding fields to the Questionnaire and Question, fields should be added to the QuestionnaireQuestion business object. To this BO two reference fields should be added: one to the Question and one to the Questionnaire business objects and the SequenceNumber field.