Common Data Service Relationships and Lookups with Power Apps canvas apps

[@mattrowe2652]

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.

[@ShanesCows]

Thanks Matt. I have never done many to many 😳

[@mattrowe2652]

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

[@facqns]

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

[@ShanesCows]

Glad I could help!

[@notonurnelly]

Great point on Explicit Column Selection this has been catching me out a bit recently, now I fully understand whats happening.

[@ShanesCows]

Awesome Jamie.

[@filipuppsall-sjogren114]

Thanks for a good video into how CDS works... have felt it daunting before but this gave me a good introduction :-)

[@ShanesCows]

Glad to help. 😀

[@shoaibyounus]

Thanks Shane Young. I am watching your videos and they are really helpful :)

[@ShanesCows]

Great to hear!

[@geralddahl9159]

UpdateIf explanation is very helpful to know about. Thank you.

[@geralddahl9159]

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?

[@ShanesCows]

Glad it was helpful!

[@ShanesCows]

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.

[@dudunek]

Very nice Shane. You should make video about many-to-many relationships.

[@ShanesCows]

I have never used them so not sure I am the right person. But thanks for the encouragement 😀

[@barbloke1566]

Great work Shane, my goto Power Platform Guy! 😄

[@ShanesCows]

Awe shucks! Thanks

[@khorjenqyang]

new background looks more clear & better

[@angrybatvoice]

Agreed! Lighting seems better too. Well done Shane!

[@ShanesCows]

Thank you both! Still not perfect but getting better is all I can hope for.

[@marcsavoie7865]

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!

[@ShanesCows]

I love With so much it has its own video :) kzbin.info/www/bejne/d4qnY6KNqspqnNk

[@robvanbreemen6660]

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!

[@ShanesCows]

Rob this video talks about parent/child. kzbin.info/www/bejne/eqrPqaaOfrqsesU Take the concept and yes you can add grand parent :)

[@avinoamrothenberg1244]

Finally great lighting 💡

[@ShanesCows]

Thank you. It has been a real struggle but getting better :)

[@Damski748]

Another great video, thank you!

[@ShanesCows]

Glad you enjoyed it!

[@majahbrooks8158]

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?

[@TiagoRuivo]

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?

[@ShanesCows]

Records are the row in a table. That is everything about that entry.

[@sanwoolutolu4123]

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'

[@ShanesCows]

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.

[@artkarp]

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

[@ShanesCows]

Thanks for sharing 🐶

[@carlosenriquecontrerasespi4266]

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"

[@nirjasmuhammed8512]

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

[@shyamksukumaran]

Thank you Shane.

[@ShanesCows]

You are welcome!

[@helloanurag]

Yay CDS/Dataflex video!!!

[@ShanesCows]

Glad you like it!

[@KingOfBlades27]

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?

[@ShanesCows]

Hmmm I haven’t tried that but something in that ballpark should work. Not sure the exact syntax though.

[@KingOfBlades27]

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

[@jordanc8852]

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?

[@paumason]

thanks!!! you are always so clear!! I couldn't solve a search over a lookup field into a gallery, any idea?

[@ShanesCows]

Search only works on text fields not complex ones🤨

[@johnbratts]

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.

[@ShanesCows]

Try FieldName = Blank() I think that is the way to get around delegation.

[@Fernando_Calero]

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.

[@ShanesCows]

You can play without a license. You just can run the app without a premium license

[@aviksarkar9801]

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?

[@ShanesCows]

There isn’t a name. It is the Power Apps formula language. 😀

[@RobertoBorgesBr]

Awesome!

[@ShanesCows]

Thanks 😊

[@maycondias6903]

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?

[@ShanesCows]

I haven’t but would be fun. I will think about it 😀

[@danielszelagiewicz2262]

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?

[@djpowerage]

Did you manage to get this sorted Daniel? this is something I think I need!

[@danielszelagiewicz2262]

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

[@MrDasarinaveen]

Hello Shane, I am facing some problem with the group by with the lookup type columns. any reason you can suggest?

[@ShanesCows]

Not sure. My advice is simplify and try to get small pieces working then get more complex.

[@leenashreeindrani532]

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

[@ShanesCows]

Thanks for the suggestions 😎

[@eduardocoello1216]

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?

[@ShanesCows]

Create an app that asks for the input you want. Maybe combine deep linking kzbin.info/www/bejne/d2azmmiGZ75-mtU to send them to the exact item you want to input?

[@eduardocoello1216]

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?

[@julien8979]

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.

[@ShanesCows]

I do not have one that specific. I do talk about Dataflows and SharePoint a bit in this video if it helps kzbin.info/www/bejne/eJmvoppnn9iViqs

[@sanjeevtiwari17]

Hi Shane ...Nice Video.Can you please create a video for View/download Note attachment from CDS in Canvas app.

[@ShanesCows]

Noted. Thanks

[@danielhammer8950]

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?

[@ShanesCows]

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

[@artkarp]

CDS has an alternate key field for this use case to store the data. Check the Dynamics 365 docs for info.

[@clientfclientl9147]

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?

[@ashokpershad]

Dataflex...thats what i am waiting for...but heard that it will not come anytime before end of this month...😥

[@ShanesCows]

I wish I knew when it was coming also. I do think it will change a lot of what we do. 😀

[@ashokpershad]

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.

[@stanfda]

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

[@ShanesCows]

David look at this video on Delegation for a deeper explanation. kzbin.info/www/bejne/i5KlnomLht2SfM0 5,000 items does limit you. Just have to work smart.

[@magnusjeppesen]

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.

[@ShanesCows]

Hmmm. I haven’t done that one before. Sorry

[@magnusjeppesen]

@@ShanesCows no problem 👍

[@djpowerage]

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?

[@alexshteinberg1227]

Great review of CDS relationships. It looks the unique id {guid }is based on SQL. Which I assume thats the backend engine for this.

[@ShanesCows]

You are correct 😊

[@gustavoviniciusguimaraesde1638]

Is it possible to do that with Basic CDS Account ?

[@ShanesCows]

Yes. No difference

[@comprascbgolfe9191]

How does CDS know which field/column it has to use to make the relationship between the entities?

[@ShanesCows]

Not sure but it does. There is an ID column that CDS (now Dataverse) uses as a primary key under the hood.

[@MsKaryn]

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.

[@ludovicbocquet9783]

Hello, It does'nt work for me. I have to filter a gallery with two tables ( and 5 comboboxes).

[@Andyandrwew144]

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.

[@ShanesCows]

I don’t. And I haven’t taken it. Wha type of questions did you struggle with?

[@Andyandrwew144]

@@ShanesCows they asked about entity security in one question. The rest were just general architecture decisions.

[@abhisiktasarkar9962]

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

[@ShanesCows]

Not that I know of. Model driven only works with CDS (dataverse) as far as i know

[@richardclowes6166]

A paw here and a paw there ... too funny...

[@ShanesCows]

Thanks 🐶

[@JamieWillisRose]

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

[@ShanesCows]

Hey Jamie I haven’t. I h e never had to use them in the real world so you probably know more than me

[@wendelsilva7055]

Need a premium for dataverse : (

[@ShanesCows]

Yup. 😑

[@pabloroldan6626]

MS likes to change the name(CDS,flex,dataverse) every year 🤨😑

[@ShanesCows]

Yup, not fun.

[@mvos1980]

Thanks again for the great video!

[@ShanesCows]

My pleasure!