Testing quickly if Dataverse fields are empty

 Here is a quick hack, to test, if specific Dataverse fields of a record are empty (or null). 

The normal way would be to insert a "is equal to null" condition into your flow for each field you'd like to check. That's too much work!

The following method is safer, quicker to write and performs much faster on top of that!

You will need four actions: 

• a "Get Row by ID" (Dataverse) action that loads the Dataverse record to be tested

• a compose that holds an array of field names. You can add as many fields as you like here, but they should be the logical field names.

• a filter array to iterate over this array and perform the "is-null" checks. Note: Normally filter array is performed on an array of data elements, for example an array of Dataverse records. This is NOT the case here. We stay in one record and iterate over some of its fields instead.

• a condition that counts the number of "is-null" fields found by the filter array.

Array of field names (Compose)

Here is an example of what this array could look like:

["abc_fullname",

  "abc_birthday",

  "abc_department,

  "abc_supervisor"]

Filter array

From:     outputs('Compose')

Map: 

empty(string(coalesce(outputs('Get_Row_by_ID')?['body']?[item()],'')))

"is equal to"

true

Let me quickly explain the mapping function. Remember that item() acts as an iterator function, applied to all the array elements loaded into the "From:" field. 

The first element is "abc_fullname", which is a field name and it is used as such to load the value of a record field with that field name:

outputs('Get_Row_by_ID')?['body']?[item()]

becomes

outputs('Get_Row_by_ID')?['body']?['abc_fullname']

The stored value of this field could be a variety of things, a string, a number, a lookup, null. Since all we are about here, is, whether it is empty, we can use the function empty(). 

However, empty expects a string as input, so we need to cast the field value as a string, using string(). 

string() will produce an error though, if the field value is null, so we also use coalesce(), one of my favorite functions in Power Automate, to make sure the value is at least an empty string.

Condition

length(body('Filter_array'))

"is equal to"

0

Insert your business logic in the "If yes" branch (no fields are empty or null) and in the "If no" branch (some fields are empty or null). 

Asynchronous Flow Design - Effient, Lean, Robust and Testable!

Power Automate beginners are often tempted to pack a whole process into a single flow. The advantages seem obvious: less clutter, easy to keep an overview over all runs, etc. However, flows quickly become very complicated and fragile. 

I'd like to propose an approach that is inspired from asynchronous software design: Chop up a process into stages and handle each stage with an asynchronous child flow. 

Each flow, if completed successfully, will then "hand-over" to the next child flow. This approach works very well for automation processes on a Dataverse records (however, the same approach could also be used for SharePoint lists).

Pre-Requesites

• A column carrying a "WorkflowStatus" with a Choice value for each of the stages of the automation process,
  examples: "new", "stored", "checked", "confirmed", "rejected", "approved", "completed"
  
  
• A child flow for each of the stages.
  examples: "NewEntry", "StoreEntry", "CheckEntry", "ConfirmationApproval", "RejectEntry", "ApproveEntry", "FinalizeEntry"

Asynchronous Child Flow design (Best Practice)

Each Child Flow architect follows the following pattern: 

1. Trigger Action = "Manually trigger a flow".
   Argument: GUID (of the Dataverse record)
   
   
2. Action "Respond to Power Apps or flow". No return argument.
   This will release the calling child flow right away ("Hey, I got it now, you can go have a rest.")
   
   
3. "Get a row by ID (Dataverse)" (using the GUID)
   
   
4. Assert that the WorkflowStatus is allowed to be processed by the current child flow (=the current stage of the process).
   If not, terminate with message to support.
   
   
5. Further assertions on values stored in the record.
   Assertions, generally speaking, are expected states, which you don't actually expect to fail. But it's an excellent defensive programming practice to assure that they do meet your expectations. Not only will it make testing easier, assertions will act as built-in sanity checks for future updates to the flow.
   If any of these assertions fail, terminate with a message to support.
   
   
6. Only now, set the WorkflowStatus to the name of the current stage. Why so late? Only now, the flow is ready to perform it's core duties - everything before was just prep.
   
   
7. Perform all operative steps of the current process stage.
   In case of errors, terminate with message to support.
   Some of the actions may build upon each other. In this case, errors should cause an early termination with message to support.
   
   
8. Launch next child flow in the chain. Even out-of-sequence calls are possible here (#4 would safeguard against illegal out-of-sequence calls).

Robust Error Handling

Create a separate child flow "Support Case".

Trigger Input #1: GUID (just like above)

Trigger Input #2: "pre or post". The calling child flow can specify whether it failed pre- or post-operation. This will define the point of reentry to the process chain (after the support case is resolved).

Inside "Support Case" create an approval to the Support Worker, which gives them the possibility to manually adjust the record, check run histories, etc. and then click on "Approve" to set the record as resolved. Based on the the "pre- or post-operation" trigger option, it will re-trigger the calling flow ("pre") or trigger then next flow in the process chain ("post").

Error collection is done in each of the child flows belonging to the process chain. An easy way of doing this is to initialize a string array (e.g. "errors") and then append a message to it whenever something goes wrong. Between each of the major steps above in "Child Flow Design", test if the length of the errors array > 0, if yes, launch a "Support Case". 

In order to pass the messages from the errors array, you can either pass them via a child flow trigger argument, or, even better, write it to a special "Errors" column of the Dataverse record. The second option is more persistent, and in general it's a good idea to keep the interface lean between flows (= keep the child flow trigger arguments to an absolute minimum), usually you just need the record GUID. 

Advantages of Asynchronous Flow Design

• Flexibility: out-of-sequence calling is possible without becoming a programming nightmare
  
  
• Manual reactivation after failture: Should your flow fail and you manage to find the reason for it: Fix the flow (and/or dataverse record), then simply run that flow manually with the GUID that caused the failure. It will then continue its asynchronous journey, as if nothing had happened. 
  
  
• Efficiency: Each flow only completes a part of the process and then hands-over to the next flow
  
  
• Timeout robustness: Remember, each flow can only be running for 30 days, so if you try to chain multiple approvals together, it's very likely, you will sometimes hit this limit. However, if only one approval is allowed per flow, you can very easily extend timeout limits to much much longer process durations. In theory: 30 days x the number of the flows in the process chain.
  
  
• Testability: It's very nice to be able to test each stage individually without having to retrigger the whole chain of flows!
  
  

I hope this approach is something that will benefit you as well in your next project! Good luck.