Replicating vlookup with CDS entities

[Ifrit]

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

[HSheild]

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.

[Ifrit]

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

[HSheild]

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.

[Ifrit]

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]

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

[Ifrit]

@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

[jlindstrom]

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