I recently set out to create a “live” conference room schedule that could be presented constantly on an auto-refreshed screen outside conference rooms. This would replace printed schedules placed in holders outside the rooms. The following example uses a SharePoint calendar as the conference room calendar and can be refreshed constantly using Power BI’s scheduled refresh in O365 or Report Server.
This post will introduce you to some basic conditional formatting, rules & validation ideas you can implement today in your customized SharePoint forms using PowerApps. And don’t worry – if you start making changes to your form and don’t want to keep them, you can easily switch back to the original SharePoint form.
Note: I previously shared how to do this in SharePoint Designer. The following method utilizing Flow is better, and does not use loops/pauses.
It’s well-known that SharePoint calculated columns don’t permit [Today] to be used as a formula for a calculated date column. And the “default to today’s date” setting only works upon creation, and doesn’t update daily. But we can create a standard date column and have Microsoft Flow automatically update it daily for us, therefore allowing us to effortlessly perform calculations against today’s date such as:
- Age =(TodayDate-Birthday)/365
- Years of Service =(TodayDate-StartDate)/365
- Days Past Due =(TodayDate-DueDate)
- Weeks until summer break =(SummerStart-TodayDate)/7
Here’s how to create your own, always accurate/updated, today column (see bottom of post for video):
Microsoft Flow mobile buttons are magical. One touch on your mobile device, and gears start turning to retrieve and deliver the data you need when and how you need it. Recently, I set out to deliver all Microsoft Forms responses to a recipient on-demand as an excel file using a Microsoft Flow mobile button they could press whenever they wanted the results. I also created a button someone could use to be sent all the birthdays coming up in the next week for our organization whenever they need it. You can adjust the following steps to fit your situation and tools, but the following outlines two ideas:
- Sending someone all responses to a Microsoft Forms survey whenever they press the button (Take a snapshot in time of responses, or pull up-to-the-minute feedback into your meeting)
- Sending someone SharePoint list items in an excel sheet that match a certain criteria (Projects ending in the next two weeks)