A game changer? setValue in Microsoft Lists

For as long as I can remember, SharePoint lists have kept a clear separation between viewing and editing. As soon as you needed to modify fields, you had to switch to a different screen, whether it was the grid view (Excel style) or a single item edit form. The alternative was to assemble your own layout, using a List View Web Part, InfoPath, programming, or more recently Power Apps.

A while ago, I also came up with my own solution, which I called the HTML Calculated Column (HTMLcc).. It later evolved to work with client-side rendering (CSR) in SP 2013. However, those classic approaches were sunset with the move to modern SharePoint and restrictions put on client-side scripts.

I had some tough years after modern was released, because of the huge feature gap. We were sent back to square one, with lists missing even the basics, such as form customization, templating, or calendar views. For many, classic lists were still the go-to choice.

Fast forward to 2021. Major changes have happened, especially in the past year. Forms can be customized out of the box, templates are back, and so are calendar views (well, month view for now), plus now board views. The integration with the Power Platform has matured, and you can today include a Power Apps form, a Power Automate flow, or a Power Bi report directly in your list customization. We also get offline access and half-baked mobile access (iOS but not Android, really?). A new branding reflects these changes, we are now calling them Microsoft Lists and they can live in SharePoint or OneDrive.

And now the cherry on the cake: setValue.

What I haven’t mentioned yet is that Microsoft has also come up with their own special way to customize list layouts in a safe way. It’s called column formatting or view formatting, and relies on the JSON notation to describe the rendering format. The idea is to inject HTML in the views – just like we used to do with HTMLcc and CSR – but restrict it to a controlled subset to keep it safe. This is in line with other safety measures restricting the use of JavaScript on SharePoint pages.

With this newfound ability to mix read-only and edit fields, view formatting is moving one step closer to what you can find elsewhere on the Web, or build with development frameworks such as SPFx. Microsoft Lists also become a more sensible alternative to Power Apps for simple scenarios.

One downside I see to this action is that it’s pure UI and doesn’t validate the user’s actual permissions. If you have read access only, or item level permissions are turned on in the list settings, you’ll still see the button and get an error message when you click. Personally I can live with that.

But how about Power Automate buttons?

Before setValue was released, list formatting already offered a custom action called executeFlow, allowing you to trigger a Power Automate flow. While the action itself is much more powerful than setValue, it has a major drawback from a UX standpoint: the delay between the user click and execution. While update and UI refresh are almost instant with setValue, it could take a few minutes for the flow to run and the change to be reflected in the UI.

I recently demonstrated setValue to someone who was new to list formatting. Much to my dismay, he was not impressed at all by the new capability, simply because he considered it the normal way things should work. And as you can guess he deemed field updates through executeFlow unacceptable… which from today’s perspective sounds about right.

Try it out! Some samples spotted in the wild

Clearly I am not the only one excited about this new action, and some samples have already popped up in the community. Microsoft MVP Tetsuya Kawahara published formatting for switches and checkboxes to the Github PnP repository. The above screenshot shows them in action in a turn-taking application I built last week. In my scenario, volunteers can come to the list and update their weekly availability (check the days they are in, or turn the Active switch off when they are out of the office). Power Automate flows run on top of that and assign action items to the volunteers available on a given day.

Michel Mendes published a sample with approve/reject buttons. Below a screenshot from his blog:

Another blogger, Dennis, published a use case for course registration.

Let’s not forget inlineEditField

Another recent addition, inlineEditField, allows you to make a specific field editable in list formatting. Also a convenient feature, for example to allow users to directly update a choice field like a status directly in the view.

inlineEditField will honor permissions, and only users with edit access will get on click the conversion to an edit control.

What’s next?

This open door to new dynamic behaviors got me excited like it was 2008 again, and I started brainstorming scenarios that go beyond simple row rendering. Hopefully I’ll have some creative use cases to share in the coming months! Just the few examples shared by the community in the past couple days show us how the new capabilities can help build simple apps with a more user-friendly interface, without the need for a bigger hammer such as Power Apps.


Introducing the Property Pane Portal [Part 3]

Audience: SPFx Reactjs developers.

[Part 1] Rethinking the SPFx Property Pane controls

[Part 2] Sample 1: Fluent UI controls

[Part 3] (this post) Sample 2: PnP SPFx Controls

[Part 4] The concept: leveraging Reactjs Portal for SPFx

[Part 5] Diving into the code

[Part 6] Sample 3: Microsoft Graph Toolkit controls

In part 2, I showcased the use of Fluent UI controls in the Property Pane. Now it’s the turn of the PnP SPFx Controls.

Today, the PnP portal on Github offers two sets of Reactjs controls for your SPFx solution:

