Repeating section as html table in mail

M
Badge +9

A simple way to convert Nintex Forms Repeating section XML data to a HTML table is using a XLS transformation. To do the transformation only one action is needed: Query XML.

Here is a simple example (Forms 2013, Workflow 2013). The form looks like:

Repeating section contains three single line controls named 'last_name', 'first_name', 'company' and is connected to workflow variable 'contacts_xml' of type 'Multiple lines of text'.

XML-content of contacts_xml:

<?xml version="1.0" encoding="utf-8"?><RepeaterData><Version /><Items><Item><last_name type="System.String">Lauer</last_name><first_name type="System.String">Manfred</first_name><company type="System.String">KOB</company></Item><Item><last_name type="System.String">Billing</last_name><first_name type="System.String">Emily</first_name><company type="System.String">Nintex</company></Item></Items></RepeaterData>

xml to html transformation is made with a Query XML action. XML source is workflow variable contacts_xml, result will be stored in workflow variable contacts_html

Here is complete XSLT as text:

<?xml version="1.0"?>

    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

        <!-- transform repeating section from xml to html -->

        <xsl:template match="/">

            <xsl:apply-templates />

        </xsl:template>

        <xsl:template match="Items">

            <table border="1" width="100%" style="border-collapse:collapse;background-color:#eee;border:1px solid;color:black;font-size:100%;font-family:arial,helvetica,sans-serif;">

                <thead>

                    <tr>

                        <td>first name</td>

                        <td>name</td>

                        <td>company</td>

                    </tr>

                </thead>

                <tbody>

                    <xsl:apply-templates />

                </tbody>

            </table>

        </xsl:template>

        <xsl:template match="Item">

            <tr>

                <!-- field output order -->

                <xsl:apply-templates select="first_name" />

                <xsl:apply-templates select="last_name" />

                <xsl:apply-templates select="company" />

            </tr>

        </xsl:template>

        <xsl:template match="first_name">

            <td>

                <xsl:value-of select="." disable-output-escaping="yes"/> </td>

        </xsl:template>

        <xsl:template match="last_name">

            <td>

                <xsl:value-of select="." disable-output-escaping="yes"/> </td>

        </xsl:template>

        <xsl:template match="company">

            <td>

                <xsl:value-of select="." disable-output-escaping="yes"/> </td>

        </xsl:template>

    </xsl:stylesheet>

To avoid problems with german Umlauten etc. output escaping has to be disabled because content of repeating section in XML is already encoded.

Resulting html in workflow variable contacts_html:

<table border="1" width="100%" style="border-collapse:collapse;background-color:#eee;border:1px solid;color:black;font-size:100%;font-family:arial,helvetica,sans-serif;"><thead><tr><td>first name</td><td>name</td><td>company</td></tr></thead><tbody><tr><td>Manfred</td><td>Lauer</td><td>KOB</td></tr><tr><td>Emily</td><td>Billing</td><td>Nintex</td></tr></tbody></table>

In Send notification action choose Rich Text and insert by reference workflow variable contacts_html:

Resulting mail looks like:

The look of the table can be changed by using inline css styles.

Example workflow is attached. Comments are welcome.

Manfred Lauer

see also XSL Transformation of repeating section to html

30 replies

alexjolynintex
Badge +6

Nice one!

That's reallly cool to share Manfred!

M
Badge +9

Hi Chetan

connection is made in settings of Repeating Section control:

Kind regards

Manfred

M
Badge +9

You have to define a workflow variable of type Multiple lines of text, then variable is shown in connect dialog:

M
Badge +9

Hi Chet

nice to hear that it works this way.

Kind regards

Manfred

C
Badge +4

Just found this......had it into my workflow in 5 minutes....works perfectly!!

Thanks so much!

M
Badge +9

Update for stylesheet can be found here: XSL Transformation of repeating section to html

Transformation to other output formats is possible too, not limited to html.

M
Badge +9

Hi Divya,

I have no access to O365. What error messages did you get.

Kind regards

Manfred

M
Badge +9

Hi Divya,

can You give a screenshot of Query XML Configuration? Can You choose XSLT or is there only XPATH Query possible?

Kind regards

Manfred

M
Badge +9

Hi Divya,

XSLT processing is needed for my solution!

Kind regards

Manfred

G
Badge +8

Thanks for sharing Manfred. I did not use it for a repeating section this time but the formatting of your table saved me here. Finally no huge blank spaces in the table in my inbox happy.png

M
Badge +9

It's in Nintex Forms Designer.

Chris_Ben
Userlevel 6
Badge +12

How have I not found this post before Manfred?  I formatted HTML tables by building strings with a few loops.  I can chop out about 20 workflow actions now and replace it with this!  Thanks!!

B
Badge +11

HELP

I am trying to follow your instruction but require some advise.
The repeated section on my form is connected to a multi-line text-field called Inventory.

The result of the Inventory field as is following:

<?xml version="1.0" encoding="utf-8"?>
<RepeaterData>
<Version />
<Items>
<Item>
<Returned type="System.Double">100</Returned>
<ReturnedRate type="System.Decimal">91.66</ReturnedRate>
<TotalRemaining type="System.Decimal">366.64</TotalRemaining>
</Item>
</Items>
</RepeaterData>

Now, in my workflow I have created the Query XML with the following information:

I am missing the steps how to connect the repeated section to a Workflow Variable!!!

In the form design does not provide on option for that ...

The XSLT format is as following:

