cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wleurcor
Frequent Visitor

Adding records from a table to another table

Hello,

 

I have tried googling and reading different solutions but I haven't been able to do exactly what I want.

In my example I have a table with a list of all companies and I want to select one or more of the companies to be added to a table for qualified companies. I'm imagining a gallery with a + button that adds the selected record to a gallery for selected companies.

 

Table 1 
CompanyID
A1
B2
C3
D4

 

Table 2 
Qualified companyID
C3

 

It doesn't have to be a gallery or even a data table as long as I can have a list of all of Table 1 where I can copy records to Table 2 and save the update in the source.

 

Thank you for the help!

1 ACCEPTED SOLUTION

Accepted Solutions
mdevaney
Super User III
Super User III

@wleurcor 

Put this code in the OnSelect property of the + button:

Patch(Table2, Defaults(Table2),{ ‘Qualified Company’: Company, OriginalID: ID })


Then use Table2 in the Items property of the other gallery.  My assumption is Table2 is your qualified companies datasource that you are saving to.

 

You will also notice a new column called OriginalID. That’s because you can’t control the ID number in all datasources other than Excel: it’s auto-assigned.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

4 REPLIES 4
mdevaney
Super User III
Super User III

@wleurcor 

Put this code in the OnSelect property of the + button:

Patch(Table2, Defaults(Table2),{ ‘Qualified Company’: Company, OriginalID: ID })


Then use Table2 in the Items property of the other gallery.  My assumption is Table2 is your qualified companies datasource that you are saving to.

 

You will also notice a new column called OriginalID. That’s because you can’t control the ID number in all datasources other than Excel: it’s auto-assigned.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

365CornerDawid
Resolver III
Resolver III

Hi,

You can achieve such result by mixing Gallery and Collections 🙂 not sure what is your data source so my answer will be based on the collections.
For the testing add in your app Button which and in the OnSelect property add following:
ClearCollect(colCompanies,{CompanyName:"A",CompanyID:1},{CompanyName:"B",CompanyID:2},{CompanyName:"C",CompanyID:3},{CompanyName:"D",CompanyID:4})
Next click the buttone - it will create collection and it will be easier to work with it.
Add Gallery and as Items add colCompanies
Format Gallery but it should looks more or less like 
Annotation 2020-08-03 141343.png

In the plus icon OnSelect add:
Collect(colSelected,ThisItem)
(click plus at least once)
Add next Gallery and add colSelected and you should see new elements when you click plus icon.

Bonus: In the Selected gallery under OnSelect of recycle bin I added so User could remove selected item:
Remove(colSelected,ThisItem)
Annotation 2020-08-03 1451343.png
Hope this will help if yes please dont forget to accept the solution so other can find it easier.
Regards
Dawid

 

 



Thanks for the help @365CornerDawid and @mdevaney. I use excel in OneDrive at the moment. mdevaney's solution worked for me but I also want to understand your solution to improve my Power Apps skills

 

I looked through Collect() function and it states that it works with any data source, but for me it doesn't do anything when I press the button with Collect(Table2,ThisItem). Is it because of the data source or should I explicitly state column names? In my app the column names are identical in the two tables

 

 

Hey,

Thanks no worries. For the intro to the collections you can start for instance with Shane's material https://www.youtube.com/watch?v=yS6mTRlmuls
As it looks like couple things are mixed up here.
If you want to Collect - create virtual table in the memory of Power Apps current session from your Excel data you can just do:
Collect(colName,ExcelTableName) that will create collection of Excel data.

ThisItem is operator used in the Gallery control basically it allows you to refer to selected element of Gallery.

So using Collect(Table2,ThisItem) would work in Gallery and it would add new record of ThisItem to Table2.
If Table 2 is your secondary data source second excel table then Collect might be not the best possible call. Better could be Patch function: https://www.youtube.com/watch?v=gX5q_V3U8-s

My personal approach is that Galleries and Collections are the doors to the great Power Apps so spending time to get this two things will benefit in future 🙂

Hope this will help.
Feel free to ask if anything is unclear. 

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 (1,578)