Course registration with List formatting

Dennis Goedegebuure
4 minutes to read
17 Jan, 2022
- Community post

Recently Tetsuya and Michel shared some great examples of a new option in List formatting, the setValue action within List formatting. This blog shows an example on how to use list formatting to create registration or sign-up formatting in a SharePoint list.

Course Registration

One of the examples I have seen a lot on the Power Automate community is registration or sign-up in a SharePoint list. A lot of times a Power Automate flow would be needed to meet the requirements in these kind of scenarios. In this article I am going to show you can use the setValue action to meet most of your requirements without the need of building a flow. Assume we have the following requirements:

A user can register for one or several courses
A course has a number of available places, when all places are taken registration is not possible
There is a registration end date, after this registration is not possible

SharePoint Online list

First of all you would need to create the SharePoint Online list and the columns. To give you a head-start a create a PnP PowerShell script for this. Feel free to reuse it.

<# .SYNOPSIS
    Create list for Course registration.
.DESCRIPTION
    You need to have the latest version of PnP PowerShell
    Create list for Course registration.
.PARAMETER SiteCollection
    Specifies the site collection Url of the SharePoint Online environment.
.PARAMETER ListName
    Specifies the name of the SharePoint Online list.
.NOTES
    Author:
    Expiscornovus
    Current Version:
    0.1
    Version History:
    0.1 - First minor version
.EXAMPLE
    PS> .\create_courseregistration_spolist.ps1 -SiteCollection "https://contoso.sharepoint.com/sites/formatting" -ListName "Courses"

#>
param ([Parameter(Mandatory)]$SiteCollection,[Parameter(Mandatory)]$ListName)
Connect-PnPOnline -Url $SiteCollection -Interactive
New-PnPList -Title $ListName -Template GenericList
Add-PnPField -List $ListName -DisplayName "Registration End Date" -InternalName "RegistrationEndDate" -Type DateTime
Add-PnPField -List $ListName -DisplayName "Course Date" -InternalName "CourseDate" -Type DateTime
Add-PnPField -List $ListName -DisplayName "Register" -InternalName "Register" -Type Text
Add-PnPField -List $ListName -DisplayName "Number of Available Places" -InternalName "NumberofPlaces" -Type Number
Add-PnPFieldFromXml -List $ListName -FieldXml '<Field Type="UserMulti" DisplayName="People Who Registered" UserSelectionMode="PeopleOnly" StaticName="PeopleWhoRegistered" Name="PeopleWhoRegistered" Mult="TRUE" />'
Add-PnPView -List $ListName -Title "All Courses" -Fields "Title","RegistrationEndDate","CourseDate","Register","NumberofPlaces","PeopleWhoRegistered"
Disconnect-PnPOnline

Change format Register column

Navigate to your newly created list and select Column settings > Format this column.
Insert the json below and select save.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "flex-directon": "row",
    "justify-content": "left",
    "align-items": "center",
    "flex-wrap": "nowrap"
  },
  "children": [
    {
      "elmType": "div",
      "style": {
        "display": "=if(indexOf([$PeopleWhoRegistered.email], ) == -1 && length([$PeopleWhoRegistered]) != [$NumberofPlaces] && @now <= [$RegistrationEndDate], 'inherit','none')",
        "flex-directon": "row",
        "justify-content": "left",
        "align-items": "center",
        "flex-wrap": "wrap"
      },
      "children": [
        {
          "elmType": "button",
          "customRowAction": {
            "action": "setValue",
            "actionInput": {
              "Text": "Update",
              "PeopleWhoRegistered": "=appendTo([$PeopleWhoRegistered], )"
            }
          },
          "attributes": {
            "class": "ms-fontColor-themePrimary ms-fontColor-themeDarker--hover"
          },
          "style": {
            "border": "none",
            "background-color": "transparent",
            "cursor": "pointer",
            "display": "flex",
            "flex-directon": "row",
            "justify-content": "left",
            "align-items": "center",
            "flex-wrap": "wrap"
          },
          "children": [
            {
              "elmType": "span",
              "attributes": {
                "iconName": "AddTo"
              },
              "style": {
                "padding": "0px"
              }
            }
          ]
        }
      ]
    },
    {
      "elmType": "div",
      "children": [
        {
          "elmType": "span",
          "txtContent": "Already Registered",
          "style": {
            "display": "=if(indexOf([$PeopleWhoRegistered.email], ) == -1, 'none','inherit')",
            "padding-left": "5px",
            "word-break": "keep-all"
          }
        }
      ]
    },
    {
      "elmType": "div",
      "children": [
        {
          "elmType": "span",
          "txtContent": "Course is full",
          "style": {
            "display": "=if(length([$PeopleWhoRegistered]) != [$NumberofPlaces], 'none','inherit')",
            "padding-left": "5px",
            "word-break": "keep-all"
          }
        }
      ]
    },
        {
      "elmType": "div",
      "children": [
        {
          "elmType": "span",
          "txtContent": "Registration has ended",
          "style": {
            "display": "=if(@now <= [$RegistrationEndDate], 'none','inherit')",
            "padding-left": "5px",
            "word-break": "keep-all"
          }
        }
      ]
    }
  ]
}

Explanation of the json

First we try to handle the showing/hiding of the register button. This is done in the display property of the div.

if(indexOf([$PeopleWhoRegistered.email], ) == -1 && length([$PeopleWhoRegistered]) != [$NumberofPlaces] && @now <= [$RegistrationEndDate], 'inherit','none')

In this formula I am using a conditional operator if(). In that if statement I am checking if three things are all true.

If the e-mail of the person who is creating the list item is not registered. This expression is using a IndexOf binary operator and a @me special string value. When the expression returns -1 it hasn’t found the e-mail and the button will still be shown.
It will compare the length unary operator to check the number of people registered for the current course. When that isn’t equal the number of available places the register button will still be shown.
If the current date is before the registration date the register button will still be shown. The @now special string value is used for this. In the bottom of the json I am using the same expressions only in individual divs and the true false values will be in reserve. This is to show a custom message when one of the conditions is met.

{
          "elmType": "button",
          "customRowAction": {
            "action": "setValue",
            "actionInput": {
              "Text": "Update",
              "PeopleWhoRegistered": "=appendTo([$PeopleWhoRegistered], )"
            }
          }

And finally the start of this blog, the customRowAction with a setValue. In this case I am using a appendTo binary operation to add the person who is creating the list item to the existing list of people who registered.