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

Power Automate: How to compare field value of one Dynamics 365 entity to the schema name of another Dynamics 365 entity

Hello Community,

 

I have a requirement whereby I would like to use Power Automate to compare field value of one Dynamics 365 entity to the schema name of another Dynamics 365 entity. Is this possible?

 

For example, on entity A I have a field called "Validation Field" (a text field). The value that is entered in this field is each field schema name from entity B. So imagine I have the list of entity A records returned from the "List Record" action in my Flow, I want to check a specific entity B record against the list of entity A records to see if the Validation Field value from the entity A records contains the specific field schema name from entity A.

 

How can I do such comparison in the Flow? Usually in the Flow we are comparing field value to field value. I don't know how to compare field value to the schema name of another field.

 

Any pointers would be appreciated.

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @D365_Eric 

 

Your HTTP request will return all the field names (Schemas) in JSON format. You need to add a Parse JSON step and compare. What is that List All Validation Entity Records returns from the CDS db?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials

View solution in original post

4 REPLIES 4
Super User III
Super User III

Hi @D365_Eric 

 

You can query the CDS meta data using API

 

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/webapi/query-metadata-web-a...

 

Please see below steps.

 

image.png

 

 

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/webapi/retrieve-metadata-na...

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials

Hi @abm ,

 

Thank you very much for your reply and pointers!

 

So using the "Invoke an HTTP request" action in my Flow, I am able to retrieve all the attributes (i.e. fields and their field logical name) from my desired entity "Annual Statistics" (see my screen shot below). Now that I have done this, what would I need to set up in order to compare a field value from the entity "Validation" against the list of field logical names from the "Annual Statistics" entity from the API request?

 

For example, in the Condition I have circled below, I would like to check if the field value on the left side called "Validation Field" (which is from the Validation entity) is equal to one of the field attribute logical name from the entity that I retrieve the field attributes from in the API call from the HTTP request. This conditional check fails of course, because I cannot simply use the "Body" output for this condition. I am not sure how I could make use of the output from the HTTP API request to achieve this comparison in my conditional statement.

 

I would appreciate some further advice. Thank you!

 
 

Flow.jpg

 

 

Hi @D365_Eric 

 

Your HTTP request will return all the field names (Schemas) in JSON format. You need to add a Parse JSON step and compare. What is that List All Validation Entity Records returns from the CDS db?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials

View solution in original post

Hi @abm ,

 

Thank you again for your reply!

 

If you don't mind, let me describe in details below what I am trying to achieve, so that perhaps you could see what my gaps are and would certainly appreciate your additional insights.

 

At a high level, we have an entity in Dynamics 365, and the user will be filling the entity form via the Power Apps Portal, and the requirement is to verify a set of 70 fields on the record against the respective field validation rule, only after the record is submitted. I don't need to trigger this check everytime one of the 70 fields gets updated. I only need to trigger this check when the status of the record is updated to Submitted.

 

I am looking to use Power Automate for this solution. I am not a developer that writes codes, so hoping to stay away from custom codes (i.e. Azure Functions, etc.).

 

So, when the Flow is triggered, I need to check the value of a "Code" field on the record (there are 4 different codes: C, R, S, T). For each code, the validation min/max value range for the 70 fields is different. So if the code = C, I need to check the 70 fields on the record against the rules, and if the code = R, I need to check the 70 fields on the record against another set of different rules, and so on. The validation is basically to check if the value entered in the field is within the min/max value range defined in the validation rule for that particular field. Again, depending on the "code", the min/max value range varies for each of the 70 fields. So in total we have 284 combinations.

 

Also, the requirement says if a field fails the check, I need to flag a checkbox field on the record, and also create an error log via another custom entity. The whole idea is that after this Flow is done processing, the user in Dynamics can easily go to the dashboard to review any records having the checkbox field flagged as having a problem so the user can review the record, and the user can look at the error logs on the related entity subgrid on the record to know which of the 70 fields to examine the data. That's what we are trying to achieve for the client.

 

I could build out this big Flow logics using one Condition check action for each of the field, but I will end up needing to have 284 such Condition checks, because if each one fails, I need to flag the checkbox and create the error log record, so I need individual Condition checks. But this would be massive, very time consuming to build. Also, the client would like to be able to easily update the rules in the future, so it's not good to define the actual min/max values inside the Flow. 

 

So, I was thinking to create a custom entity in Dynamics 365 to store the validation rules. So I will have 284 records under this custom entity, each one representing a rule for each field (4 codes x 70 fields = 284 rules). I am calling this custom entity "Validation Rule Setting".

 

I can use the "List Records" action in the Flow to fetch the 284 validation rule records, but I am having problems coming up with the logics to dynamically pass the record I am checking in the Flow against the unique one rule from the list of 284. Ideally, I like to be able to use some kind of looping where the Flow would understand and be smart enough to examine the 70 fields from the records against the respective 70 rules from my custom entity list, based on what the Code is.

 

As an example, one of the 70 fields I need to check is "new_numberofschools". The validation rule record contains a text field which stores the schema name of the field, and a set of whole number fields to store the min/max values. So, I need to be able to somehow tell the Flow to pick out which rule to use for the field "new_numberofschools" to that I can do the min/max comparison. But I am not sure how to implement this, and to do it with an efficient logics.

 

Would greatly appreciate some guidance.

 

Thank you!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Top Kudoed Authors
Users online (7,020)