Common Data Service Relationships

[Anonymous]

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

[CWesener]

Hi @Anonymous,

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.

 

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.

[Anonymous]

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?

[CWesener]

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

[Anonymous]

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]

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 

[Anonymous]

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

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.