PowerApps List Relationships with SharePoint

[@lxz86]

the with function is surprisingly well-bundled up👍

[@ShanesCows]

It is one of my absolute favorites. 🥰

[@twoods1969]

Thanks for this video! It was spot-on and was exactly what I was looking for!

[@ShanesCows]

Awesome, love to hear that. 🤩

[@marcinjarzabek7021]

Shane, can you make a video about Sharepoint limitations and how to overcome them?

[@ShanesCows]

Can you give me some examples of things you would like to include? I am curious. :)

[@marcinjarzabek7021]

@@ShanesCows 40k items limit, limit of lookups in one list, general performance issues, slow item deleting

[@janpotyka5986]

The one reason I prefer not using the SharePoint Lookup way, is because if you then want to customize the SPO form in PowerApps, the Lookup Field is a "Complex Type" and as such is not delegable for filtering. Obviously not an issue when not customizing the form though. Thanks for another on point vid Shane.

[@ShanesCows]

You are welcome and I agree with your thought. 🤩

[@mikeymouse2]

​@@ShanesCowsif you can customize the list, it's easy enough to add another text field and set that value equal to the lookup column's value with power apps on save. Easy to do, and i still get to use lookup columns best feature. The parent/child relationship behavior when a parent item is deleted.

[@Roleofm]

@@ShanesCows lookup columns are delegable

[@oluwatobiyusuf]

Thank you so much Shane

[@ShanesCows]

Happy to help!

[@tomka5050]

Great video! I recall you mentioning several times that you are not a fan of using forms in Power Apps and prefer creating your own "Patching" methods. Could you please explain how to upload an attachment to a SharePoint list without using the Form control? Thank you!

[@ShanesCows]

Attachments are not possible with Patch :( The good news is I rarely use Attachments. :) I prefer uploading files and then referencing them than using Attachments.

[@chairattangthong]

Helpful and Thank you so much, really appreciated

[@ShanesCows]

You're very welcome!

[@mehtabidreesi7942]

@@ShanesCows Hi Shane, could you create a video on building a Power Platform app integrated with Dynamics 365 and deploying it using Azure DevOps pipelines for full CI/CD automation? That would be fantastic content

[@adlaimaschiach]

I am looking for a "remove" / "removeif" sample with a Lookup Column , TNX

[@ShanesCows]

I think this is your best bet kzbin.info/www/bejne/rny7gaCBZZV3eaM

[@leithmarshall5267]

Hey thanks Shane, great video and gained a few nuggets from it. One question though and unfortunately you didn't touch on it but does PowerApps support many to many relationships using SharePoint list? I can write the data out to a many to many list, but I can't seem to display it and the related list to the user from within a gallery (in other words a join between the many to many sharepoint list and the related sharepoint list). The scenario we have is a master list (the center of the application), a related table (which forms a library of possible record choices that could be linked to the master list), and in between, the many to many resolving list (some folks call it a junction table) with the primary keys from the master table and the related table stored in it. The patching of all the tables is not the issue, it is as I say displaying the data from the many to many list and the related list in say a gallery. I love how easy it is to do some things in PowerApps, but curse when you try to push the boundaries 🙂.

[@ShanesCows]

When we want to do Many-to-many with Power Apps we make all of the pieces are self and it works fine. So we create the mapping table in the middle and then when we save data we write the IDs directly to the mapping table. Then, we can combine that mapping table with Lookups to the related tables to show the information. Part of me wants to make this a YT video but most of me knows that nobody would want to watch it, except for a handful of people like you. 😎 I will think about it.

[@leithmarshall5267]

@@ShanesCows Hey Shane, thank you for your feedback, it was much appreciated. I went back to basics and created a test harness to validate some of my thinking around Many to Many relationships basing it on a simplified data model and reduced dataset, but still revolving around the (monster) application I've been constructing for our organisation. Long story short, I managed to find a way to make it work, but had quite a bit of code updating and testing to do to validate that it would work ok, and while not perfect, it seems to meet our needs. It would be cool if SharePoint Lists had the concept in their Views to allow multiple tables to be joined so a single view could display data from related sources, rather than just the data from within itself (if you get my drift). If you'd like to have a copy of my test harness sing out. Anyway, thanks again for your feedback.

[@MarcoStrambini]

Good video Thanks. How can I manage this sitation: 3 tables, project list, 1-m table that keep the relationship between Project ids and task ids, task list. In sql is a simple join, here ?

[@ShanesCows]

I think what you are looking for is a mapping table or what I call a glue table. So you would have one table that would have a Lookup to Projects and a Lookup to Tasks. Then if you have 3 tasks for 1 project you have 3 rows, one for each task. You filter that mapping table by project to see the tasks. I have a video that is all about data relationships here kzbin.info/www/bejne/sH-sipmNqtN4iNU if it helps.

[@mtamitolanga]

HI Shane, when I first watched the video I struggled getting my mind around how patching to a variable simultaneously patches to SharePoint/Dataverse as well 😄 I guess as long as it works, we good to go. One more other thing, would you recommend forms or patch? Thank you very much...

[@ShanesCows]

Think about it this way. When the Patch happens, it returns the whole record that was just created. Usually that record just goes no where. But when we put a variable around the Patch then that records gets saved by the variable. I have a video on Patch vs. Forms kzbin.info/www/bejne/jWmtm3looJV8h6M

[@masgitz]

How long does power apps keep that last patch record in memory?

[@ShanesCows]

Until you close the app or you change it.

[@sandramcgechan5817]

Another great video, thank you so much! You mentioned updating a video where you demo how to let the user create multiple children before the parent is saved, would you have a link to the existing video on that? Or is it one of those things where Power Apps has moved on and I should wait for the updated video? P.S. Buddy is such a cutie, but I think that's his couch now...

[@ShanesCows]

This is the video kzbin.info/www/bejne/rpjdn55qjdGDedE functionally it still 100% works. It just looks really old. :) Buddy must have heard you, he literally ran down to my office and jumped on the chair while I was typing this. 🤣 🐶

[@m_o_r_g_o_t_h]

How about realtionships cross-site-collections?

[@ShanesCows]

If you use the method where you set and maintain your own key then you can have cross site collections or even cross platforms. 😎

[@m_o_r_g_o_t_h]

@@ShanesCows but only in the App. How do show this on the listview, like a lookup field?

[@mikeymouse2]

Does anyone have a video talking about how to replicate arguably the best thing about lookup columns and that's enforcing the parent/child relationship when the parent gets deleted. I know how to do it, but maybe there's a more efficient way, specifically when there are several unrelated apps or list that have a reference to the same parent lookup list. It would just be nice to see someone talk about it and the pros and cons.

[@disturbedcarrot]

There a few videos around with using powerapps and lookup columns. I like lookup columns as well because it allows you to enforce referential integrity at the list level rather than the power apps level, especially if edits may occur directly on the list itself.

[@renataaugustinova1399]

Hi, please is it possible to display in parent only latest child? For example newest task name?

[@TheD3dicateDGamerS]

Yup just add additional filter maybe sort by newest modified or created

[@ShanesCows]

I agree with the idea that you would want to do a filter to get just item you want. Maybe use Last(filter(list...)) that would only return the last record. Or use Sort like the other commenter mentioned. :)

[@maheshalamalakala874]

Please explain captch in powerapps

[@ShanesCows]

I will ponder on this. I have never done it before though. 😎

[@maheshalamalakala874]

@@ShanesCows please explain

[@disturbedcarrot]

Sorry, off topic. But that's a cute looking dog.

[@ShanesCows]

Thanks. Buddy is a bernedoodle and stars in a lot of my videos. 😍

[@disturbedcarrot]

@@ShanesCows Love it!

[@pdบ้านสัตว์เลี้ยง]

please make a clip power app approve without power automate flow

[@ShanesCows]

Like this? PowerApps Approvals without using Flow kzbin.info/www/bejne/amqyY2tqmcqCd6s

[@pdบ้านสัตว์เลี้ยง]

@@ShanesCows in case there is a new version ^^

[@pdบ้านสัตว์เลี้ยง]

@@ShanesCows Please make a clip of many approvers with approval history. It will be very helpful. 😊

[@guizot2010]

So, the ONLY reason that SharePoint automatically generates values for the "ID" column is because you called it "ID" column??? If you had given it a different name--such as "Project Number," it wouldn't generate the numbers automatically?

[@swaminawale7386]

Even though you change the name of any column you created, the internal name will not changed... ID column is something which is out of the box and I guess you can't make change in the name

[@ShanesCows]

You cannot change the name of the ID column. Every SharePoint list gets the ID column automatically when you create the list. You could make your own Project Number column but then you would have to write a formula to make it unique yourself.

[@guizot2010]

@@ShanesCows Thank you for the clarification, Shane!

[@MerkDolf]

🖥 😁 👍 ⚡ 👌🖥

[@ShanesCows]

😍