cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
daffelito
New Member

Method for collect all objects in array

Hi.

I have built a custom connector within power apps. The connector gets data from Microsoft Graph API. I have managed to get the schema for the response-JSON right. The response is in a variable, since it is multiple objects

 

I do a

Set(responsevariable, Graph.Myreport().value)

So I dont have to re-query the api all the times.

 

What I want after that is to get every single object in that response array to be a row in my datasource.

 

I have tryed to do it this way:

Collect(datasource; {field1: responsevariable.id ,, field2: responsevariable.name})

All i get is "datasource expects an string but you are collecting a table value"

 

So the question is, how do I collect all objects in the array to my datasource!?

1 ACCEPTED SOLUTION

Accepted Solutions
mr-dang
Community Champion
Community Champion

Hi @daffelito,

It sounds like you are getting a table back from the Graph API, and you want it written back to your datasource.

 

 

Collect(datasource; {field1: responsevariable.id ,, field2: responsevariable.name})

The formula you've written above is not working because responsevariable.id is returning the entire column of ids from the table.

 

What you want to do instead is to prepare a collection to match the columns in your datasource. This can be done with ForAll. When the columns align, you can collect that collection to the datasource. 

 

 

Below is an example of what this might look like with comments:

 

// Prepare a temporary collection to have the exact same schema as the connected datasource.
ClearCollect(tempCollection,Defaults(datasource));
Clear(tempCollection);

// For each of the records retrieved from GraphAPI, prepare the record to be written back to your datasource by lining up the right columns
ForAll(responsevariable,
    Collect(tempCollection,
        {
            column1: [corresponding field from response variable]
            column2: [corresponding field from response variable]
        }
    )
);

// Write the records from the temporary collection back to the connected datsource
Collect(datasource,tempCollection)

 

Here's a video on this topic:

https://www.youtube.com/watch?v=mn6RHMzjPxM

 

Let me know if you have further questions after watching the video.

 

 

Microsoft Employee
@8bitclassroom

View solution in original post

3 REPLIES 3
mr-dang
Community Champion
Community Champion

Hi @daffelito,

It sounds like you are getting a table back from the Graph API, and you want it written back to your datasource.

 

 

Collect(datasource; {field1: responsevariable.id ,, field2: responsevariable.name})

The formula you've written above is not working because responsevariable.id is returning the entire column of ids from the table.

 

What you want to do instead is to prepare a collection to match the columns in your datasource. This can be done with ForAll. When the columns align, you can collect that collection to the datasource. 

 

 

Below is an example of what this might look like with comments:

 

// Prepare a temporary collection to have the exact same schema as the connected datasource.
ClearCollect(tempCollection,Defaults(datasource));
Clear(tempCollection);

// For each of the records retrieved from GraphAPI, prepare the record to be written back to your datasource by lining up the right columns
ForAll(responsevariable,
    Collect(tempCollection,
        {
            column1: [corresponding field from response variable]
            column2: [corresponding field from response variable]
        }
    )
);

// Write the records from the temporary collection back to the connected datsource
Collect(datasource,tempCollection)

 

Here's a video on this topic:

https://www.youtube.com/watch?v=mn6RHMzjPxM

 

Let me know if you have further questions after watching the video.

 

 

Microsoft Employee
@8bitclassroom

View solution in original post

Thanks, it worked like a charm!

Btw brilliant tutorial!

fatimat
Frequent Visitor

Hi @mr-dang ,

 

I used this concept in conjuction with Shane Young's repeating tables tutorial, but it doesn't seem to be working.

 

I have:

dbo.items -  database table and items in my gallery

colItems - local collection

galfieldX - any of the 4 editable fields in my gallery

 

Onvisible: (screen)

ClearCollect(colItems,Defaults(dbo.items)));
Clear(colItems)

Onchange: (applied to each galfieldX)

If(ThisItem.ID exactin colItems.ID,Remove(colItems,LookUp(colItems,ID = ThisItem.ID)));
Collect(colItems,
{
ID: ThisItem.ID,
Order: Value(galfieldX.Text),
Description: galfieldX.Text,
Input: galfieldX.Text,
ExpectedResults: galfieldX.Text
})

 

Onselect: (external save button)

Collect(dbo.items,colItems);
Clear(colItems)

 

I don't have a save button for each line in the gallery because this is supposed to be a bulk edit function. The collection gets updated, but when I click the save button, it's just not successfully written back to dbo.items.

 

Can you please tell me what I missed?

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,118)