Changing datasource from SP List to Collections (multi-select person column not collected)

(0) Share

Report

Posted on by jaanihsm

300

Hi! I'm trying to change my datasource from SharePoint list to a collection of that list using Power Automate.I have my

Signers choice column which is a multi-select person column. Everything is working except my Signers column in collections is blank.

How do I correct this? I'm new to changing the datasource to collections. Appreciate your help! If you can please provide screenshots. Thanks in advance.

Categories:

Connecting to Data

I have the same question (0)

All responses (7)

Answers (1)

Verified answer

AndrianaO 213 Moderator on at

Like (1)

Report

Changing datasource from SP List to Collections (multi-select person column not collected)

Hi! Before I explain what to do with your problem, I would like to say I am not sure if you need to use Power Automate at all. You can create a SharePoint connection in your Canvas App and create a collection directly in your App using a simple formula:

ClearCollect(colSP,'Test List')

Where colSP is a collection name and 'Test List' is the connected SharePoint list. It will be much more easier.

So, back again to your question. Your formula is not working because the multi-select column is a table, so you need to work with it properly. Try

ClearCollect(

colListData2,

ForAll(

Table(ParseJSON(TestFlow.Run().data)) As outerLoop,

{

title: outerLoop.Value.Title,

cSigners: Table(

ForAll(

outerLoop.Value.Signers,

{

DisplayName: ThisRecord.DisplayName,

Email: ThisRecord.Email

}

)

)

}

)

);

Please note, that I used "As outerLoop" in the first ForAll() function. You need to do the same and it means you will get errors in all other parts of this formula where you used Value. notation. To fix this you need to replace Value. with outerLoop.Value. (for example, like I did with the sample Title field, so in your case Value.Title -> outerLoop.Value.Title).

Let me know if this works or if you have more questions!

Was this reply helpful? Yes No
jaanihsm 300 on at

Like (0)

Report

Changing datasource from SP List to Collections (multi-select person column not collected)

Hi @AndrianaO! Will try this and let you know. Thanks!

And also thank you for that question.

My organization does not want Power Apps to directly connect to SharePoint lists now as part of security policy and instead connect to the data from a collection created from a flow extracting contents of the lists.

This is the first dilemma I came across with. I have many forms and galleries that now need to interact with collections which is kind of overwhelming for my skill level and there's very little resources out there for this topic.

Appreciate your inputs! Will let you know how this works. :)

Was this reply helpful? Yes No
WarrenBelz 151,730 Most Valuable Professional on at

Like (1)

Report

Changing datasource from SP List to Collections (multi-select person column not collected)

Hi @jaanihsm

This is only an observation comment as to what the "security" issue is with a "direct" connection - does not a query or write function have the same level of "connection" with a data source whether you do it the "easy" way with Power Apps or the (very) "hard" way with a Flow as it is using the same connector in the environment ??
However @AndrianaO has the matter well under control.

Was this reply helpful? Yes No
jaanihsm 300 on at

Like (0)

Report

Changing datasource from SP List to Collections (multi-select person column not collected)

Thanks @AndrianaO! That worked! Will try to learn more about overLoop now. Appreciate your time and knowledge-sharing! Now on to the next steps with this datasource change. Thanks again! :)

Hi @WarrenBelz! Thank you for your comment! Valid question and I also thought the same. If I remember correctly, I think their reasoning was SharePoint list is a persistent, permanent data source while collection is a temporary data storage within the app. However, this has made my attempt for my solution move to production a lot harder as I am very unfamiliar with collections as data source. But greatly appreciate the help of everyone here! Great community!

Was this reply helpful? Yes No
AndrianaO 213 Moderator on at

Like (1)

Report

Changing datasource from SP List to Collections (multi-select person column not collected)

No problem @jaanihsm! I am happy to hear that the formula works.

I also completely agree with @WarrenBelz that from the security point of view, both "easy" and "hard" approaches are completely the same. If this is the reason for doing things in such a hard way I highly recommend you discuss it again with your organization.

It is true that PowerApp collection stores the data temporarily and SharePoint is a permanent data source. The thing is when you are using the connection to SharePoint in your CanvasApp this connection does not make the app (or a user device) a permanent data source (you can read about it in the documentation). The connection allows user to get data from SharePoint when the proper formula is applied. For example, when you are using a gallery and select SharePoint List as a data source, your app gets data from SharePoint to display it in the gallery (but it does not save it permanently anywhere on the device). When users close the app (or if the Internet connection is lost) they cannot access the data anymore. Moreover, you need to use special functions like LoadData() or SaveData() to save the data on the device, but that is not your case at all (if you'd like to learn more about developing offline Canvas Apps and these functions you can read the documentation).

And when you're trying to get the collection with the PowerAutomate Flow you're doing basically the same as a simple connection in the Canvas App (retrieving the data when it is needed, not saving it anywhere on the user's device), but with a way bigger effort.

Of course, the decision is up to you and your company. I just wanted to address the security concerns since you've mentioned that you are unfamiliar with the collections as a data source. I would also appreciate it if you verify my previous answer. Have a nice day!

Was this reply helpful? Yes No
jaanihsm 300 on at

Like (1)

Report

Changing datasource from SP List to Collections (multi-select person column not collected)

Thanks @AndrianaO for that very detailed and helpful response! Greatly appreciated!

You definitely listed some valid points that can help enlighten both ends of our security and development groups.

I will for sure review this and share with them. Hopefully, another discussion will be opened to revisit the policy.

By the way, I am trying to tag your response as the answer (ticking the 'Does this answer your question?' box) but I get the loading spinner and then does nothing. Am I missing something?

Was this reply helpful? Yes No
AndrianaO 213 Moderator on at

Like (0)

Report

Changing datasource from SP List to Collections (multi-select person column not collected)

Happy to help!

You did everything right with "Does this answer your question?", but sometimes this website works slowly. You can try to verify the answer again and maybe this time it'll work :)

Was this reply helpful? Yes No