Electronic Form & Workflow with SharePoint Online Site Column, Calculated Column, Content Approval on Custom List

Problem

As a member of Agile Development team, I've been tasked to utilize SharePoint Online custom list to quickly mock-up electronic form, database, workflow and I found interesting features and limitations that SharePoint Developer should be aware of in order to utilize built-in SharePoint functionalities to suit certain use-cases.

Findings

Site Column

Tips

It can be hidden from the list form by configuring Site Content Type.

Limitation

It must be optional to be dynamically hidden via list form Edit columns | Edit conditional formula.

Calculated Site Column

Tips

1. Calculated site column can be used to include other site column(s) for list view column formatting reference purpose (i.e. =if(1=1,[CalculatedColumnNameToDispayOnListView],[OtherColumnName1]+[OtherColumnName2]...).

2. Name with : suffix (i.e. Date Submit for Approval: with formula =IF(1=1,IF([Date Submit For Approval]>0,[Date Submit For Approval]," "),[Flow Is Running]+[Form Status])) to use on list form to prevent editing (i.e. Date Submit For Approval) and to allow list column formatting formula (i.e. Approval Status) to refer to.

Limitations

1. Workaround only works for list view formatting, but not list form (Edit Column formula).

2.1. It cannot have formula reference to People site column.

2.2. It cannot have formula reference to _ModerationStatus (Approval Status).

2.3. It cannot be hidden on Site Content Type level, however it can be hidden by unticking checkbox on form Edit Column formula.

2.4. It cannot be too complex (i.e. too many IFs) on Site Column level, however it can be on List column level? Strange but true as of April 2021.

Content Approval for Custom List

Tips

1. You can set Require content approval for submitted items? Yes on List settings | Versioning settings to leverage built-in Approval Status (Pending/Rejected/Approved) in order to restrict who can see Draft (non-Approved) list item (i.e. only users with Read permission, Contribute + Edit permission, or Approver permission + Author) when the list item Approval Status is still Pending or Rejected.

2. Setting Who should see draft items in the list? *Only users who can edit items and

List settings | Advanced Settings | Item-level Permissions |

Read access: *Read all items and 

Create and Edit access: *Create items and edit items that were created by

will limit list item to be editable to only Created By (Author) with Contributor or Editor permission when  Draft or Rejected.

3. To prevent Approved list item to be edited by certain users, create a folder i.e. Approved (locked), break the folder permission inheritance, and secure it by unique site group. After the Content Approval Flow set the Approval Status to Approved, use the following API call to move the list item to the folder:

Send an HTTP request to SharePoint

Method: POST

Uri: _api/SP.MoveCopyUtil.MoveFileByPath()

Headers:

Accept                application/json;odata=verbose

Content-Type    application/json;odata=verbose

Body

{"srcPath":{"__metadata":{"type":"SP.ResourcePath"},"DecodedUrl":"https://your-tenancy.sharepoint.com/sites/SiteName/Lists/CustomListNameFlowTriggerBodyFullPath/variableListItemID_.000"},"destPath":{"__metadata":{"type":"SP.ResourcePath"},"DecodedUrl":"https://your-tenancy.sharepoint.com/sites/SiteName/Lists/CustomListNameFlowTriggerBodyFullPath/Approved (locked)/variableListItemID_.000"},"options":{"__metadata":{"type":"SP.MoveCopyOptions"},"RetainEditorAndModifiedOnMove":true,"ShouldBypassSharedLocks":true}}

4. You can quickly create Approval workflow using Content Approval PowerAutomate Flow Template to the custom list coupled with site column Form Status: Draft/Submit For Approval, Flow Is Running: Yes/No coupled with built-in {ModerationStatus} (Approval Status) as Flow Trigger Conditions:

@and(equals(triggerBody()?['Flow_x0020_Is_x0020_Running']?['value'],'No'),equals(triggerBody()?['Form_x0020_Status']?['value'],'Submit For Approval'),equals(triggerBody()?['{ModerationStatus}'],'Pending'))

5. To enable Draft editing, create a new site column Form Status non-mandatory choice field with Draft and Submit For Approval choices and default: Draft. Also on the form Edit Column | Edit conditional formula =if([$Form_x0020_Status]=='Submit For Approval','false','true') to hide the field on the form if Submit For Approval is selected.

6. Also create another site column Flow Is Running choice field with Yes and No choices string and default: No to indicate if there is already an approval flow running to prevent triggering another flow.

7. To display Draft/Pending/Approved/Reject on the list view, use the following formatting on the Approval Status column:

{

"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",

"elmType": "div",

"children": [

{

"elmType": "button",

"txtContent": "=if([$Form_x0020_Status]=='Draft' && @currentField=='Pending' && [$Flow_x0020_Is_x0020_Running]=='No','Draft',if([$Form_x0020_Status]=='Submit For Approval' && @currentField=='Pending' && [$Flow_x0020_Is_x0020_Running]=='No','Submitted For Approval',@currentField))",

"customRowAction": {

"action": "defaultClick"

},

"style": {

"background-color": "=if([$Form_x0020_Status]=='Submit For Approval' && @currentField=='Pending' && [$Flow_x0020_Is_x0020_Running]=='Yes','#b3ad10',if(@currentField=='Rejected','#c42121',if(@currentField=='Approved','#1d8f4e','#A9A9A9')))",

"color": "#ffffff",

"outline": "transparent",

"border-width": "1px",

"border-style": "solid",

"border-color": "transparent",

"width": "100%",

"height": "3.5em",

"display": "block",

"cursor": "pointer"

}

}

]

}

8. Hide Approved/Rejected by column with the following list-view column formatting:

{

"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",

"elmType": "span",

"txtContent": "=if([$_ModerationStatus]=='Approved' || [$_ModerationStatus]=='Rejected',[$Approved_x002F_Rejected_x0020_by.title],'')"

}

9. Hide Date Approved/Rejected column with the following list-view column formatting:

{

"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",

"elmType": "span",

"txtContent": "=if([$_ModerationStatus]=='Approved' || [$_ModerationStatus]=='Rejected',toLocaleDateString(@currentField),'')"

}

10. To display all list items created by me but including Approved (locked) folder on flat format, select List Settings | Advanced Settings | Edit current view | Folders | Show all items without folders and also Filter | Show items only when the following is true: Title is not equal to Approved (locked) And Created By is equal to [Me]

Limitations

1. No minor version (DRAFT) functionality like when used on Document Library so untick Keep draft checkbox for Custom List.

Comments

Tips

1. You can log your approval events in a running sheet on Comments (new list functionality) like Submit For Approval, item Approved or Rejected with Approvers Comments using Flow action to call the following API:

Send an HTTP request to SharePoint

Method: POST

Uri: _api/web/lists/getbytitle('@{variables('ListTitle')}')/items(@{variables('ListItemID')})/Comments()

Body

{"text":"@{triggerBody()?['Editor']?['Email']} submitted to @{variables('Approver')} for Approval"}

and for Approved/Rejected Comments

Body

{"text":"@replace(replace(replace(outputs('HTMl_Table_with_Styles'),'</th><th>','</th><th> - '),'</td><td>','</td><td> - '),'<tr><td>','<tr><td>\n')"}

Limitations

1. Comments can be edited by anyone with Contribute permission or higher to the list item after it's approved, so it has to be moved to read-only folder i.e. Approved (locked) to make the Comments read-only.

List Item Attachment

Tips

1. You can attach files and it will follow list item security.

Limitations

1. The attached filed doesn't have versioning.

 

2. To hide the Attachment control on the out-of-the-box list form, select Attachments to list items are: *Disabled AND you must untick Add attachments column to default view everytime you modify and save List Settings | Advance Settings on SharePoint Online as of April 2021.

Leveraging Lookup Field

Tips

1. If there's a requirement to allow editing part of approved (locked) form, a separate list linked with Lookup field will come handy.

 

2. To render Lookup Field as clickable button on list-view and list-form that will open a ChildEditableLinkedList item in Edit form and when clicking Cancel or Save form button it will browser will be redirected to ParentNonEditableLinkedList item leveraging the Source=returnUrl query string, use the column formatting formula:

 

{

"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",

"elmType": "a",

"attributes": {

"target": "_self",

"href": "='/sites/SiteCollectionName/Lists/ChildEditableLinkedList/EditForm.aspx?ID=' + @currentField.lookupId + '&Source=%2Fsites%2FSiteCollectionName%2FLists%2FParentNonEditableLinkedList%2FDispForm.aspx%3FID%3D'+[$ID]"

},

"style": {

"text-decoration": "none"

},

"children": [

{

"elmType": "button",

"txtContent": "@currentField.lookupValue",

"style": {

"background-color": "=if(@currentField.lookupValue=='To Confirm','#b3ad10',if(@currentField.lookupValue=='Not Successful','#c42121',if(@currentField.lookupValue=='Successful','#1d8f4e','#ffffff')))",

"color": "#ffffff",

"outline": "transparent",

"border-width": "1px",

"border-style": "solid",

"border-color": "transparent",

"width": "100%",

"height": "3.5em",

"display": "block",

"cursor": "pointer"

}

}

]

}

 

Note: [$ID] is case-sensitive and &Source=returnUrl must be encoded.

3. If the editable field is a choice-field (i.e. Outcome field with To Confirm, Successfull, Not Successful radio-buttons), create another calculated-field (i.e. Outcome:) to allow child linked-list Lookup: Outcome field to refer to Outcome: value for display on parent linked list-form and list-view.

4. To hide editable field on parent linked-list form on New item, use the following Conditional formula: =if([$Lookup_x003A__x0020_Outcome]=='','false','true')

Limitation

1. Lookup field cannot refer to In this column: choice-field so the workaround is to use calculated-field referring to the choice-field.

2. If user click Return to classic SharePoint on list-view, the Lookup field will be editable if not hidden.