Custom Power App Forms in SharePoint Online: 'Start Date and Time' and 'End Date and Time' Validation on Submit

Customizing a SharePoint list form in Power Apps and need to ensure a 'Start Date and Time' field is always earlier than its corresponding 'End Date and Time' field? See steps below. Note that I am using a Submit button for validation.

1)  Identify the data card values for your 'Start Date and Time' and 'End Date and Time' fields within your custom Power App form. The start date and time values for my solution were DateValue1, HourValue1, and MinuteValue1, and the end date and time values for my solution were DateValue2, HourValue2, and MinuteValue2.

2)  Next, add a Submit button to your form. For the OnSelect property of the Submit button, enter the following If statement in the formula bar. Be sure to match the DateValue, HourValue, and MinuteValue with your data card values from Step 1.

If(DateValue2.SelectedDate + Time(Value(HourValue2.Selected.Value), Value(MinuteValue2.Selected.Value), 0) < DateValue1.SelectedDate + Time(Value(HourValue1.Selected.Value), Value(MinuteValue1.Selected.Value), 0), Notify("End Date and Time cannot be earlier than the Start Date and Time", NotificationType.Error),SubmitForm( SharePointForm1 ))

3)  Save and Publish the Power App form.

Prevent duplicate reservations on a SharePoint Calendar using Power Automate

When building an asset reservation system in SharePoint Online, you may have a need to prevent duplicate reservations on a SharePoint calendar. There are several popular solutions for this requirement, but I prefer to use Power Automate with the 'Get Items' action and a filter query. 

Most reservation systems I have seen in SharePoint today basically use two lists -- one for capturing each reservation request and another list for the reservation calendar. Each request form submission triggers a Power Automate flow that uses the 'Get Items' action with a filter query to check existing items on the reservation calendar that contain the same values for one or more columns submitted in each request, before scheduling the new request. 

I initially struggled with defining the appropriate logic for the 'Get Items' filter query, but I eventually determined the key point to remember is the fact that each new request will always have a start and end date. Therefore, the filter query only needs to retrieve the existing calendar items that currently exist BETWEEN the proposed start and end dates of each new request. Here is the formula I recommend for the 'Get Items' filter query.

StartDateandTime (from Calendar) le (less than or equal to) Return Date (from Request) AND EndDateandTime (from Calendar) ge (greater than or equal to) Departure Date (from Request)

Of course, the next step in the flow (after the Get Items action) should be a Condition that uses an expression to see if the output of the previous 'Get Items' action contains one or more existing items in the calendar list. See below. If there are any duplicate items identified, you can then define the next action in your flow.

length(outputs('Get_items')?['body/value']) 'is greater than or equal to' 1

Hope this helps anyone looking to prevent duplicate items on a SharePoint calendar.

SharePoint Modern Page: Site Collections Directory

Adding a site collection directory to a SharePoint classic page was easy. However, with the new modern pages in SharePoint, it has become more challenging. A quick, easy way I have found to accomplish this need is to use the Highlighted Content web part and a custom query. See below for details.

1. Add the Highlighted Content web part on the page where you want the site collection directory.

2. Edit the Highlighted Content web part, select 'Custom query', select 'All sites' for the Source, and then add the following custom query in the 'Query text (KQL)' box: Path:"https://tenant.sharepoint.com/sites/*" contentclass:STS_Site. See screenshot below for an example. To sort the results on the page, you are given the option to use a managed property to sort on. As you can see in the screenshot below, I searched for the term "string", and ultimately selected the 'RefinableString00" managed property, which sorted my site collections by site name.

3. Republish the page.

Restore a deleted site collection (SharePoint 2010 / 2013 / 2016 / 2019 On-Premises)

Did you know that you can now restore a deleted site collection? Microsoft has issued two PowerShell commands for this need. The only caveat is that they only work in an on-premises farm. I tested both commands recently, and I can tell you firsthand that they work!

Get-SPDeletedSite: Gets a list of deleted site collections.

https://docs.microsoft.com/en-us/powershell/module/sharepoint-server/get-spdeletedsite?view=sharepoint-ps

Restore-SPDeletedSite: Restores a deleted site collection.

https://docs.microsoft.com/en-us/powershell/module/sharepoint-server/restore-spdeletedsite?view=sharepoint-ps

Item-Level Permissions in SharePoint Online using Power Automate

As you fellow SharePoint gurus already know, SharePoint 2010 Workflows will be retired this year. This of course also means that we will eventually lose the all important 'Replace List Item Permissions' action in SharePoint Designer, which essentially gave us the ability to implement automated item-level permissions for a SharePoint library or list. I recently took the time over a weekend to evaluate any good alternative (non 3rd party) solutions that I could think of, and it appears that Power Automate is now the best way to go. However, there is one drawback to this new approach -- you can only assign Edit or Read level permissions to any given item in a SharePoint library or list.

1. Go to the Power Automate site, which at the time of this article is located at https://us.flow.microsoft.com/en-us. Be sure to login.

2. Create a new automated blank flow using the 'When an item is created' SharePoint trigger.

3. Add the remaining logic as shown in the screenshot below.

NOTES:  1) The ID is available in the dynamic content section that appears once you click inside the 'Id' field in your flow.  2) I also used the dynamic content section for the Recipients, but you can manually type in email addresses as long as you separate them with a semicolon.

SharePoint Online Wiki Homepage: Change Top Suite Bar Color (CSS)

To change the color of the top suite bar on a SharePoint Online Wiki homepage, add a Script Editor web part to the page and add the following CSS code:

#suiteBarTop, #O365_NavHeader, #O365_AppName, #O365_MainLink_NavMenu, #O365_MainLink_Bell, #O365_MainLink_Settings, #O365_MainLink_Help, #O365_MainLink_Me  

{

background: #0078d4 !important;

}

That's it. Enjoy.

SharePoint Roles are Business Roles, Not IT Roles

SharePoint is a platform for BUSINESS SOLUTIONS. Without business purpose, SharePoint is worthless. And because SharePoint professionals work so closely with the core areas of business (accounting, business development, finance, operations, etc.), I wholeheartedly believe SharePoint roles are business roles, not IT roles.

Just a little 'food for thought' for the next time you are planning to hire a SharePoint professional.

That is all for this post.

Quick Tip: Add 'Find a file' search box to Document Library Web Part

Another Quick Tip:

To add the 'Find a file' search box to a document library web part on a page, simply edit the web part, expand the Miscellaneous section, and select the checkbox to "Display search box". That's it.

If only Microsoft made everything that easy! :)

Have a great day.