cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Medoomi
Resolver I
Resolver I

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
EricRegnier
Super User II
Super User II

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
Drrickryp
Super User II
Super User II

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. 

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

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

timl
Super User III
Super User III

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 

EricRegnier
Super User II
Super User II

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (91,700)