In this #PowerShot, I will show you how to save the approver from an approval request to a record in Dataverse where the field is a lookup to the User table. The approver details from the approval action do not return the GUID that can be used to map to the user record in Dataverse. In this post, we will take a look at how we can filter the User table and get the User GUID that can be used on the lookup field.
Power Automate
Below is a simple flow that gets triggered when a new rows is added to Dataverse and sends out an approval request.
Step 1: Trigger - When a row is added, modified or deleted in Dataverse. For this flow, I have selected the change type as added. The flow can also be triggered when a row is modified or deleted. However, when the row is deleted, the flow will not be able to update the same record on the same table as the record is deleted.
Step 2: Action - Compose. I have added the compose action to create a hyperlink to the record on a Model Driven App so that the approver can view it.
Step 3: Action - Start and wait for an approval. I have selected the approval type as first to respond. In cases where it is required that the approval response should wait for all the approvers, the wait for all responses can also be selected. I have hard coded the approver on this action for test. However, approver email address can also be dynamically passed here.
Step 4: Action - Condition. In the left box of the condition action, select the Outcome value from the dynamic selector. The operator is "is equal to" and the value to compare is "Approve"
Condition "Yes" branch
Step 5: Action - List Rows from Dataverse. Select the "Users" table and in the filter rows, use the expression as:
internalemailaddress eq '@first(outputs('Start_and_wait_for_an_approval')?['body/responses'])?['responder/email']'
This query is to filter the users table and get the row matching the internal email address from the output of the approval action.
The expression "first(outputs('Start_and_wait_for_an_approval')?['body/responses'])?['responder/email']" will get the first responder from the approval action outputs and select their email address that is needed in the filter query.
Step 6: Action - Compose. I have added the compose action here to show the expression that can be passed on directly to the Dataverse action.
/systemusers(@first(outputs('List_rows')?['body/value'])?['systemuserid'])
This is the format that needs to be used when passing the lookup value for a lookup type column on dataverse. The expression "first(outputs('List_rows')?['body/value'])?['systemuserid']" will get the first row from the outputs of the list rows action and select the GUID of the user record.
The compose outputs can then be directly passed to the Update a Row in Dataverse action to the corresponding lookup column.
In this post, we saw how we can use the email address of a user to filter the User table in Dataverse, extract the GUID and update a lookup value using Power Automate.
I hope you found this interesting and it helped you. Thank you for reading.