Forum Discussion
Column Visibility based on previous people column
We've setup persistent approvals for travel requests using a Power Automate flow (not the built-in workflow), so the approved status is not lost if/when the list item is edited.
We have a choice column for Approval Request allowing "Draft" and "Submitted".
Just need a control to prevent saving the list item as "Submitted" if there is no selection in the Approver (people) column.
As you know, we can't use a people column to evaluate conditional visibility on the list form...
https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/list-form-conditional-show-hide
And we've tried JSON formatting with a 'helper' column to show if an approver has been assigned or not:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "=if(length([$Approver]) > 0, 'Assigned', 'Unassigned')"
}
But this doesn't set a value in the column so cannot be used for list validation settings.
To reduce complexity, we're trying to do this without using a Power Apps form and conditional visibility... but maybe it's not possible?
Already found this help redirecting to Power Apps...
https://techcommunity.microsoft.com/discussions/sharepoint_general/column-visibility-based-on-previous-multi-choice-column/3987360
Any alternative suggestions are welcomed.
Thanks!
1 Reply
I'm not sure how your automation is set up for triggers and whatnot, but I have a number of similar automations. They needed to be simple enough to view and maintain that my client could edit it as needed. The list is set up with the "People" column for the manager/approver that defaults to blank, a choice column for submitted/awaiting manager approval/ awaiting COO approval/awaiting CEO approval/denied that defaults to blank, and a choice column each for manager approval/COO approval/CEO approval that all default to blank but have the options of submitted for approval/approved/denied/NA.
In my case the automation is all triggered by a form submission. Then the automation gets the items from the form and creates an item in the SharePoint list with all of those columns blank as defaulted. Their approver is the manager of the person who submitted the request so I "Get user profile" to pull their manager's name, then update the SharePoint list item to fill in:
- "People" column with manager name
- change the status to awaiting manager approval
- change the "Manager approval" column to "submitted for approval."
The approval is triggered at that point and sent to the manager. The next step is a conditional formatting for if the output of the manager approved, continue to get approval from the COO, if declined, send an email to the user that their request was declined. Regardless of the outcome, there is an update item action that updates the list with the manager's response.
Approval:
- changes the first column to "awaiting COO approval,"
- changes manager approval column to "approved,"
- COO approval to "submitted for approval."
Decline:
- changes the first column to "Declined,"
- changes manager approval column to "declined,"
- changes remaining approval columns to "NA."
This process continues until all approval responses have been given. When all approvals have been given, the submitter receives an email that their request was approved. Creating it this way allowed for the columns to be updated as the flow continued, while leaving them blank or set to NA based on set criteria.
I hope this helps!
