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 / Update a Sharepoint li...
Power Apps
Answered

Update a Sharepoint list with values from another one

(0) ShareShare
ReportReport
Posted on by IzzyWizz 291

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.

Categories:
Building Power Apps
I have the same question (0)
  • Drrickryp Profile Picture
    Drrickryp Super User 2024 Season 1 on at

    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.

  • IzzyWizz Profile Picture
    IzzyWizz 291 on at

    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.

  • IzzyWizz Profile Picture
    IzzyWizz 291 on at

    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

  • Drrickryp Profile Picture
    Drrickryp Super User 2024 Season 1 on at

    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
    IzzyWizz 291 on at

    @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.

  • Verified answer
    Drrickryp Profile Picture
    Drrickryp Super User 2024 Season 1 on at

    @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
    IzzyWizz 291 on at

    Will do, thanks.

  • Powerplatform01 Profile Picture
    Powerplatform01 386 on at

    Hi, 

    Please watch

    https://youtu.be/639qnKw85n8

     

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

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Users!

Kudos to our 2025 Community Spotlight Honorees

Congratulations to our 2025 community superstars!

Congratulations to the February Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 519 Most Valuable Professional

#2
11manish Profile Picture

11manish 489

#3
Haque Profile Picture

Haque 327

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