Skip to main content
Community site session details

Community site session details

Session Id : cFH4WQVKXvRh2LKC87XJRS
Power Apps - Building Power Apps
Answered

Update a Sharepoint list with values from another one

Like (0) ShareShare
ReportReport
Posted on 19 May 2020 17:51:42 by 281

I have created a stock monitoring app based on two Sharepoint lists. The first list has stock item (text column) and quantity (number column) data as well as other fields for each item. The second list has stock movements (number column) in it, with columns for item (a Lookup field from the stock item column in the first list), date, stock out quantity (number column) and location (choice column) it is out to. 

My app is linked to both lists.

I can view, edit and add new items to the first list and I can view, edit and add new items to the second list.

My question is, what is the best way for me to link the lists? At the moment they act independently. I want the stock out quantity from list 2 to update the relevant stock item's stock quantity in the first list every time a stock item is taken out.

How do I do this with my app? And, is this the best way?

Thanks.

  • Powerplatform01 Profile Picture
    386 on 27 Jul 2021 at 09:35:38
    Re: Update a Sharepoint list with values from another one

    Hi, 

    Please watch

    https://youtu.be/639qnKw85n8

     

    Please like and subscribe it.  it's work for you.

  • IzzyWizz Profile Picture
    281 on 21 May 2020 at 18:47:56
    Re: Update a Sharepoint list with values from another one

    Will do, thanks.

  • Verified answer
    Drrickryp Profile Picture
    Super User 2024 Season 1 on 21 May 2020 at 18:43:39
    Re: Update a Sharepoint list with values from another one

    @IzzyWizz 

    You are correct with regards to the foreign key. As far as your next issue, you can make that a new question.  Please mark this one solved if you have it working.  Take a look at how to create an inventory app here:https://powerapps.microsoft.com/en-us/blog/creating-a-swag-inventory-app/ .  Give it a try on your own and come back if you have a problem.

  • IzzyWizz Profile Picture
    281 on 21 May 2020 at 18:37:34
    Re: Update a Sharepoint list with values from another one

    @Drrickryp Thank you! That is the gallery displaying properly now. I see that in this version of the app, I had the wrong dropdown number too but it was the .Title bit that I hadn't got in any of the many versions I tried.

    PowerApps does seem a bit buggy, sometimes I have had things throw up an error and then I have done exactly the same thing again and it works. Shane Young's 'cut and paste back' tip is a good one. It's a bit frustrating as I don't then know if the problem is me writing rubbish or that I just need to keep trying.

    Am I correct that to make the foreign key in Sharepoint would mean a LookUp column in my 'many' table (e.g. ChemID) sourced from the ID column in the 'one' table?

    Once I can get the basics working, I next need to update the stock quantity field in the 'one' table by subtracting the quantity issued value from it every time a new submission is made in the 'many' table. Are you able to tell me what function I would need to use? Knowing what to call something makes searching so much easier.

    Thanks.

  • Drrickryp Profile Picture
    Super User 2024 Season 1 on 21 May 2020 at 15:24:26
    Re: Update a Sharepoint list with values from another one

    Hi @IzzyWizz 

    I think the problem is in the Filter for the gallery.  It should be Filter('test stock out',Title=Dropdown2.Selected.Title)).  I believe that this will work. 

    Assuming your One side list is called Items, then ideally there should be a number column in the Many table 'Test Stock Out' for the ID from your Items list, call it itemID that is the foreign key.  What you have done is create the foreign key on the Title column.  That will work but it isn't ideal unless you create an index on the Title column in the 'Test Stock Out'.  This has to be done in SharePoint. https://support.microsoft.com/en-gb/office/add-an-index-to-a-sharepoint-column-f3f00554-b7dc-44d1-a2ed-d477eac463b0  If your list gets very large, your app will slow down unless an index is added.  Rather, I would suggest that you add a column to the Test Stock Out list called itemID that will be the foreign key in the Many side.  

     

  • IzzyWizz Profile Picture
    281 on 21 May 2020 at 11:02:36
    Re: Update a Sharepoint list with values from another one

    Hi @Drrickryp,

    I have gone through your blog posts and Shane's video and set up two Sharepoint lists which mirror your customer and order examples (I think) and made the five screen app. I have, rather to my surprise managed to successfully submit a record on the 'many' side of the relationship table based on a dropdown list that is drawn from the Title column on the 'one' side.  But what I can't do, despite trying for hours and recreating the tables in both Excel and Sharepoint is get a Gallery that shows the items in the 'many' list filtered on a dropdown which draws its values from the 'one'. 

    This is about by fifth iteration and the submit new record form is working but what am I doing wrong in the Items option for the gallery? I have tried adding .Text, .Value, Selected text and I have also made a version using the ID column in the 'one' as a Lookup column in the 'many' but that didn't work either. If my submit form works happily with details from two data sources, I just can't work out why the gallery won't. Something simple hopefully!

    Thanks.

    gallerydropdownitems.jpgmanytable.jpgonetable.jpg

  • IzzyWizz Profile Picture
    281 on 20 May 2020 at 07:36:44
    Re: Update a Sharepoint list with values from another one

    Hi @Drrickryp,

    Thank you. I will work through those and see how I get on. I'm liking it already and I haven't got beyond Young Frankenstein.

    Izzy.

  • Drrickryp Profile Picture
    Super User 2024 Season 1 on 19 May 2020 at 18:16:24
    Re: Update a Sharepoint list with values from another one

    Hi @IzzyWizz 

    All SharePoint lists have a hidden ID column.  You use this to link the two lists. In a One to Many relationship, the ID from the One side is inserted as a number column into the Many side. It is typical to name this column by the name of the One side.  So for example, if you have a Customer list and Order list, the Order list should contain a column called customerID.  For more information regarding how to organize your data so that it will work in PowerApps, please review my series https://powerusers.microsoft.com/t5/News-Announcements/Database-Design-Fundamentals-and-PowerApps-An-Overview/ba-p/184485 .  The relevant post related specifically to your question is https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Principles-and-PowerApps-Step-3-Keys-and/ba-p/188640 and the last one in the series shows how to use your lists to create an app in PowerApps.

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

Quick Links

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 791 Most Valuable Professional

#2
MS.Ragavendar Profile Picture

MS.Ragavendar 410

#3
mmbr1606 Profile Picture

mmbr1606 275 Super User 2025 Season 1