If you are reading this, you likely ran into an issue where you created an approval flow, but it expired before the recipient had time to approve or reject it. The timeout for an approval or any flow is thirty days; then, it stops running. Yes, there are some clever workaround to alert if the flow times out, but who wants to mess with that?
The approach I took to solve this was to leverage some of the existing tooling, then add to them. When you create an approval, a row is created in the dataverse Approval table. As we all know, a flow is trigger-based, so why not create one that simply monitors the Approval table, then handles things from there?
At a high level, here is the basic approach.
Start by creating a simple flow that initiates an approval, then run it. In my example, note the value in the Item Link field; this will come into play later.
Next, navigate to make.powerapps.com, expand the Dataverse section, and click on Tables. After the page loads, click the All link under Tables, then search for approval. If you search for approval and do not get a result, make sure you click the All link.
Open the Approval table; in it, you will see your approval, possibly more depending on how old your environment is or if many people in your company are using approvals. When looking at the data, the takeaway is what is stored in the table and what can be used in the flow that handles the outcome of the approval. In my case, using the Item Link field is key to handling the approval response. With it, I can filter the value and know if I need to take action on the item or not.
When creating the flow that responds to the approval, you can filter it at the design level or in the trigger settings. I went with the trigger setting due to the number of approvals that could be firing across my organization in our default tenant. Why do you need to filter it? Just assume other approvals might be writing to the same dataverse table.
The above conditions filter the value I passed in the create approval flow (Item Link) and if the item has been approved or rejected.
Here is an overview of the flow that handles the outcome of the approval. I mixed dataverse connector types due to an issue with the trigger condition not working with the green dataverse connector. In the Expand Query field, I used the Fetch XML builder to query over to the Approval Response table to get the comment field; not used in the example, but nonetheless, it’s there. From the Get a row by ID action, the response of the approval is available to use to handle the outcome (Result) of the approval.
To my knowledge, there is no reason why you can’t create an approval that is active for months, if not years.
Notes: 1) You can access and review the approval records using PowerBI, Flow, Access, ___ 2) You can bulks update the records using PowerShell, Flow, Access (be real careful), __ 3) You can pass items in the Details field, then parse them out when handling the approval. Here is one simple example where I’m passing a SharePoint item ID from the approval and parsing it in the response flow:
Response flow compose statement that parses the Details field.
Over the years, some updates to Flow have been better than others, and others, not so much. If memory serves, the send an email action would use dynamic hyperlinks without much work, but something went sideways with one of the updates causing dynamic hyperlinks not to work as you’d want.
Here is a basic example of including a hyperlink in an outgoing email; further down the page, I’ll provide a more realistitc example.
In this example, I setup the Flow to trigger when an item is added to a SharePoint library. The key thing to note in this example is the value of the varHyerplink variable; note the double quotes around the link to item.
Below are all the steps required to add your company holidays to your Outlook calendar. This is a simple approach that can easily be updated to pull the holidays and dates from other data sources like Exel, SharePoint, Dataverse…
Overview of the flow that will be created:
All of the holidays (events) will be stored in an array using a compose action.
After that, a parse JSON event will be used to make the events available in the apply to each action. Note: after the parse JSON action is added to the flow, click the generate from sample button and paste in the holidays JSON from the step above.
Next, add an apply to each action to the flow. In the action, the output box will hold the body of the parse JSON action.
The convert time zone action is used to standardize the date format. For the base time use the Date field from the parse JSON action.
The last item that needs to be added is the outlook create event action. Note: for the End time property a formula is used: addDays(body(‘Convert_time_zone’),1)
Unable to process template language expressions for action 'Apply_to_each_sftp_file' at line '1' and column '30517': 'The template language expression 'body('List_files_in_folder')?['body']' cannot be evaluated because property 'body' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.
From one day to the next one of my flows stopped working with the error above. The flow is super simple: Get files from an SFTP folder Loop through the files Copy file to another location Delete file
Looking at the apply to each step I noticed body element was referenced. This might have been related to how I set up the loop or the autogenerated action.
Action the flow was unhappy with:
Updated the flow and it started working:
Note how the ?[‘body’] element is missing from the second picture.
Fix? Created a new Apply to each loop by first setting a variable to the name of the file I’m looping on, then added my other actions. There might be a better way to fix this, but for now, this is what works.
I was trying to use a SQL Insert Row action to insert a new row in a SQL Server table and received a Bad Gateway error. First, I thought it was a permissions issue, then I thought my Flow stopped working…
Turned out to be an issue with the amount of data being inserted into a field. One SQL column was set to varchar(X) and the Flow was trying to insert more characters than X.
On the surface, this request sounded super simple and straightforward. “we need to copy files from a SharePoint library to Blob storage.” Simple enough? Well, yes, but the SharePoint library has a couple of required fields and a Flow is triggered by an action.
Consider what I’m outlining below to be version ONE of the process. In the near future, I will update this post with a slightly more resilient solution.
My SharePoint library has a required field titled DesinationFolder
Context of what I’m doing in the Flow: Trigger: When files is created in a folder When a file is added to a library the flow is triggered Get file metadata File Identifier: Use File identifier from the step above Get file properties Id: Use the ItemId from the previous step Initialize variable Name: vCheckedOut Type: Boolean Value: Checked out (field from Get properties) Initialize variable Name: vFolderPath Type: String Value: Condition vCheckedOut is equal to true Yes: Do until vCheckout is equal to False GetFileProperties Set variable Name: vCheckedOut Value: Checked out (value from the Get file properties above) No: Set variable Name: vFolderPath Value: FolderPath (SharePoint field)