Power Automate: best approach to build multiple conditional logics

[Anonymous]

Hello Community,

I have a client requirement to use Power Automate to build a long list of conditional logics, whereby (at a high level):

- An account entity record has 4 categories

- For each account category, I need to check 70 fields if each field meets a certain input criteria (the input criteria for each field is different), and if at least one out of the 70 fields fails the input criteria validation, then flag the record as an error

To implement the above, I am looking at using the "Switch" action in the Power Automate so I can have one switch case branch for each of the 4 account categories. Then, underneath each case branch, I will have a long list of conditions to check through all the 70 fields against their input criteria. As you could imagine while approach would work, it's quite time consuming and tedious.

I wonder if there might be a better way of handling this in the Power Automate. Are there better Flow actions I could use to help simplify this process, and make the overall logics shorter to set up?

Any ideas or suggestions would be appreciated!

Thank you!

[PowerBack19]

@Anonymous  - I wouldn't recommend using the Switch for this as there is a limit of 27 cases under the Switch. https://powerusers.microsoft.com/t5/General-Power-Automate/Switch-max-cases/td-p/207850

If you are using a Model-Drive Power App or Dynamics 365 for your Account records, then I would recommend using the FetchXml builder within the XrmToolBox. Once the tedious conditions are built, then you can use the FetchXml output within the "Common Data Service (current environment)" connector. Keep in mind this connector can only be created from within a Solution file.

The "List Records" action under the CDS (ce) connector allows you to input FetchXml with dynamic inputs from prior Power Automate steps. Then if any of the records met the criteria, you can do an "Update Record" action to flag the account record.

[Anonymous]

Hi @PowerBack19 

Thank you very much for your reply and insights!

If I may pick your brain a little further since there are added complexities to my original requirement.

So, what I have now is the followings (at a high level):

- In Dynamics 365, I have a custom entity called "Validation Rule" where each record represents a rule. Each rule is for a specific field, and for a specific code, from the Account entity.

- Whenever the account record is updated, I want to trigger the Power Automate and apply the validation rule, referencing the Validation Rule entity.

- My challenge is: how can I define the steps/actions in the Power Automate so that it understands which Validation Rule record to use for the comparison. For example, the account that's updated could be of code "A", "B", or "C", and the I need to run the validation on field "a", "b", and "c" on the account. Under the Validation Rule entity, I have the following list of records:

- Record 1: Field = a, Code = A, Min Value = 0, Max Value = 100

- Record 2: Field = a, Code = B, Min Value = 0, Max Value = 80

- Record 3: Field = a, Code = C, Min Value = 50, Max Value = 200

- Record 4: Field = b, Code = A, Min Value = 0, Max Value = 75

- Record 5: Field = b, Code = B, Min Value = 0, Max Value = 50

- etc.

So basically, what I am checking is if the account record is of Code = A, then I would need to apply the validation rules that are relevant for Code = A to check that the field values entered are between the min/max values range.

Any suggestions how I could best implement this. I am hoping for an approach where I could avoid to deal with any codes.

Any further pointers would be greatly appreciated!

Thank you!

[PowerBack19]

@Anonymous - Have you thought of a different approach such as using Business Rules for each field you need to check on the Account entity? For example: Have conditions that check if the Account "Code" equals "A" and the Field value is Greater Than 0 and Less Than 100. If so, then it's good. If not, then you can have a message displayed to the user as they are filling out the Account form.

The problem with using Power Automate is that you need to have the trigger look if the Code value is updated and if any of the 70+ fields have been updated. I don't know if you can put that many fields in the trigger. Also, the flow won't run until after the Account record is saved. With the Business Rule, it is happening as soon as the field is updated prior to saving. This allows for a smoother entry process.

Check out this doc on Business Rules: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-create-business-rule 

[Anonymous]

Hi @PowerBack19 ,

Thank you again for your insights. It's more complicated than that.

First of all, the user will be filling the form via the Power Apps Portal, and the requirement is to trigger the checks only after the record is submitted, that's why I am not going with business rules, and business rules are also not supported on the portal. I don't need to trigger this Flow everytime one of the 70 fields are updated. I only need to trigger this Flow when the status of the record is updated to Submitted.

Then, after the Flow is triggered, I need to check the value of the "Code" on the record (there are 4 different codes: C, R, S, T). For each code, the 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.

Also, the requirement says if a field fails the check, I need to update a multi-line text field on the record with some log message, and the message is unique for each field that fails the check. Also, if any of the fields fails the check, I need to flag a checkbox field on the record. 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 multi-line text log field 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 update the multi-line log field, 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 rules inside the Flow. 

So, I was thinking to create a custom entity in Dynamics 365 to store the 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 can use the "List Records" action in the Flow to fetch the 284 rules, 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.

Would you have any further insights on how I could achieve this in a more dynamic efficient manner? Sorry for the long description, but I hope this helps explain the requirement I am trying to solve.

Thank you very much!