cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ifrit
Advocate IV
Advocate IV

Replicating vlookup with CDS entities

Hi, 

I'm starting with with CDS and model-driven apps and decided to try on recreating a simple scenario.

I have an Excel report that is build by calculating fields from multiple sheets using vlookups, so I thought I would re-create each sheet as new entity in CDS and then have a "master" entity with calculated fields that would show data from others base on formula similar to vlookup. And that's where I'm failing...

 

Could somebody please help me jump start my brain and point me in the right direction or to a good source?

Any assistance is highly appreciated

7 REPLIES 7
HSheild
Super User
Super User

Hi @Ifrit ,

 

In CDS we use relationships to join entities (entity = Excel table) instead of using vlookups.  Basically, entities can have a reference (lookup) to other entities that it is related to.  This could be quite a learning curve if you are not familiar with databases and relationships but here is some documentation to get you started.

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/create-edit-entity-relationship...

 

Have a read through all the pages in the Work with entity relationships section and then come back if you have any further questions.

Thank you for the quick response.

 

I have read all the articles and understand it on base level but I think I'm still not able to replicate my scenario.

 

What I think would help me is specific example to unstuck my brain.

 

let see I have 2 entities, Entity A has ID field and Entity B has ID and Country field.
How would you create calculated text field that would show country if ID of A and B match?

 

Thank you for all the help

Thanks for the extra detail. That helps.

Sounds like you want to show the country field from entity B on the form of entity A. For entity A you need to create a many to one relationship with entity B. That will give you a lookup field on entity A which points to entity B. Put that field on your entity A form. You can then set the lookup field on entity A to the entity B record where they have the same IDs in you spreadsheet. You now have a relationship created between the 2 records.

If entity B represents a country then you will want the country name text to be the primary field of entity B. This will then show the country name in the lookup field on entity A.

If you have other fields on entity B that you want to show on the entity A form then you can create a quick view form for entity B and show that on the form of entity A.

https://docs.microsoft.com/en-us/powerapps/maker/model-driven-apps/create-edit-quick-view-forms

I hope this makes sense.

This is a bit of a mindset shift for you. You are not trying to calculate fields on entity A, in CDS you are actually referencing entity B and showing the details of entity B on the entity A form.

Thank you, 

I managed what you described working before but it isn't exactly.

 

I understand the concept but what if I don't care about form input. I populate entity with records from another datasource and the same for IDs of Entity A. How do I auto-populate the lookup with the right value since I can't calculate it?

 

Thank you

 

 

CliveH
Regular Visitor

@Ifrit ,

 

Did you ever get a workable solution to this question?  I am trying to do the same thing but haven't been able to find a good solution?

 

Thanks.

@CliveH 
not really, it's just not a way CDS works. I ended up using CDS as source for the data and do the calculations in Power BI.

BR
Jan

So in your scenario you have a country on one entity and if you have that Record id on another record via a relationship, you want to display the value of the country from record a on record b

 

possible solutions include using a quick view form to display parent record fields on child—that’s how we show account fields on child contacts

 

also calculated fields can use values from the related record

 

and views can display fields from related records 

 

If the records are not directly related, use power automate to copy the value of you aren’t concerned about the data being in different entities 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,627)