While working with a client on their SPO implementation recently I stumbled upon a very odd issue. Although it is Microsoft so….

We migrated content and metadata from an older, on-premise DM to SPO. There were several fields in each of the document libraries that needed to be required. We used several choice fields with their own internal lists. But one field had enough choices in it that we created a separate list of the choices and pointed a look-up field at it for both the primary selection and to pull in a secondary description field as well.

Next we do some testing prior to client training sessions and discover a problem. SharePoint has very tight integration with the Microsoft Office Desktop apps like Word and Excel. This is great because it gives users the opportunity to populate the metadata fields as part of the “save” process. In fact when you have required fields you must populate those at a bare minimum.

From Office 2016 forward, Microsoft (in their infinite wisdom) deprecated the DIP (Document Information Panel) which at the time made it very simple to enter your metadata while saving a document to anywhere including SharePoint.

With today’s Office version you have to go the the Info panel of Excel to be able to set metadata and it does not work at all with any lookup fields that are required. You will see the “Show Details” box with a red asterisk and border if it is required but clicking it will yield nothing. So we now have a catch-22, how do you save a file from Excel to SharePoint with a required field you cannot populate.

The only answer that would work was to remove the requirement for the field and build a Power Automate Cloud Flow that was triggered by a new file. Checked if it was an Excel file then paused for 15 minutes to give the user an opportunity to update the required field after saving. Then get the file properties and check if the field was blank. If it was then email the file creator and their manager that the file was not properly tagged.

Interestingly, this is not an issue in Word. Thanks again Microsoft.