cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SamPo
Level 8

Common Data Service Relationships

Hi,

 

I've just begun working with CDS for apps and having trouble understanding the relationships feature.

 

1) I have an entity called 'Employees' and another called 'IT Tasks'. I need a lookup field on 'IT Tasks' so each task is related to a specific employee. I've created a default relationship between the 2 tables (and it works), yet when I add the lookup field to a form on Powerapps it shows every value in the dropdown as 'systemusers'. When I go to look at the actual data in the table, the lookup field shows the value of 'primary name' (in this case the employee fullname). How can I change the lookup field in the app to show the primary name as well?

 

2) Ideally I would like to use an external ID for the relationship called 'Employee ID'. This is a unique value that comes from our HR system. Both the employee details and tasks records are created by flow when an incoming HTTP request is received from the HR system and I would like to simply insert the employee ID into both table records via flow and have that be the key for this relationship. Would apprecieate if someone could explain exactly how I can acheive this.

 

Thanks,

Sam

7 REPLIES 7
PowerApps Staff CWesener
PowerApps Staff

Re: Common Data Service Relationships

 

Hi @SamPo,

 

For 1) you can set the Dispaly value on the lookup control in studio to pick the Primary name from your related entity - it picks a field by default, which is often unhelpful.

 

 LookupDisplayField2.gif

 

2) You can add a new key to your entity by opening it in the Portal (Data > Entities > Your Entity) clicking keys, and adding a new key and then select the field you want to use. This will enforce uniqueness on the field, and you can then use it to search to find records from Flow to perform updates.

 

Hope this helps,

Clay.

Thanks,
Clay.
SamPo
Level 8

Re: Common Data Service Relationships

Thanks yes that does help a lot.

 

Regarding the Relationships: Using the key field is helpful when say when I'm running a flow and searching for related records but it still does not create an actual relationship in the data in the same way a foreign key does (or at least I can't work out how to do it)

 

Essentially I want to be able to run a query in powerapps and use something like 'IT_Tasks.Employees.FirstName'. I know this is possible using a standard lookup field but I can't figure out how to make the lookup use my custom relationship rather than the GUID of the related record - is this possible?

PowerApps Staff CWesener
PowerApps Staff

Re: Common Data Service Relationships

Thanks @SamPo - it would help if you could explian to me why you want it to use something other than the GUID? If you can use other fields in your filter expression, and in the lookup, etc - can you let me know what you're trying to do that the fact that the underlying FK is a GUID is preventing you to do? Is it that you need to do a lookup on the related table first using the natural key, to get the GUID in the first place? Rather than be able to use the natural key in in a single operation?

 

Not implying there isnt an issue here, just trying to get my head around it.

Thanks,
Clay.
SamPo
Level 8

Re: Common Data Service Relationships

Hi,

 

Yes, you are correct that I can always use an expression to filter out the record I need based on the Employee ID but as you say I would like to be able to use the natural key (the employee ID in this case) in one single operation inside my apps.

 

I'm building a number of apps for our global HR team, based on about 6 different entities in CDS, each of which contains an 'Employee ID' column and it would make things far easier if I could define this column as my FK for all tables (I have one employee details table which would serve as the primary table for all the others). It makes sense to me that if I'm able to define a relationship key then this key should be able to serve as my FK for other entities.

 

Sam

Sarang24Dev
Level: Powered On

Re: Common Data Service Relationships

Has anyone repsonded back to your question? i am facing a similar challenge.  Lots of tables with a common Fk and I need to build a dashboard out of these tables by looking up column in other tabels.  Basically, a query that I would have build in an MS Access 

SamPo
Level 8

Re: Common Data Service Relationships

@Sarang24Dev No, I met with a couple of engineers from the Microsoft Premier team who are supposed to be getting back to me on this question. Will update here if I get anything solid from them.

AbeSaldana
Level: Powered On

Re: Common Data Service Relationships

it seems that you have some good details on relationships with CDS, I have created a blog post and a video that will give you more details on how you can use them to get to other (FK) related entities with the same connection to the Data Source

here is the link for the Blog post and inside you will see the link for the YouTube Video.

https://abesaldana.com/canvas-and-cds-relationships/ 

 

hope this helps 

 

abe.

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 393 members 4,877 guests
Recent signins:
Please welcome our newest community members: