Writing from Array to SharePoint List based on Number of Records in Array

(0) Share

Report

Posted on by pp365

439

Hi all,

I'm hoping someone can help with the following issue when attempting to write from an Array to a SharePoint list, based on the total number of records in the Array.

The Array (arrayCoreMods) will be of indeterminate length (on the basis of the number of items a user checks on a 'Checkbox' style question on a source Microsoft Form). I know how to determine the total number of items in the Array, using Length, and also how to select a specific record with the Array, for example the first item when the Array is placed inside a Compose action named 'ComposearrayCoreMods' by using: (outputs('ComposearrayCoreMods')[0]).

The issue is how to create a 'best practice' (lean) Flow to write each individual record of the Array into an individual SharePoint List column, stopping at the last item in the Array. The attached diagram shows that the first value from the Array (at Position 0) should be recorded in column Module01, the second value from the Array (at Position 1) should be recorded in column Module02, etc. As the Array will be of indeterminate length, there might be only these two columns to write, or the process could continue to Module03, Module04 and so on.

The problem is, as the expression 'outputs('ComposearrayCoreMods')[0]' has a fixed value to determine the record to retrieve from the Array, in theory, that expression could just be added to each column in the SharePoint list with the Array record position hardcoded in to the [ ] section. So for example:

Module01: 'outputs('ComposearrayCoreMods')[0]'

Module02: 'outputs('ComposearrayCoreMods')[1]' etc.

However, this does not work as when the Array is empty, for example, outputs('ComposearrayCoreMods')[2], there is nothing to write to the SharePoint list and the Flow Fails.

I was wondering if a 'Do Until' statement might help, where two variables compare the current Array position to the total number of items in the Array, however this then introduces the issue of how to formulate the 'Update Item' SharePoint action, as even using a Dynamic Value for the Array position in the outputs statement ( outputs('ComposearrayCoreMods')[variables('varArrayPosition')] ) would not work as each time the action ran this number would be updated and the incorrect data would be written.

I'm sure there's a simple explanation for this but I'm not seeing it and as this is quite specific I ran out of terms to try and search for a solution. Many thanks for your help!

Categories:

Building flows

CoreModsArray.png

I have the same question (0)

All responses (10)

Answers (2)

grantjenkins 11,063 Moderator on at

Like (0)

Report

Will there only ever be a maximum of 5 columns in your SharePoint List (as per your screenshot), or more? I'm assuming the number of items in the array will never be greater than the number of columns.

Also, are the columns all Single line of text?

Was this reply helpful? Yes No
pp365 439 on at

Like (0)

Report

Hi @grantjenkins , thanks for your reply.

Currently the list in the image is just a PoC. I anticipate there will be approximately 15 Module Columns in total (Module01, Module02, Module03... etc).

You are absolutely right: the number of items in the array will never be greater than the number of columns. Additionally, currently, the Module columns in the SharePoint list are formatted as "Single line of text".

The write action works correctly when there is data to write. When there is no data at the relevant position of the Array, the write action fails.

Thank you!

Was this reply helpful? Yes No
jiwhite 498 on at

Like (0)

Report

Have you tried adding a Condition that checks if the fields are empty (null), then only adding the record if they're not? Here's an example flow that reads an array and only adds a record if a field in the array, , is not null.

You may also be able to use the filter connector.

Was this reply helpful? Yes No
pp365 439 on at

Like (0)

Report

Hi @jiwhite , thanks for your reply.

