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.
Will do, thanks.
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.
@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.
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.
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.
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.
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.
WarrenBelz
791
Most Valuable Professional
MS.Ragavendar
410
mmbr1606
275
Super User 2025 Season 1