CDS Entities - Is it possible to create and run across different CDS entities?

[Sarang24Dev]

I have created a few entities with some common columns.  I want to create query using the data in entities and present the data output of query in Model-driven apps. Basically, I am trying to achieve statements like Inner Join in MS Access on CDS entities.  Is it possible to do this using CDS?

[v-monli-msft]

Hi @Sarang24Dev,

My understanding is that you want to combine records from two entities when  there are matching values in a field. You can achieve this and save this combined table as a collection, but you cannot save this collection to a new entity unless the entity is already created and has exactly the same fields.

You can use the functions AddColumns and LookUp to perform joins in PowerApps. You could refer to below thread for more detailed information:

https://powerusers.microsoft.com/t5/General-Discussion/Syntax-for-joining-tables/td-p/61387

Regards,

Mona

[Sarang24Dev]

Thank you for the quick response, Mona.  Joining tables in a local collection can be achieved only in canvas apps, correct?

I was looking for ways to do this in Model-driven app so that I can present the joined table as a dashboard to the users.  Can you confirm if its possible to do this in model-driven app? 

[AbeSaldana · Accepted solution]

 Sarang24Dev

You can have many relationships on CDS and Dyanmics 365 CE, these are 1 to Many, Many to 1 and Many to Many, and they are related to Lookup Field in the Entities,  we already have some examples on how this is related,

Account to Contact relationships (1 to Many)

Account is related to the Company in Contacts, 

Contact that is the Primary Contact for the Account (Many to 1) 

SystemUserRoles is the Many to Many with SystemUsers (Users) and Roles

you just need to understand how the relationship is created using the lookup fields in CDS  I think this is a good topic for a blog post and video that I can do in the near future. 

Hope this helps and this is my blog, where you will find some good post and videos related to CDS and Model Driven. 

https://abesaldana.com/blog/

Abe.

[Sarang24Dev]

Thank you, Abe. Hopefully I will be able to get it working.

[anjuriprasad]

Hi Sarang,
Will u able to achieve joining the tables and display output rows in model driven app