Power Platform Community Forum Thread Details

Hi Community,

I'm struggling to get the formula when there is one collection ie., Collection1 which consists of three columns Name, Email, ItemID and second collection containing three columns ItemName, ItemDescription, Quantity. I would like to Patch records to a sql table which has four columns Item, ItemName, ItemDescription,Quantity. What would be the formula in these scenarios where ItemID is in one collection and ItemName, ItemDesc and Quantity are in another collection. Do I need to use ForAll to update multiple records in sql server?

Thank you!

Categories:

Building Power Apps

Hi @Kitz ,

Do you mean that the second collection is the nested table of Collection1 like the left of screenshot?

What is the column name of second collection in Collection1?

You can try to use Ungroup function to expand the nested table to be like to the right of screenshot.

ClearCollect(CollectionUngroup, Ungroup(Collection1,<GroupColumnName>))

Note: GroupColumnName is the Column name of second collection.

Once the nested table is expanded, just use patch function to patch the record.

For example:

Patch('SQL table', BaseRecord, {ItemID: First(CollectionUngroup).ItemID,ItemName: First(CollectionUngroup).ItemName, ItemDesc: First(CollectionUngroup).ItemDesc, Quantity: First(CollectionUngroup).Quantity })

Annotation 2019-09-26 112321.png