These libraries are extremely useful, because they are targeted at Microsoft 365 users and offer controls that you wouldn’t get out-of-the-box in other control libraries, not even with Microsoft’s own Fluent UI. For example: SharePoint list picker, list item picker, or people picker. The only library I can think of that comes close is the Microsoft Graph Toolkit, which we’ll tackle in part 6 of this series.

Listen to Joel Rodrigues introducing the SPFx Reactjs Controls, starting at 26:15 in the August 2 episode of the Microsoft 365 Developer Podcast.

Now, the painful part is that you need two libraries, because the Property Pane cannot use the regular controls and requires its own dedicated property controls. This is what we intend to change with the Property Pane Portal: use the same sp-dev-fx-controls-react library not just for the Web Part body, but also for the Property Pane!

The general steps are the same as in part 2, with the use of PropertyPaneHost and PropertyPanePortal. The only difference is the control itself:

Note how the context gets passed to the components through a Reactjs Context Hook. Here again, just like for the example in part 2, the Property Pane Portal allows us to leverage all the usual Reactjs patterns. The benefit is not just to skip the sp-dev-fx-property-controls library, but also pave the way to richer forms allowing more dynamic styling or interaction across components.

See it for yourself with the Github sample. A sppkg package (for demo purposes only) is included if you are in a hurry 🙂

Introducing the Property Pane Portal [Part 2]

Audience: SPFx Reactjs developers.

[Part 1] Rethinking the SPFx Property Pane controls

[Part 2] (this post) Sample 1: Fluent UI controls

[Part 3] Sample 2: PnP SPFx Controls

[Part 4] The concept: leveraging Reactjs Portal for SPFx

[Part 5] Diving into the code

[Part 6] Sample 3: Microsoft Graph Toolkit controls

In part 1, I explained the objective of the Property Pane Portal: allow us to use any Reactjs controls library in the Property Pane. This post showcases the concept applied to the Microsoft Fluent UI Northstar controls. Here is a screenshot of the expected result for our Github sample:

To follow this post, a prerequisite is to be familiar with SPFx Reactjs and the default SPFx Property Pane. If you are only interested to test drive the Web Part, you can grab the sample sppkg file (demo purposes only) from Github.

In the Property Pane, each default control (text field in the example below) is generated by a built-in function (PropertyPaneTextField() in the example).

Let’s now see how the code in our Fluent UI Github sample differs.

With the Property Pane Portal technique, the generic function PropertyPaneHost() only generates the host of each control:

The Web Part property propertyPaneHosts serves as data store for the location of the hosts. It is attached to the Web Part (“this”) to ensure there is no collision, for example in case a page hosts multiple instances of the same SPFx Web Part.

The controls themselves are included in the React DOM, along with the regular components used in the Web Part body (screenshot below). Then the job of the Reactjs Portal is to beam them to the Property Pane.

You’ll find the CustomPropertyPane component in CustomPropertyPane.tsx:

The machinery that supports the PropertyPanePortal component and the propertyPaneHost function is hosted in the PPP folder, and should work with any Reactjs controls library. Parts 4 and 5 will explore the concept and code in more details. In the meantime, feel free to post questions in the comments below.

Introducing the Property Pane Portal [Part 1]

Audience: SPFx Reactjs developers.

My quest for an easier way to build and maintain Property Pane controls on the SharePoint Framework.

[Part 1] (this post) Rethinking the SPFx Property Pane controls

[Part 2] Sample 1: Fluent UI controls

[Part 3] Sample 2: PnP SPFx Controls

[Part 4] The concept: leveraging Reactjs Portal for SPFx

[Part 5] Diving into the code

[Part 6] Sample 3: Microsoft Graph Toolkit controls

A long due post…

Last year, I started doing more serious development work with the SharePoint Framework (SPFx).

I have been using JavaScript for 15 years, and got started with Reactjs in 2019, directly working with function components and hooks – rather than the traditional class components – as primary building blocks. My first Reactjs solutions were built on the Create React App toolchain. Transitioning to SPFx was easy, until I started building custom Property Pane forms. While the body of a SPFx Web Part behaves like any single page application, the Property Pane follows a very specific format. Let’s take a closer look.

Below an example of Property Pane taken from the Microsoft documentation. On the surface, it looks like a Web form allowing to set the Web Part properties.

When we crack the code open however, it doesn’t look like a regular client side form:

