Action Button in Lists: Set Column Value
SharePoint Lists / Microsoft Lists have a capability that allows you to add a button to a row, which when clicked will set the value of one or more columns in that row. For example, a “Complete Task” button, when clicked, could set the a status column in a row to “Completed” and set a person field called “Completed by” to the current user’s name. All of this can be done without the need for any kind of workflow or flow. This will entail some JSON code.
You’ll also learn the logic of how to create a button that only shows for a certain person.
Towards the end of this post, I’ll show you a way to kick off a flow, without the flow panel popping out and without the user having to click to run the flow in multiple dialog boxes.
I’m not just giving you a bunch of code snippets, I’m teaching you what the different pieces of the snippets do, so that you’ll know how to tweak them as needed.
There are three main concepts I’ll cover first:
- Action – What the button does when clicked
- Visual – What the button looks like (colors / style)
- Text – What the button says
Here is a basic portion of code, to show you the gist of the structure of the action. This will create a button that says CLICK HERE. You can set as many different column values as you’d like, but in this example there are two. The blue shows the internal (system) names of the columns, and the purple is what you’d like to set them to.
“txtContent”: “CLICK HERE”,
Here are the steps to set up a simple action button to set a field value for a yes/no (boolean) column. In this example, when a trip is over, I’d like to click a button that will set the column to Yes (true). Since this is a boolean column, SharePoint sees it as true/false or 1 / 0
I’m starting off very simple, and will be adding things like logic later. We’re starting off with setting a boolean, and then moving to other column types that contain text.
1. Any column in your list or library can be used as the placeholder for a button. It can be a column that already has data in it, but for my example, I’m just going to create a brand new, blank, single line of text column in my list, and call it Button.
2. Create a Yes/No column called Trip Over. (I’m creating this with a space in it, so when I do that, the system name will be Trip_x0020_Over)
3. Create a Choice column called Status. Use the following choices, with New being the default value.
4. Click the column header –> Column settings –> Format this Column
5. Click Advanced Mode
6. Paste this code in your code editor:
“txtContent”: “Trip is over”,
7. Click Save. Close the code window.
8. Now in the list, click the button on a row, and you’ll see that the value turns to YES on that row, in the Trip Over column.
9. Now let’s try a different column type. We’ll just make a couple of changes to that same button. Click the Button column header, choose Column settings and Format this column. (You’ll keep using Format this column each time you change the code in all of the steps below)
10. Make the following changes to the code. The txtContent is now going to say “Working on it”, and the actionInput is now going to set the Status column to the value “In Progress”.
11. Next, there’s the concept of logic, which can be done in the JSON code. Here’s how I can make the text say “Set to in progress” if it is new, and “Approve this” otherwise, in line 4:
“txtContent”: “=if([$Status] == ‘New’, ‘Set to in progress’, ‘Set to Approved’)”
That won’t do, though, we want to be even more specific about what it says, per different statuses.
“txtContent”: “=if([$Status] == ‘New’, ‘Set to in progress’, if([$Status] == ‘In Progress’, ‘Set to Approved’, ‘none’ ))”
You could have several nested IF statements, and then at the end, this statement says that the column will say the word “none” if none of those criteria apply. We’ll get to the logic, too, that will let you hide the button altogether, per logic conditions.
12. That txtContent logic only controls what the button says. You can also control what value gets set, under the actionInput.
Here’s how to control what value gets set in the status column, in line 8:
“Status”: “=if([$Status] == ‘New’, ‘In progress’, if([$Status] == ‘In Progress’, ‘Approved’,”))”
In this code, it doesn’t like it when you try and use an IF statement without an ELSE, so that’s why I have an empty set of single quotes in that last part of the formula.
13. Add another new column, a person column called Approved By. Add a new date column called Approved On.
When you set the value, you can set multiple columns. So, if the person clicking the button is approving it, their name can automatically be placed in the Approved By column, and the current date can be recorded in the approved on column. In this example, though, I only want it to set those fields IF it is currently in progress being moved to approved. So, here is the syntax:
In order to set the current date/time in a column, use @now
In order to grab the logged in user’s name, and set it in a person column, use @me
14. What about the color of the button? You can use logic here as well.
Notice the style section that I added. My style is extremely simple, with a green background and white text, but if you know how to CSS styling, you can do an extreme amount of customization in this code. Here is the logic that sets the background color to green if the item has a status of New, otherwise it is blue:
“background-color”: “=if([$Status] == ‘New’, ‘green’, ‘blue’)”
What if you want to hide and show the button according to certain logic? That can be done with visibility in the style. In this example, if the status is not New, I want to show the button, otherwise hide it:
What if you want to only show the button to a certain person?
* Here is how to write the code that shows the button only if Manager = ME
Here is how to use an AND, so that you can only show the button if the logged in person is the manager AND the status is not already approved”
“visibility”: “=if(‘@me’ == [$Manager.email] && [$Status] != ‘Approved’,’visible’,’hidden’)”
Next, you might be thinking that these buttons sure are ugly. You also may be thinking, “What about having an approve AND a reject button”? That’s where you get into the realm of JSON and writing code. I’m no JSON expert, but there are a bunch of people out there who have written blogs and sample code that you can use, to try out different styles. There is an *extreme* amount of formatting that is possible with this. Check out the link below in my resources, to Michel Carlo’s blog for one example of how to create some lovely approve / reject buttons in a single column.
You may also be wondering what some other actions may be, besides setValue. There is an action called defaultClick, which will just open that item. There’s editProps, which opens up the item in edit mode, like that old “Edit” column used to do in classic SharePoint. There is also an action called share, which will pull up the sharing dialog box. Then, there’s the executeFlow action, which I covered in an old post called Button in SharePoint List to Trigger Flow. There are also a couple of others, delete and openContextMenu, which are all mentioned in that first reference link at the bottom of this post.
Here’s the code all together:
“txtContent”: “=if([$Status] == ‘New’, ‘Set to in progress’, if([$Status] == ‘In Progress’, ‘Set to Approved’,”))”,
“Status”: “=if([$Status] == ‘New’, ‘In Progress’, if([$Status] == ‘In Progress’, ‘Approved’,”))”,
“Approved_x0020_By”: “=if([$Status] == ‘In Progress’, ‘@me’,”)”,
“Approved_x0020_On”: “=if([$Status] == ‘In Progress’, ‘@now’,”)”
“background-color”: “=if([$Status] == ‘New’, ‘green’, ‘blue’)”,
“visibility”: “=if(‘@me’ == [$Manager.email] && [$Status] != ‘Approved’,’visible’,’hidden’)”
But wait, there’s more!
Speaking of that old blog post of mine, there were some negatives to that approach. The main problem with executeFlow, is that you then have another couple of clicks, to get the flow running. When you executeFlow, it opens up the flow panel on the far right side, and then the user has to click to run the flow. Even if you don’t have any inputs, you’re still having to make end users do all of those annoying clicks. Why can’t the button just immediately trigger the flow? Voila! Now it can, using this setValue method. Here’s how:
The scenario is that you may have a complex flow, with multiple levels of approvals, or just any other complexity that needs to involve a flow, not just simply changing a couple of column values. You want to run a flow at the click of a button, but you don’t want end users to have to go through the pain of multiple clicks, and you also want them to be able to visually see the button go away so that they don’t run the flow twice. This is a completely different scenario than the above one, not to be used in combination with it. So, to make that clear, we’ll be creating a new button for this new concept.
The gist of it is that instead of using executeFlow, I’m going to create a flow that runs based on an item being modified in my list, and I’m going to use this setValue action on the button, which will set the value of a column, which will trigger the flow. This is in the associated video (at the bottom of this post) starting at 41:00 minutes.
1. Create a flow that uses the SharePoint trigger called When an item or file is modified, and pick your site and the Travel Requests list you’re using for this test.
2. In the settings of the trigger, go to trigger conditions. My status column is a choice column, and I only want this flow to run if the current status of the item is In Progress. So, my trigger condition is:
@equals(triggerOutputs()?[‘body/Status/Value’], ‘In Progress’)
3. Add a SharePoint action called Get changes for an item or a file (properties only)
Use the ID from the current item, and use this for the expression in the Since box:
This will give me the data that lets me know exactly which column value was just changed, and I will need to know if the status just changed.
4. Add a condition. Has Column Changed: Status is equal to true
5. In the Yes path, add an approval action.
6. Create a new single line of text column in your list, and you can call it anything. I named mine “ApprovalFlow”.
7. Click the column header –> Column settings –> Format this column.
8. Here is the code:
“txtContent”: “Start Approval Process”,
“Status”: “In Progress”
“visibility”: “=if([$Status] == ‘New’,’visible’,’hidden'”
9. Click SAVE. This button simply sets the status to In Progress, and then it only shows if the status is New. Since NEW is your default status choice, all items will have this blue button showing until someone clicks it, then the flow will kick off and the button will be hidden.
This is an extremely simple flow. The thing that I’m demonstrating here is the concept of triggering a flow based on a specific status, not really what’s in the flow. All I’m putting in here is an approval, but of course yours can be as long and complex as you’d like. You would probably want something to happen in your flow after the approval has been done, like send an email, update the SharePoint item, set the content approval status, or anything at all.
This flow will only trigger if the status is in progress, and the approval will only start if the status is the column that just changed. This way, an approval process can only be kicked off, under this very specific condition.
Here is my reference video where I demonstrated all of these steps. Scroll down for the resource links below.
Power Hour: List Formatting Sample Gallery