I feel there must be a more optimum way of achieving this, at least by an expression. Otherwise (if I've understood correctly) I'd have to run a Condition action for each of 15+ expressions and also have a myriad of Update Item actions for the SharePoint list (potentially one Update Item per Condition, IE at least one call to SharePoint) for each Condition.

I really appreciate your contribution but I'm hoping there's an optimum way to achieve this 🙂

Was this reply helpful? Yes No
jiwhite 498 on at

Like (0)

Report

You can add additional rows in the Condition to check for whatever you need to if there is one outcome. You can also create variables to represent the columns you're setting and set those using functions, then populate the update using the variables. Or, you can use Condition branching. I wish that flows could be programmed using code, rather than a slow, limited UI, but it is what it is. The next option is Azure and custom coding, but that is more expensive.

Was this reply helpful? Yes No
Verified answer

pp365 439 on at

Like (0)

Report
Hi again,

I've figured this out - although I would still be grateful for feedback as there still must be an optimum way of doing this rather than assess every possible record of the Array - especially given it's possible to determine how many records there are in an array in advance of the execution and therefore dynamically work with the maximum number of records in an Array without having to specify a maximum 'by design' of this approach.

Nevertheless, the below ensures only one call to SharePoint to update the list and achieves what I wanted, which is to write the appropriate data to the appropriate column using the relevant record position in the Array and not fail when the Array record is blank.

The solution is to use an If statement, combined with Empty, to determine if there is a record present at the position in the Array. If the statement evaluates to true (there is NO record at the position in the Array), then "null" is used, if False then it inserts the correct data from the Array (which in turn is written to the SharePoint list during the Create Item action).

Credit to this post which helped me formulate the idea.

For the benefit of anyone reading this post, my findings were:

As it's an array, and I'm looking for a specific record position WITHIN the array, the use of Empty worked. In my tests, Length did NOT work when there was no record present at the specified position.
The "value" to use for the If statement false outcome, for the SharePoint list update, must be null. No action is taken and the Flow is successful.

The formula used (in this example, we are looking at record position 3 in the Array, hence the use of [2] ([0] = 1):

If(empty(outputs('arrayCoreMods')?[2]),'There is no record at this position','There is a record at this position')

I will continue to work on improving this and will post any further updates - however additional feedback from Community members much appreciated.

Was this reply helpful? Yes No
jiwhite 498 on at

Like (0)

Report

One more suggestion, you could use nested Apply to each loops to evaluate the columns and do what you want with arbitrary length arrays, as well as add booleans to track what happened to other fields or arrays if needed.

Was this reply helpful? Yes No
Verified answer

grantjenkins 11,063 Moderator on at

Like (0)

Report
@pp365 This is how I would do it using XML and XPath.

The SharePoint List I'm using for this example.

And the Microsoft Form that allows users to select multiple campuses.

See full flow below. I'll go into each of the actions.

When a new response is submitted and Get response details are fairly straightforward.

XML is a Compose that converts the Campuses from the Form to XML so we can use XPath. Note that it also adds a root node so it's valid XML. The expression used is:
xml(json(concat('{"root": { value:', outputs('Get_response_details')?['body/r92fdf12dc7b04e52b3c560b75f0f8589'], '}}'))) //Note that r92fdf12dc7b04e52b3c560b75f0f8589 is the Campuses field.

This would result in XML similar to that below:
<root> <value>Campus A</value> <value>Campus C</value> <value>Campus E</value> </root>

Create item then uses XPath to retrieve the items from our XML. The expressions used for each Module field are below.
xpath(outputs('XML'), 'string(//root/value[1]/text())') xpath(outputs('XML'), 'string(//root/value[2]/text())') xpath(outputs('XML'), 'string(//root/value[3]/text())') xpath(outputs('XML'), 'string(//root/value[4]/text())') xpath(outputs('XML'), 'string(//root/value[5]/text())')

Was this reply helpful? Yes No
pp365 439 on at

Like (0)

Report

Hi @grantjenkins , many thanks for this and for documenting it thoroughly to make it easy to understand. I will test this out. Could I ask three quick questions;

1. Is XML/xpath a more performant method than the method I've described above (using an If statement to test whether an array position is blank)? Are there are another benefits to the XML/xpath method compared to my method?

2. Presumably if there was no data to reference (write) inside one of the xpath expressions in the 'Create item' action - let's say for Module04 for example - the 'Create item' action would not fail and would just ignore the 'blank' xpath statement?

3. Are there any learning resources you'd recommend for XML/xpath as presumably there are other instances where these approaches/tools would be beneficial?

Once again many thanks @grantjenkins !

Was this reply helpful? Yes No
pp365 439 on at

Like (0)

Report

Hi @grantjenkins ,

I've tested the XML route and it works very well - thank you very much for the pointer on this. When you have a moment I wondered if you could share thoughts on whether there are there any learning resources you'd recommend for XML/xpath in the context of Power Platform, as presumably there are other instances where these approaches/tools would be beneficial?

Edit: During my own testing I noted the efficiencies of the XML/xpath method you've recommended and feedback here for the benefit of anyone referencing this thread in the future. The data does not need to be transformed in any significant way in your method (does not need to be split, put in to an array, testing the array positions for whether they contain data or not, etc). An optimum solution! Accordingly, I've ensured that I've marked your post as the accepted solution - many thanks.

(Note: I also answered my other question during testing: if the xpath expression is empty/blank, the Flow does not fail - which is expected)

Once again many thanks @grantjenkins !

Was this reply helpful? Yes No