web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Merge two tables into ...
Power Apps
Unanswered

Merge two tables into one Collection(Patch)

(0) ShareShare
ReportReport
Posted on by lairpr

Hi,

 

I am in need of some help or guidance on how to merge two collections(tables) and Patch the data of both collections. 

Here are the two collections I need to merge and the Patch function;

//Collection 1
ClearCollect(Collection1, Table({DISP_NAME:cmbPR1Approvers.SelectedItems.APPROVER,APPROVER_NAME:cmb1Approvers.SelectedItems.APPROVER,APPROVER_EMAIL:cmb1Approvers.SelectedItems.APPROVER_EMAIL,APPROVER_ID:cmb1Approvers.SelectedItems.APPROVER_ID,APPROVER_USER_ID:cmb1Approvers.SelectedItems.APPROVER_ID, APPROVED_STATUS:"Pending"}) );

//Collection 2
ClearCollect(Collection2, {DISP_NAME:cmb2Approvers.SelectedItems.APPROVER,APPROVER_NAME:cmb2Approvers.SelectedItems.APPROVER,APPROVER_EMAIL:cmb2Approvers.SelectedItems.APPROVER_EMAIL,APPROVER_ID:cmb2Approvers.SelectedItems.APPROVER_ID,APPROVER_USER_ID:cmb2Approvers.SelectedItems.APPROVER_ID, APPROVED_STATUS:"Pending"} );

Merged Collections and Patch
ForAll(
 MainCollection,
 If(
 !IsBlank(APPROVER_DISPLAY_NAME),
 Patch(
 First(Filter(TBL_APPROVERS, APPROVER_DISP_NAME = APPROVER_DISP_NAME)),
 
 Defaults(APPROVERS),
 {
 APPROVER_DISP_NAME: APPROVER_DISPLAY_NAME,
 APPROVER_NAME: APPROVER_NAME,
 APPROVER_EMAIL: APPROVER_EMAIL,
 APPROVER_ID: APPROVER_ID,
 APPROVED_STATUS: APPROVED_STATUS,
 APPROVER_USER_ID: APPROVER_USER_ID
 }
 )
 )
);

 

Thanks! 