<?xml version="1.0"?>

    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

        <!-- transform repeating section from xml to html -->

        <xsl:template match="/">

            <xsl:apply-templates />

        </xsl:template>
 

        <xsl:template match="Items">

            <table border="1" width="100%" style="border-collapse:collapse;background-color:#eee;border:1px solid;color:black;font-size:100%;font-family:arial,helvetica,sans-serif;">

                <thead>

                    <tr>

                        <td>Returned</td>

                        <td>ReturnedRate</td>

                        <td>TotalRemaining</td>

                    </tr>

                </thead>

                <tbody>

                    <xsl:apply-templates />

                </tbody>

            </table>

        </xsl:template>

 

        <xsl:template match="Item">

            <tr>

                <!-- field output order -->

                <xsl:apply-templates select="Returned" />

                <xsl:apply-templates select="ReturnedRate" />

                <xsl:apply-templates select="TotalRemaining" />

            </tr>

        </xsl:template>

 

        <xsl:template match="Returned">

            <td>

                <xsl:value-of select="." disable-output-escaping="yes"/> </td>

        </xsl:template>

 

        <xsl:template match="ReturnedRate">

            <td>

                <xsl:value-of select="." disable-output-escaping="yes"/> </td>

        </xsl:template>

 

        <xsl:template match="TotalRemaining">

            <td>

                <xsl:value-of select="." disable-output-escaping="yes"/> </td>

        </xsl:template>

 

    </xsl:stylesheet>

When I run the RUN TEST:

M
Badge +9

To test the action You have to replace '{ItemProperty:Inventory}' by XML of Repeating Section as value of ItemProperty is only available when workflow is running.

Kind regards

Manfred

B
Badge +11

works great happy.png

Wonder if you can help me with another challenge.

Can we add an empty <tr> <td> column between other ones? Example:

I tried by adding the following:

<td>-</td>

<xsl:apply-templates select="-" />

<xsl:template match="-">
            <td><xsl:value-of select="." disable-output-escaping="yes"/> </td>
        </xsl:template> 

but then it shows an error when running the wf.
Just like to add a split / space between the data to make it more distinct.

E
Userlevel 5
Badge +14

in 'Run now' test item properties can not be evaluated to a value, since the action doesn't run on any list item.

so in 'Run now' you have to provide real value - repater XML in your case

B
Badge +11

Hello

I have added a DropDown Box as a LookUp value to fetch the data from a column in a custom list.

Now, when the data is saved ... the dropdown value is saved as the ID number instead of the actual text.

when I look at the XML data, the <SelectCurrency> shows number 2 as the ID number where in fact the ID 2 column Currency is "Euro"

where do I change this option to store the value from the Currency and not the ID ??

Chris_Ben
Userlevel 6
Badge +12

Hi Bim,

Use the parseLookup function and that will return either the list id or the value (depending on how you use it).  To get the value, change your List column name entry to: parseLookup(Currency).

Have a squiz at the online help (or search here) for more examples of how parseLookup works.

Cheers,

Chris

B
Badge +11

Hi @Chris Ben

there is no such function available apart from the "inline functions".  What I also done is, created a calculated field (named: varCurrency) where I used the parseLookup(SelectCurrency) which shows whatever it is been selected from the drop down box.

Now, since the varCurrency is part as the Repeating Section > it will be saved to a field called Inventory - which in return safes the data as the XML file. With the script provided by Manfred ; I was able to fetch those data and make it present in a table view.

All fine happy.png apart from the <SelectCurrency> showing the ID number instead
and the <varCurrency> not showing at all. Although the data is in there.

Here is the actual XML section :

<varCurrency type="System.String">GBP - Sterling</varCurrency>
<SelectCurrency type="System.String">1</SelectCurrency>
This is what I use in the XLST:
 + $(this).find("varCurrency").text() + which remains blanksad.png
 + $(this).find("SelectCurrency").text() + then shows the ID instead.
 
N
Badge +3

‌ Nice post! Works like a charm.

Thanks for the post

B
Badge +9

Hi, Manfred Lauer

How would you send separate email based on company with repeating section data in HTML table with data only relevant to that company?

N
Badge +2

Hello Manfred Lauer. Using the above example, how do I filter the results that are returned in an email if I wanted to show only the first names that contain "Manfred"? 

M
Badge +9

see . There You will find an example how to filter items.

C
Badge +2

If anyone is looking to turn the time in a date field from 24hr to 12hr format, this is the code I'm using.

 

<xsl:template match="MyDateTime">
<td>
<xsl:value-of select="substring-before(., ' ')"/>
<xsl:text> - </xsl:text>
<xsl:variable name="time24ms" select="substring-after(., ' ')"/>
<xsl:variable name="time24" select="substring($time24ms, 1, string-length($time24ms) - 3)"/>
<xsl:variable name="h" select="substring-before($time24, ':')"/>
<xsl:value-of select="($h + 11) mod 12 + 1"/>
<xsl:text>:</xsl:text>
<xsl:value-of select="substring-after($time24,':')"/>
<xsl:value-of select="substring(' AM PM', 1 + 3*($h > 11), 3)"/>
</td>
</xsl:template>

kelliganp
Userlevel 4
Badge +10

Hi @mlauer,

Wow! This worked great for me! Any advice on how to use the "inline CSS styles"? My table has currency in two of the columns. I have no experience with CSS and especially how to inject it into the code you provided. Thanks!!

 

2606iD09A887EE2AE36DC.png

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings