Question

Repeating Sections: Extract Data From O365 Forms in NAC, and From NAC Form Child Sections

  • 26 September 2023
  • 7 replies
  • 167 views

This How can I loop through rows in a repeating section in Nintex Workflow? (NWC) shows how to extract data in an NAC workflow from a repeating section in an NAC form. Can/ how do I extract data from repeating sections an existing O365 SharePoint form in an NAC workflow, and also, how do extract data from a child repeating section in an NAC form in an NAC workflow?


7 replies

Userlevel 6
Badge +22

Hi @rthomson,

 

The first thing you would need to do is connect the repeating section to a MultiLines of text column in SPO configured as plain text.

You then use the Query JSON action to Extract the information you need.
Child sections will have their own node in the JSON code so you just extract the whole node using the Query JSON action then loop through that extracted part to extract the information you need.

Thanks @SimonMuntz . I’ve connected a repeating section to a plain text MLT column, but the output doesn’t look good (see below). This is from a responsive form, and the reason I’m asking is because I’m trying to work out how to migrate an existing O365 workflow to NAC without having to re-build the form as well.

 

<?xml version="1.0" encoding="utf-8"?><RepeaterData><Version /><Items><Item><TCMTicket type="System.String"></TCMTicket><SAPRole type="System.String">R/ P Name #1</SAPRole><Instance type="System.String">BW (Business Warehouse)</Instance><Client type="System.String">Development</Client><RequestDescription type="System.String">RD#1</RequestDescription><RepeatingSubSection type="System.String">&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;&lt;RepeaterData&gt;&lt;Version /&gt;&lt;Items&gt;&lt;Item&gt;&lt;AuthorizationObject type=&quot;System.String&quot;&gt;AO-A&lt;/AuthorizationObject&gt;&lt;AuthorizationValue type=&quot;System.String&quot;&gt;AV-A&lt;/AuthorizationValue&gt;&lt;/Item&gt;&lt;Item&gt;&lt;AuthorizationObject type=&quot;System.String&quot;&gt;AO-B&lt;/AuthorizationObject&gt;&lt;AuthorizationValue type=&quot;System.String&quot;&gt;AV-B&lt;/AuthorizationValue&gt;&lt;/Item&gt;&lt;/Items&gt;&lt;/RepeaterData&gt;</RepeatingSubSection></Item><Item><TCMTicket type="System.String"></TCMTicket><SAPRole type="System.String">R/ P Name #2</SAPRole><Instance type="System.String">CRM (Customer Relationship Management)</Instance><Client type="System.String">Test</Client><RequestDescription type="System.String">RD#2</RequestDescription><RepeatingSubSection type="System.String">&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;&lt;RepeaterData&gt;&lt;Version /&gt;&lt;Items&gt;&lt;Item&gt;&lt;AuthorizationObject type=&quot;System.String&quot;&gt;AO-C&lt;/AuthorizationObject&gt;&lt;AuthorizationValue type=&quot;System.String&quot;&gt;AV-C&lt;/AuthorizationValue&gt;&lt;/Item&gt;&lt;Item&gt;&lt;AuthorizationObject type=&quot;System.String&quot;&gt;AO-D&lt;/AuthorizationObject&gt;&lt;AuthorizationValue type=&quot;System.String&quot;&gt;AV-D&lt;/AuthorizationValue&gt;&lt;/Item&gt;&lt;/Items&gt;&lt;/RepeaterData&gt;</RepeatingSubSection></Item></Items></RepeaterData>

Userlevel 6
Badge +22

Hi @rthomson,

Sorry, My mistake, When I said Query JSON I meant Query XML.
Anyway,  It appears that you are correct in that when XML is embedded within XML the XML is encoded so that it does not break the XML formatting.
Nintex for O365 Workflow has a function that allowed you to decode the XML once the node is extracted.
Unfortunately, Nintex Workflow Cloud does not have this functionality.

As per the post below, the only way around this is to not use child repeating sections.

You could try creating creating an extension that does the decoding or find an API to do it.
Encoding XML is not an exclusive Nintex thing, it is standard practice to maintain the integrity of the code.

Thanks @SimonMuntz. My objective is to migrate an existing O365 workflow to NAC and switch over from O365 to NAC so that the same SPO list is still used, so I don’t have the option to make form changes/ not use child repeating sections.

For the time being I’ll build a parallel Dev O365 workflow to take care of the repeating section data extraction, understanding that this will have an expiration date in 2026.Thanks again for your help.

Userlevel 6
Badge +22

Hi @rthomson,

The only other alternative would be to use regular expression actions to replace the escaped text with unescaped text in the XML.

Hi @SimonMuntz,

I have done a bunch of testing on this and have so far reached the following conclusions:

  1. While I am able to replace escaped text with unescaped text in O365, I can't get it to work in NAC: e.g. if I try to replace &lt; with < by using an Apply a Regular Expression action, when I re-open the action, the escaped characters have automatically been replaced with the unescaped character, so when I run the workflow the replace doesn't work - maybe I'm doing this wrong in NAC?
  2. In NAC I am able to query individual repeating sections in the NFFormData XML - e.g. using the XPath query //RepeatingSectionAddAuth, where RepeatingSectionAddAuth is the name of a repeating section - see example below. The problem though is that when I try to query the individual fields in the repeating section - e.g. using the XPath Query //Item/Instance, where Instance is a field in repeating section RepeatingSectionAddAuth - the query doesn’t work because the opening tags for the individual fields in the repeating section contain the text type="System.String", and I’m unable to remove this text from the XML in NAC. If I use this same query on XML which I’ve modified by hand to remove the text type=”System.String” though, the query works.

So: if I could find a way to remove the text type=”System.String” from the opening tags, I could query fields in parent repeating sections - do you have any ideas about how I could do that?

 

Example XML Returned by XPath Query //RepeatingSectionAddAuth

<?xml version="1.0" encoding="utf-8"?>

<RepeaterData>

<Version />

<Items>

<Item>

<Instance type="System.String">BW (Business Warehouse)</Instance>

<Client type="System.String">Development</Client>

<RepeatingSubSection type="System.String">&amp;lt;?xml version=&amp;quot;1.0&amp;quot; encoding=&amp;quot;utf-8&amp;quot;?&amp;gt;&amp;lt;RepeaterData&amp;gt;&amp;lt;Version /&amp;gt;&amp;lt;Items&amp;gt;&amp;lt;Item&amp;gt;&amp;lt;AuthorizationObject type=&amp;quot;System.String&amp;quot;&amp;gt;rthomson&amp;lt;/AuthorizationObject&amp;gt;&amp;lt;AuthorizationValue type=&amp;quot;System.String&amp;quot;&amp;gt;rthomson&amp;lt;/AuthorizationValue&amp;gt;&amp;lt;/Item&amp;gt;&amp;lt;/Items&amp;gt;&amp;lt;/RepeaterData&amp;gt;</RepeatingSubSection></Item>

</Items>

</RepeaterData>

Just a quick update for anyone coming to this thread with a similar issue: you can replace e.g. &lt; in the NFFormData xml with < by creating a variable and setting the default value to &lt; and then using that variable as the Pattern, and < as the Replacement Text in an Apply Regular Expression action. If you repeat this for &gt; and &quot; etc., you can clean the NFFormData xml and then the query expressions will work.

Reply