Categories:
Building Power Apps
I have the same question (0)
  • WarrenBelz Profile Picture
    WarrenBelz 156,277 Most Valuable Professional on at

    Hi @lairpr ,

    I am not entirely sure on your reference to APPROVERS and you are patching to TBL_Approvers. You also did not mention your data source, but I have used ID as the list identifier.

    ClearCollect(
 ColToPatch, 
 {
 DISP_NAME:cmbPR1Approvers.SelectedItems.APPROVER,
 APPROVER_NAME:cmb1Approvers.SelectedItems.APPROVER,
 APPROVER_EMAIL:cmb1Approvers.SelectedItems.APPROVER_EMAIL,
 APPROVER_ID:cmb1Approvers.SelectedItems.APPROVER_ID,
 APPROVER_USER_ID:cmb1Approvers.SelectedItems.APPROVER_ID, 
 APPROVED_STATUS:"Pending"
 },
 {
 DISP_NAME:cmb2Approvers.SelectedItems.APPROVER,
 APPROVER_NAME:cmb2Approvers.SelectedItems.APPROVER,
 APPROVER_EMAIL:cmb2Approvers.SelectedItems.APPROVER_EMAIL,
 APPROVER_ID:cmb2Approvers.SelectedItems.APPROVER_ID,
 APPROVER_USER_ID:cmb2Approvers.SelectedItems.APPROVER_ID, 
 APPROVED_STATUS:"Pending"
 } 
);
ForAll(
 colToPatch as aPatch,
 With(
 {
 wPatch:
 LookUp(
 TBL_APPROVERS,
 APPROVER_DISPLAY_NAME = aPatch.APPROVER_DISPLAY_NAME
 )
 },
 Patch(
 TBL_APPROVERS,
 If(
 !IsBlank(wPatch.ID),
 {ID:aPatch.ID}, 
 Defaults(TBL_APPROVERS)
 ),
 {
 APPROVER_DISP_NAME: aPatch.APPROVER_DISPLAY_NAME,
 APPROVER_NAME: aPatch.APPROVER_NAME,
 APPROVER_EMAIL: aPatch.APPROVER_EMAIL,
 APPROVER_ID: aPatch.APPROVER_ID,
 APPROVED_STATUS: aPatch.APPROVED_STATUS,
 APPROVER_USER_ID: aPatch.APPROVER_USER_ID
 }
 )
 )
);

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

  • lairpr Profile Picture
    lairpr on at

    Hi,

    TBL_Approvers is a SQL table. I am using SQL to store the data. 

    I am going to give your example a try. 

    Thank you.

  • lairpr Profile Picture
    lairpr on at

    Warren,

    On the Lookup: 

    LookUp(
 TBL_APPROVERS,
 APPROVER_DISPLAY_NAME =(ERROR: These types cant be compare: Text, Table 

) aPatch.APPROVER_DISPLAY_NAME
 )

     

    And on this one: 

    If(
 !IsBlank(wPatch.ID),
 {ID:aPatch.ID}, 
 Defaults(TBL_APPROVERS)
 }, <-----HERE (The formula contains 'PareClose' where 'CurlyClose' is expected.)

    Thanks!

  • WarrenBelz Profile Picture
    WarrenBelz 156,277 Most Valuable Professional on at

    Hi @lairpr ,

    Note I free-typed this in notepad and it was more to give you an idea of the structure you might need.

    The second one was just a wrong bracket type (now fixed - I thought you might have seen that) - the first is suggesting APPROVER_DISPLAY_NAME in the Table TBL_APPROVERS is a different field type to the same field name in the Collection colToPatch - I can not see your data and the more important thing is if I have interpreted what you are tying to do correctly.

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

     

     

  • lairpr Profile Picture
    lairpr on at

    Warren,

    Thank you for your reply. I was able to modify the patch function as you suggested. But is not adding the collection data in the SQL table and no errors are thrown. 

    Here is what I have: 

    ForAll(
 ColApprovers As aPatch,
 With(
 {
 wPatch:
 LookUp(
 APPROVERS, //table where the approvers, name, email, and id are stored
 APPROVER in aPatch.APPROVER_NAME //APPROVER is the name in the collection
 )
 },
 Patch( 
 If(
 !IsBlank(wPatch.APPROVER_ID),
 {APPROVER_ID:aPatch.APPROVER_ID}, 
 Defaults(LdC_REQ_APPROVER) //table where the data needs to be saved
 ),
 {
 DISP_NAME: aPatch.APPROVER_NAME,
 NAME: aPatch.APPROVER_NAME,
 EMAIL: aPatch.APPROVER_EMAIL,
 ID: aPatch.APPROVER_ID,
 STATUS: aPatch.APPROVED_STATUS,
 USER_ID: aPatch.APPROVER_USER_ID,
 REQ_ID: Frm1.LastSubmit.ID
 }
 )
 )
);
  • WarrenBelz Profile Picture
    WarrenBelz 156,277 Most Valuable Professional on at

    Hi @lairpr ,

    You left the table name out of the Patch statement

    ForAll(
 ColApprovers As aPatch,
 With(
 {
 wPatch:
 LookUp(
 APPROVERS, //table where the approvers, name, email, and id are stored
 APPROVER in aPatch.APPROVER_NAME //APPROVER is the name in the collection
 )
 },
 Patch( 
 LdC_REQ_APPROVER,
 If(
 !IsBlank(wPatch.APPROVER_ID),
 {APPROVER_ID:aPatch.APPROVER_ID}, 
 Defaults(LdC_REQ_APPROVER) //table where the data needs to be saved
 ),
 {
 DISP_NAME: aPatch.APPROVER_NAME,
 NAME: aPatch.APPROVER_NAME,
 EMAIL: aPatch.APPROVER_EMAIL,
 ID: aPatch.APPROVER_ID,
 STATUS: aPatch.APPROVED_STATUS,
 USER_ID: aPatch.APPROVER_USER_ID,
 REQ_ID: Frm1.LastSubmit.ID
 }
 )
 )
);

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

  • lairpr Profile Picture
    lairpr on at

    Warren,

    I had no luck patching the data from both tables(collections).

    When the table to be Patch is added:

    Patch(
    SQL_TABLE,
    Does not match the expected text found in the table. All names match with table columns.
    {
    DISP_NAME: aPatch.APPROVER_NAME,
    NAME: aPatch.APPROVER_NAME,
    EMAIL: aPatch.APPROVER_EMAIL,
    ID: aPatch.APPROVER_ID,
    STATUS: aPatch.APPROVED_STATUS,
    USER_ID: aPatch.APPROVER_USER_ID,
    REQ_ID: Frm1.LastSubmit.ID
    }

    ColApprovers
    All the data is display in tables

    2021-09-30 23_13_41-Power Apps and 57 more pages - Profile 1 - Microsoft​ Edge.png

    I found the bellow example and when tested the way it is it works.
    But when I try to modify it, it doest work.
    //ClearCollect(DirectoryData,Table({FirstName:"John",FCDUserEmail:"test@test.tst"},{FirstName:"Bob",FCDUserEmail:"test2@test.tst"}));
    //ClearCollect(ProfileData,{LastName:"Smith",FCDProfileEmail:"test@test.tst"});
    //ClearCollect(mergedData,AddColumns(DirectoryData,"LastName",LookUp(ProfileData,FCDUserEmail=FCDProfileEmail,LastName)))

    Not sure what is not working. I have tried different options and still cant get the data from both collections added to the SQL table. 

  • WarrenBelz Profile Picture
    WarrenBelz 156,277 Most Valuable Professional on at

    @lairpr ,

    All I can do is give you valid syntax (which the structure is). I can see a heap of tables in your collection and you are referring to them in your Patch as single values - that is probably what is not matching.

  • lairpr Profile Picture
    lairpr on at

    Warren,

    Thank you for your input. 

    How I can then patch the tables correctly with multiple values? 

  • WarrenBelz Profile Picture
    WarrenBelz 156,277 Most Valuable Professional on at

    @lairpr ,

    The problem is the opposite - you are trying to Patch a Table with a single value. A better question I think is if your data structure is correct considering what you are trying to do with it.

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 396 Most Valuable Professional

#2
11manish Profile Picture

11manish 134 Super User 2026 Season 2

#3
MS.Ragavendar Profile Picture

MS.Ragavendar 101 Super User 2026 Season 2

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans