Common Data Service Relationships and Lookups with Power Apps canvas apps
Vložit
- čas přidán 25. 07. 2024
- In this video, you will learn how to setup and use Lookup columns in the Common Data Service aka CDS aka Dataverse. We talk about Filter, Patch, Collections, Forms, and more all while helping you build better relationships in your Power Apps Canvas Apps. I also skip all of the "relationship" jokes I wanted to tell.
Common Data Service Playlist • Common Data Service
Power App Training at training.powerapps911.com - Věda a technologie
You keep answering all my questions, and even the ones I still didn't have when I saw the video, but sure I was going to have later on. Thak you so much dude!.
Glad I could help!
Thanks Shane Young. I am watching your videos and they are really helpful :)
Great to hear!
Great point on Explicit Column Selection this has been catching me out a bit recently, now I fully understand whats happening.
Awesome Jamie.
Huge thanks to you Shane. I've been watching your videos for some time now and they've been a massive help. Having watched this video some time ago, I'd forgotten all about "Explicit Column Selection" and it's been causing me no end of frustration, as soon as I saw your explanation once again, I knew exactly what had been causing me so many problems. I hope you can find some time to do some more videos on 'FlexiDataMegaVerse' (or whetever it's called these days), [hint] It would be great to see something on Navigating many-to-many relationships in canvas apps. You have my undying gratitude. Thanks again.
Thanks Matt. I have never done many to many 😳
@@ShanesCows I'm beginning to wish I hadn't !! I can't get any of my galleries to refresh / update when linking records together 🥲 Looks like I'm going to have to roll out a few more collections.
Thanks for a good video into how CDS works... have felt it daunting before but this gave me a good introduction :-)
Glad to help. 😀
Another great video, thank you!
Glad you enjoyed it!
Love your channel, Shane!
I have been STRUGGLING to figure out how to filter a table on the 1 side of a 1:M relationship. The scenario in a nutshell: I have a table VacationRequests that has a lookup field to table Employees (i.e the employee who made the request). I want show Employees in a gallery that DO NOT have a VacationRequest scheduled on a given day. I have tried too many combinations of FILTER and LOOKUP to list but still can't quite get it. Do you have any suggestions or functions/patterns that I could research?
Thanks again for the great video!
My pleasure!
Great work Shane, my goto Power Platform Guy! 😄
Awe shucks! Thanks
Very nice Shane. You should make video about many-to-many relationships.
I have never used them so not sure I am the right person. But thanks for the encouragement 😀
Hi Shane, always liking the video's you create. Keep them coming! That said, i want to know if there is a possibility to shown the relationship between grandparent > parent > child relationship. Basically showing all the "grand children" in one view based on that one grandparent. BR Rob!
Rob this video talks about parent/child. czcams.com/video/DylxsXIUyDc/video.html Take the concept and yes you can add grand parent :)
Another awesome video Shane! Can you let us know when to choose CDS / Data Flex (whatever) and when to choose Sharepoint as our data source please?
Finally great lighting 💡
Thank you. It has been a real struggle but getting better :)
Hello Shane, thanks for sharing this video. I would like to ask if you have a video showing how to transform and sync data between Dynamics CRM 365 online and SharePoint online list using Dataflows.
I do not have one that specific. I do talk about Dataflows and SharePoint a bit in this video if it helps czcams.com/video/BhLqg1jsbTI/video.html
new background looks more clear & better
Agreed! Lighting seems better too. Well done Shane!
Thank you both! Still not perfect but getting better is all I can hope for.
Yay CDS/Dataflex video!!!
Glad you like it!
Hi Shane, thanks for these awesome videos! They've been helping me a lot!
I'm creating an app using existing related data out of Business Central. I've created entities in CDS which sync both ways with Business Central to the respective tables. My question is, how do I create relationships between these entities using the existing keys and thus keeping the relationships already there?
Yikes. Not saying it isn’t possible but I have never done it. You have the is field so I think you could remake the connection indirectly with using the Lookup function
CDS has an alternate key field for this use case to store the data. Check the Dynamics 365 docs for info.
You said that the primary key field is the same name as the entity. For almost all cases that is true. However, it is possible to have created the entity with the key field text being different than the entity name. In fact, you can edit the key field name. Just something to be aware of. Having them named the same makes the dot notation confusing too. If you want to compare records by GUID you might write Account.Account which means AccountRecord.AccountGUID. Fun stuff. 😂
Thanks for sharing 🐶
Hi Shane, thanks for sharing this video. Question: Have you tried filtering lookup field for null or empty value? I have tried IsBlank function but power apps is giving me a delegation warning.
Try FieldName = Blank() I think that is the way to get around delegation.
The With() fonction... I did not know it existed :(
Now I do!
I wanted to work with temporary variables for so long like I did with python in Dynamo. Declaring a global variable just to use in formula was a waste. Thank you!
I love With so much it has its own video :) czcams.com/video/AYD2oWue7fw/video.html
Hi Shane,
I'm from Brazil and always followed your videos. I am always learning something new from them.
Do you think about doing some content about integrating power apps with smarthome?
I haven’t but would be fun. I will think about it 😀
Hi Shane,i have a lookup column and a self lookup column above it,is there an option to print the self lookup column in a gallery
UpdateIf explanation is very helpful to know about. Thank you.
Unrelated question: I have a gallery that breaks the words of some text I'm trying to memorize using the split function. Then I'm using Set(hint01,Concat(FirstN(Gallery1.AllItems.Title3,5),Title3&" ")) as a variable that becomes the text for a label to give the first five words of the text I'm trying to memorize. Any tips how to get code for the next five words and then for the next five words after that?
Glad it was helpful!
I haven't done it. But what I would think about is using Len. So give me the first 25 characters, and then then the next using Mid function.
thanks!!! you are always so clear!! I couldn't solve a search over a lookup field into a gallery, any idea?
Search only works on text fields not complex ones🤨
Awesome!
Thanks 😊
Hi Shane!, thanks for the video... just a question, how can I have a default selected item using relationship? because I can´t just use a value because is different and as I save it, It doesn't work.. "value must be a data entity record"
Thank you Shane.
You are welcome!
Hi Shane and thanks for this video, very interesting and unveils the mystery of CDS, for me at least. I have a M365 E3 license, don't have a standalone Power Apps license, and I could follow along this video's tasks, but, will it be deleted at some point of time where Microsoft decides it's time enough of CDS testing without a standalone license, whether single apps or unlimited apps? Maybe it's a question for Microsoft directly.
You can play without a license. You just can run the app without a premium license
Hi Shane,
Thanks for these videos in CDS powerapps
Could you please share us few more videos of CDS & PowerApps
Role base management using both
How to create a record/request once an entity/ table created in cds
Limitations, performance etc etc
Thanks for the suggestions 😎
Hey Shane! I have seen lots of your videos and you explain things in a really easy way to understand.
I have two big sharpoint sites using complex workflows that send custom tasks to our users (where they need to fill in custom fields, using content types). Nothing fancy, but users can do the tasks and I can save their responses to a list and act depending on what they filled.
How can I do that with powerapps/power automate?
Create an app that asks for the input you want. Maybe combine deep linking czcams.com/video/A5Pi5P2YKds/video.html to send them to the exact item you want to input?
Shane Young no kidding, I watched your deeplinking video just 2 hrs ago and it gave me that idea.
I am working on my app and I am using galleries (repeating tables) just as your videos showed. I have a sharepoint list that has a choice column that I need in that gallery. How can I connect the drop down control in the gallery to the choice column in my sharepoint data source so it has the same choices?
Great video to get started. One question that really bothers me:
If in this case Chewy trackers has a 1:N relationship with Toy (so you could have multiple toys in one record) can I use patch function like 'The Toy' : toygallery, where toygallery is a gallery that has records of toys in it?
Hmmm I haven’t tried that but something in that ballpark should work. Not sure the exact syntax though.
@@ShanesCows Great to know. The syntax is also a bit odd to me (been coding with C# and I am trying to use syntax from there constantly :D). Thanks for the great tutorials!
Hi, Video is great for learning and when the data is new but has anybody got any guidance on how to relate multiple data tables that already have a unique column created in Excel, other than using Lookup in the Power App so that I can make use of the dot notation?
Hi Shane, In the dropdown you created. The item was 'Chewy Toys'. How did you tune the dropdown to pick the 'toy name" and not something else like 'toy description'
Click on the dropdown, on the right look for Fields. There you can click and it will let you pick which field you want to use.
I'm a bit confused, when you use the word "Record" to describe a field of an Entity (28:46 and so on). What exalcty is the record you are referring on this precise moment?
Records are the row in a table. That is everything about that entry.
@shane, I have noticed the 500/2000 record limit for a Powerapp in Teams.
I have a Prospect Spreadsheet with about 5,000 rows that I would like to expose to a Business Development Team to help curate and enhance the data. Ideally I would do this with a Power App but the record limit seemingly prevents this.
Do you have any suggestions?
David look at this video on Delegation for a deeper explanation. czcams.com/video/UaBmVUQx_Fk/video.html 5,000 items does limit you. Just have to work smart.
hello, Mr. Shane young I am a big fan of your videos. I always prefer to follow your videos when I get a problem in Power Apps. But can you tell the what is the name of the back-end language that we use in Power Apps?
There isn’t a name. It is the Power Apps formula language. 😀
Hi Shane ...Nice Video.Can you please create a video for View/download Note attachment from CDS in Canvas app.
Noted. Thanks
Don't have an "Entities" option under Data. Having a hard time creating a relationship with 2 SharePoint lists. For example, the app is displaying ID number 3 (ThisItem.CUST_ID) instead of pulling the actual name from the 2nd table. It should be accomplished with Filter function right?
Hi shane can you please let me know after loading table structure from sql server can you write back the data using power apps in sql server empty table using model driven power apps ? if so could please post some video on that
Not that I know of. Model driven only works with CDS (dataverse) as far as i know
Hello Shane,
I am facing some problem with the group by with the lookup type columns. any reason you can suggest?
Not sure. My advice is simplify and try to get small pieces working then get more complex.
Great video, with a lot of nice little tricks ;)
But, how would you go around to filter a gallery, to only shows records, that has related (child) records. I.e. only show 'parent records' that has related 'child records' - where the relationship is: parent 1 : m child.
Hmmm. I haven’t done that one before. Sorry
@@ShanesCows no problem 👍
How does CDS know which field/column it has to use to make the relationship between the entities?
Not sure but it does. There is an ID column that CDS (now Dataverse) uses as a primary key under the hood.
Dataflex...thats what i am waiting for...but heard that it will not come anytime before end of this month...😥
I wish I knew when it was coming also. I do think it will change a lot of what we do. 😀
Shane could you please do one video showing how to make one app using two different PowerApps templates? May be it’s little difficult but just wanna see what sort of things we need to take care while doing same.
Hello, It does'nt work for me. I have to filter a gallery with two tables ( and 5 comboboxes).
Great review of CDS relationships. It looks the unique id {guid }is based on SQL. Which I assume thats the backend engine for this.
You are correct 😊
Is it possible to do that with Basic CDS Account ?
Yes. No difference
This didn't work for me at all. I have Table1 that has a many to many relationship to Table2. I wanted to show all of the related image columns on my canvas app where there was a match between Table1 and Table2 only. There is no specific column in Table1 that stores a value. It is only in Table2. And, I only have the relationship to work with in order to get that result but I can't seem to get it. In your example, it seems that you have a specific lookup column in one table that connects to another table. I don't have that.
Shane. I didn't pass my MB-600. (675/700)
Do you have any good resources for this exam. That was one hard test man.
I don’t. And I haven’t taken it. Wha type of questions did you struggle with?
@@ShanesCows they asked about entity security in one question. The rest were just general architecture decisions.
Shane - Have you talked about the Relate and Unrelate functions? Would to see your take on when and how to use them. docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-relate-unrelate
Hey Jamie I haven’t. I h e never had to use them in the real world so you probably know more than me
A paw here and a paw there ... too funny...
Thanks 🐶
MS likes to change the name(CDS,flex,dataverse) every year 🤨😑
Yup, not fun.
Need a premium for dataverse : (
Yup. 😑
Great as always! One question: I have a big report with over 100 columns. I want to have it splitted in different tabels. But then i need a relationship one-to-one (as each record hold data for one test report ). How to solve it in best practice? Any video about that?
Did you manage to get this sorted Daniel? this is something I think I need!
@@djpowerage Not straight forward. I cannot find a way to use one-to-one so I had to use one-to-many. Works OK for me. I just use one column in "main" table as a reference in other tables.