groupFields: [
PropertyPaneTextField('list', {
label: 'Select a list'
PropertyPaneCheckbox('display', {
label: 'Display'
PropertyPaneSlider('max', {
label: 'Max number of tasks'

(note: simplified code for illustration purposes only)

The controls are encapsulated in predefined functions. In a way, it’s as if each control was its own form.

It’s all fine, and even convenient, as long as stay within the boundaries of what the functions allow. But it falls short as soon as we require a little more advanced functionality, such as dynamic choices, conditional formatting, etc. For example, you might want to let the user pick a site, or a group, or a list item, and the available choices will naturally depend on the context.

SPFx has an answer to that: it allows us to build our own custom controls. The process however is very intricated, and needs to be followed for every custom control. If for example you wanted to leverage the Fluent UI controls in the Property Pane, you would have to maintain for each control a dedicated Property Pane version. In comparison, in the Web Part body, you would be able to use the Fluent UI library as is.

Such an approach is actually available in the PnP community. I had some opportunities to be involved in the testing of the library, and have discussions with some of its main developers (Alex Terentiev, Elio Struyf, Joel Rodrigues). Same deal, the team needs to maintain two instances in parallel: the regular Reactjs controls and the dedicated Property Pane controls.

When I started diving into the SPFx tutorial, I found out that each of these custom controls is made of two layers (diagram below): a base that anchors the control to the Property Pane, and then the core control itself that handles the user interface and functionality. The base gets in place first, then calls an onRender() method to summon the control. Each control includes an update() method to push changes back to the Web Part property bag.

This got me thinking. Could we in some way avoid the redundancy? And as a bonus, directly hook a regular Reactjs component in here? That’s what I pictured in the diagram below: a generic, reusable frame that could serve as host to any Reactjs control.

It took me a couple days to come up with a workable model (the “Property Pane Portal”), weeks to test it, and then months to start writing about it 😊. In the next episodes, I’ll share some samples of the PPP in action, and I’ll provide more details on the architecture and the code that supports it. The key ingredient is Reactjs Portals, which allow to beam an element to another part of the DOM.

Manage in-person office hours with Microsoft 365

California is fully reopening the economy on June 15 – no more physical distancing, no more capacity limits on businesses, and relaxed mask guidance. This is just one example of how regulations around the globe are loosening up, and allowing employees to get back in the office.

Does it mean that we are back to the 2019 normal? Certainly not.

For one, organizations are are mindful of their employees’ physical and mental health, and will proceed in stages. This is the case of Microsoft for example, with its six-stage dial.

They have also assimilated the benefits of partially working from home, with reduced office costs, and reduced commute resulting in savings and (hopefully) better life-work balance.

The result is a hybrid workspace model – part home, part office – with the ensuing need to track both room occupancy and employees’ office schedule. In this post, let me introduce two solutions to address this need, available for free within Microsoft 365. One is a feature of Outlook itself, and the other is the inPerson app I have developed for Microsoft Teams. Disclaimer: you’ll need the help of an admin and possibly someone experienced in Microsoft 365 to make the most of these solutions.

Outlook Workspaces

Last year, Microsoft introduced a new feature in Outlook: in addition to the well known meeting room reservation, you can now book workspaces. As defined by Microsoft, a workspace is “a physical location where employees can work from. It can be made up of many desks or can be a single desk”.

This feature was updated in May and I have not tested it yet. for detailed instructions, visit the Microsoft Exchange team blog. Admin access and PowerShell required for the implementation!

The inPerson app

inPerson is an app I developed a few months ago for a Microsoft Teams contest, and is now available on Microsoft AppSource. It is currently offered in English, with plans to expand it to a handful of other languages if there is enough interest.

What differentiates it from other AppSource business apps is that it is entirely built on the Microsoft 365 stack, with no third-party dependency. See it as both a good thing – you have full control on your data – and a bad thing – it is up to you, the team owner, to manage the data, including access, backup or archiving.

inPerson is built with the SharePoint Framework (SPFx) and made available as team tab in Microsoft Teams. To get started, check out the YouTube tutorials listed below. inPerson relies on interactive floor plans, so in addition to installing the app you’ll have to get those plans ready (spoiler: it’s easier than you might think).

Installation tutorial (3 minutes) 

Floor plan tutorial (5 minutes) 

Team owner tutorial (6 minutes) 

User tutorial (4 minutes)

What else?

The above options are the only ones I am aware of that live in Microsoft 365, are free and do not require a third party tool. If you’d like to recommend another one, please leave a comment!


Sample floor plan

If you have a jpg or png image of your floor plan, I recommend that you follow the tutorial and build in a few minutes your own accessible SVG. For a quick test, download the SVG below, it includes 12 seats numbered 101 to 112.

Yes, you can use Modern React on SharePoint 2016

Audience: developers.

While we are following the Microsoft 365 buzz in conferences and on social media, many organizations are still on an older platform, such as SharePoint 2016. I expect that many of you in this case are looking to migrate to the Cloud, in particular government agencies with the return of the JEDI contract.

SP 2016 site owners have been relying on the Content Editor Web Part (CEWP) or the Script Editor Web Part (SEWP) for page customization. Neither is available in modern SharePoint Online, and a transition roadmap is needed.

Let’s start with the good news. In recent years, the Microsoft 365 platform has embraced JavaScript, bringing in its own typed flavor called TypeScript. It can be used on SharePoint of course but also in Teams, Outlook, Power Apps or Power BI. In SharePoint and Teams, the points of entry are called the SharePoint Framework (aka SPFx, version 1.12 expected in February) and the Microsoft Teams Toolkit. Since 2019, Microsoft has also embraced React as the main engine for its client side frameworks, and consolidated its UI library under the Fluent UI brand.

That’s very exciting news for SharePoint Online users, but what if you are still on SharePoint 2016?

Microsoft included SPFx in a SP2016 upgrade back in 2018. That made the migration to the cloud straightforward:

Now the not so good news: in 2019 and 2020, Microsoft kept upgrading the SPFx in the Cloud, but didn’t bring any improvement to the 2016 platform. That makes the migration more challenging than it used to:

Hugo Bernier has an excellent compatibility matrix that shows us SP 2016 is restricted to v1.1 and SP 2019 is restricted to v1.4.1, while SP Online has skyrocketed to v1.11. In practice, it means that neither 2016 nor 2019 can leverage the recent versions of React with hooks.

While the above diagram describes the standard roadmap, I’d like to offer another perspective. Instead of getting stuck on premises with old React, here is a different roadmap I suggested in a recent project:

Instead of SPFx 1.1, the idea is to simply leverage the Content Editor Web Part (CEWP) or the Script Editor Web Part (SEWP), which can host any script including modern React. It means that developers can already build , on premises, solutions that leverage modern scripting such as hooks or Fluent UI. In a migration the code can then be transferred to SPFx 1.11.

There’s a number of challenges with this approach and, although it worked in my case, it won’t fit every scenario.

  • First, SPFx comes with a Yeoman scaffolding tool that sets up the dev environment for you. Without SPFx you’ll have to find another way. Fortunately React offers a number of options, including the Create React App toolchain that I used. Version 4 was released on 10/23/2020. Compared to SPFx, it doesn’t come with SharePoint integration, such as no publishing or workbench. Also, there is no built-in property pane. It is not completely foreign to the Microsoft 365 universe though: the Microsoft Teams dev team has chosen Create React App as the foundation of the Microsoft Teams Toolkit.
  • Because you are leveraging the CEWP/SEWP, it also means that site owners will have access to the underlying code. SPFx does a better job at encapsulating the code.
  • As it is the route less travelled, you’ll also have difficulties finding code samples. But to be fair, recent SPFx samples won’t work on SP 2016 either.

If you are still developing on SharePoint 2016, hopefully this article will encourage you to get off the beaten path, and get better prepared for both SharePoint Online and Microsoft Teams. Feel free to share in the comments your challenges and successes!

New terminology in the Power Platform dataverse

Microsoft recently pushed several terminology updates to the Power Platform.

Dataverse is the new name of the Common Data Service, the database leg of the Microsoft Power Platform. Its lightweight version that runs in Microsoft Teams is now called Dataverse for Teams.

Earlier this month, Microsoft already made terminology updates:

Legacy termCurrent term
Entity, entitiesTable, tables
Field, fields
Attribute, attributes
Column, columns
Record, recordsRow, rows
Option set, multi select option sets
Picklist, picklists
Choice, choices
Two OptionsYes/No

Notice how the new terms match the Microsoft Lists terms.

Check out the Microsoft docs for more details.

Trick or treat? Group items by month in Microsoft Lists

Objective: get documents or list items grouped by month. It could be for example invoices, receipts, or meeting minutes. For the above screenshot, I used the “Travel requests” template available in Microsoft Lists.

The challenge is to get the months in chronological order rather than alphabetical order, for example January-February-March-April rather than April-February-January-March.

This post is actually an update for Microsoft Lists of a SharePoint trick I posted 7 years ago. It still works! For a full explanation of the trick see the original article. I’ll only repost here the final formula for the calculated column:

=REPT(" ",13-MONTH([Travel start date]))&TEXT([Travel start date],"mmmm")

In the above formula, “Travel start date” is a column of type date that comes with the Microsoft Lists template.

You’ll probably want the year too. For this you can use another level of grouping, or just include it in the formula:

=YEAR([Travel start date])&" -"&REPT(" ",13-MONTH([Travel start date]))&TEXT([Travel start date],"mmmm")

The result:

If you are not familiar with calculated columns, you can check out an introduction I wrote… 12 years ago. It seems that the rewriting of the official documentation is still in progress, when there’s an update I’ll share it on this blog. In the meantime feel free to post your questions below.

For a list of functions that can be used in formulas, see here.