cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

CDS entity calculated field - populate with lookup data?

I have three entities: "Operating Unit Manager", "Operating Unit", and "Projects"

"Operating Unit Manager" contains "Full Name" of the current managers.

"Operating Unit" contains "OU Name", and a lookup field "OU Manager" (drawing from the entity "Operating Unit Manager")

"Projects" contains a lookup field "Operating Unit" (drawing from the entity "Operating Unit").

 

I would like to create an additional field ("OU Manager" in "Projects", which, when an Operating Unit is selected, would automatically get populated with the relevant Operating Unit Manager. However, I need this to persist across screens and be used within various formulas, so my preferred method would be to create a calculated field within the CDS entity. Is there a formula that I could use in the Projects Entity (as a calculated field in that entity) which would allow me to lookup & enter the relevant manager, based on the selected Operating Unit?

 

If not, what would be the best way to accomplish this?

thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: CDS entity calculated field - populate with lookup data?

Hi @Medoomi,

Unfortunately calculated fields only support text and numeric data types. What you are looking to set seems to be a lookup field (OU Manager) which is a reference to a user record. You would be able to automatically set the OU Manager with Power Automate or out-of-the-box classic workflows is it requires to be synchronous otherwise I would go with Power Automate. In short, steps would be

  1. Create a new flow in Power Automate from your CDS solution that triggers on create or update of Project entity. Use the "Common Data Service (current environment)" connector.
  2. Add your conditions and expressions to determine the OU Manager
  3. Update the record with OU Manager

Hope this helps and makes sense...

View solution in original post

5 REPLIES 5
Highlighted
Super User
Super User

Re: CDS entity calculated field - populate with lookup data?

Hi @Medoomi 

If you have correctly assigned the relationships between your tables, you should be able to follow the example in https://powerapps.microsoft.com/en-us/blog/northwind-traders-relational-data-sample/  to accomplish your goals. If you haven't downloaded it, there is an exercise in how to build the database from scratch and along the way, you should be able to find the answer to your question. 

Highlighted
Advocate II
Advocate II

Re: CDS entity calculated field - populate with lookup data?

Hmm, I probably should have posted in "Common Data Service for Apps." That slipped my mind.

@Drrickryp

Maybe to clarify, I wondered what kind of formula could work in CDS, within an Entity, as a calculated field (whether there was any Lookup functionality within Entity calculated fields)...

Highlighted
Super User
Super User

Re: CDS entity calculated field - populate with lookup data?

@Medoomi 

I have found that calculated fields within an entity are pretty limited to mostly simple numeric functions and concatenation of text fields although I have to admit I haven't really used them for other purposes.  This limits what can be done in Model driven apps.  There is much more flexibility because the number of functions available in Canvas apps is so much greater. I tend to do my calculations there.   For an example of how limiting calculated fields are, I posted one on how to calculate age for an MDA I developed and it seemed pretty awkward to me.  https://powerusers.microsoft.com/t5/Common-Data-Service-for-Apps/Calculating-age-in-CDS-entity/td-p/... 

Highlighted
Super User
Super User

Re: CDS entity calculated field - populate with lookup data?

Hi @Medoomi 

You may also want to consider whether or not you can accomplish what you need by creating a view in your projects entity, rather than creating a calculated field.

The help page demonstrates how a column in view can span a 1:N relationship.

https://docs.microsoft.com/en-us/powerapps/maker/model-driven-apps/choose-and-configure-columns 

Highlighted
Solution Sage
Solution Sage

Re: CDS entity calculated field - populate with lookup data?

Hi @Medoomi,

Unfortunately calculated fields only support text and numeric data types. What you are looking to set seems to be a lookup field (OU Manager) which is a reference to a user record. You would be able to automatically set the OU Manager with Power Automate or out-of-the-box classic workflows is it requires to be synchronous otherwise I would go with Power Automate. In short, steps would be

  1. Create a new flow in Power Automate from your CDS solution that triggers on create or update of Project entity. Use the "Common Data Service (current environment)" connector.
  2. Add your conditions and expressions to determine the OU Manager
  3. Update the record with OU Manager

Hope this helps and makes sense...

View solution in original post

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (11,137)