Create collection from power automate where the data comes from get items inside an apply to each?

(1) Share

Report

Posted on by drawser

121

Hi,

I have this scenario Im trying to resolve:

When user opens the app the user is identified as either User 1, User 2 etc etc.

I have an SP-list that looks like this

Powerapps triggers a flow and asks for which User it is.

Based on this input the UserAccess list is filtered,

so User 1 will output:

User 1 Site A

User 1 Site B

After this I have another list that looks like this

Automate filters this list based on the site from the previous list,

so this will give all results for Site A and Site B

whitin an Apply to each.

I then use the Select to Select all values from the filtered SiteResults

And after this I send the outputs of the Select back to PowerApps

outputs('Select')

This returns data to powerapps,

but I can't for the life of me figure out how to put this in a collection

I can put in a variable without issue

Set(varSiteResults,
                TestGetUsersSites.Run(
                    "User 1"
                ).testcollection
)

I tried this for a collection

ClearCollect(
    QTestUser,
    ForAll(
        Table(ParseJSON(TestGetUsersSites.Run("User 1").testcollection).body),
        {
            Site: Text(Value.Site),
            Result: Text(Value.Result),
            Period: Text(Value.Period)
        }
    )
)

But it just tells me:

Because I guess the flow doesnt return an array but rather an object?

I also tried appending it to an array and sending it back to PowerApps but no luck.

Anyone know how to send a filter array from an apply to each to powerapps and store it in a collection?

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

realwillwright 772 Moderator on at

Like (0)

Report
Hi, There are a couple of character differences in your Flow, response and formula. Could you try this.

ClearCollect( QTestUser, ForAll( Table(ParseJSON(TestGetUsersSites.Run("User 1").TestCollection).body), { Site: Text(Value.SIte), Result: Text(Value.Result), Period: Text(Value.Period) } ) )

Was this reply helpful? Yes No
drawser 121 on at

Like (0)

Report

Thanks for the reply, sadly no difference.

Still getting the Can not use the '.' operator on the object type.

My main guess is because when you get the data from the apply to each it adds the brackets around the response

[ ]

{

"testcollection": "[{\"body\":[{\"SIte\":\"Site A\",\"Result\":\"Approved\",\"Period\":\"202502\"},{\"SIte\":\"Site A\",\"Result\":\"Approved\",\"Period\":\"202503\"}]},{\"body\":[{\"SIte\":\"Site B\",\"Result\":\"Not Approved\",\"Period\":\"202502\"},{\"SIte\":\"Site B\",\"Result\":\"Approved\",\"Period\":\"202503\"}]}]"

}

And It probably have to look like this maybe?

{

"testcollection": "{\"body\":[{\"SIte\":\"Site A\",\"Result\":\"Approved\",\"Period\":\"202502\"},{\"SIte\":\"Site A\",\"Result\":\"Approved\",\"Period\":\"202503\"}]},{\"body\":[{\"SIte\":\"Site B\",\"Result\":\"Not Approved\",\"Period\":\"202502\"},{\"SIte\":\"Site B\",\"Result\":\"Approved\",\"Period\":\"202503\"}]}"

}

Was this reply helpful? Yes No
drawser 121 on at

Like (0)

Report
I do have a workaround that works using two flows where you run the second flow within a ForAll.

But it would be nice if this could be done in just one flow.

ClearCollect( QGetUserAccess, ForAll( Table(ParseJSON(GetUserAccess.Run("User 1").accesscollection).body), { User: Text(Value.User), SiteAccess: Text(Value.SiteAccess) } ) ); ForAll( QGetUserAccess As QG, Collect( QForAllTest, ForAll( Table(ParseJSON(ForAllCollectionTest.Run(QG.SiteAccess).resultcollection).body), { Site: Text(Value.Site), Result: Text(Value.Result), Period: Text(Value.Period) } ) ) )

Was this reply helpful? Yes No
Suggested answer

LowCodeJim 306 Moderator on at

Like (0)

Report
Hi @drawser,

Is there a reason you need to use Power Automate? I have recreated your lists and added them as data sources to the app and created a third collection which I think contains the data you are after:

I added the formula to the OnChange of the dropdown list but you could easily just add it OnStart or as a Named Formula. The formula I used is:

Clear(UserSiteResults); With( { UserSites: Filter( UserAccess_1, User = Self.Selected.Value ) }, ForAll( UserSites As UserSite, Collect( UserSiteResults, Filter( SiteResults_1, Site = UserSite.SiteAccess ) ) ) )

UserAccess_1 and SiteResults_1 are the two SharePoint list data sources and you can change the "Self.Selected.Value" in the With statement to be whatever value you want to use to filter the UserAccess list.

Hopefully I have understood what you are after and this helps!

Was this reply helpful? Yes No
drawser 121 on at

Like (0)

Report

Sorry didn't say why Im using automate,

I don't want the users to have access to the datasource.

So Im using automate to bring in data with either mine or a service accounts credentials.

So users only have access to the data inside the app and can only view the items that are filtered based on who is using the app.

Was this reply helpful? Yes No
Verified answer

LowCodeJim 306 Moderator on at

Like (0)

Report
Hi @drawser,

Ah ok. In that case then I think the easiest solution is to initialise an array variable in your flow and add each result to it. As you eluded to the main issue is that you have nested arrays in your response (due to the Outputs('Select') gathering all the arrays together into an array. I have created the flow below and replaced my previous formula with this formula and got the same expected results as my previous post:

ClearCollect( UserSiteResults, ForAll( Table(ParseJSON(GetUserSiteAccess.Run(Self.Selected.Value).usersiteaccess)), { Site: Text(Value.Site), Result: Text(Value.Result), Period: Text(Value.Period) } ) )

Hopefully everything makes sense from the screenshot but let me know if you want me to expand on any of the actions.

Was this reply helpful? Yes No
drawser 121 on at

Like (0)

Report

Thanks,

two questions

What do you put in the append to array?

And how do you put the array inro the "Respond to powerapps"?

Edit, got it working by doing this:

Output from Select

Expression for items

item()?['Site'] etc etc

Respond to PowerApps

Expression:

variables('ResultsArray')

Was this reply helpful? Yes No
LowCodeJim 306 Moderator on at

Like (0)

Report

@drawser Added some extra explanation below:

The second loop is looping the array created by the select statement (the Output of the Select action):

I am then appending the current item in that loop to the array variable (this is also the Output Item of the Select action):

I am then just passing the array variable straight back to PowerApps (which is then parsing it as JSON

Let me know if you have any other questions

Was this reply helpful? Yes No