Help with Complex Sharepoint List column formatting

Copper Contributor

Hello,

 

I have a tricky issue I am trying to figure out a solution to. I have a SharePoint list with multiple columns and I want to set up column formatting for each. The format I want to create should change the background color to yellow when a new version of the item is created. 

 

The tricky part is the requirements, I want to ONLY highlight the specific column which I am changing. For example, if I have 5 columns titled 1,2,3,4,5 and I change something in column 3 I want to only highlight the item in column 3, not the whole row. That makes it so I cannot use the modified column to reference the only thing I could think of is Version History (Versioning is enabled), but I am not sure how to reference this in the JSON code and only reference a specific column. Is this possible and how would I go about doing this?

 

***Additionally I would like to create a function that clears all of the column formatting to I can reset it for customer views and start fresh every time I want the customer to view my item. This is not the highest priority but would be nice. 

 

Thank you for any help provided!

1 Reply

@jnhalv I don't think there's any way to do this with JSON as it can't check versions and format different columns based on what has changed. But you can achieve this with a simple flow in Power Automate.

 

In this example I'm only checking which of 3 columns has changed, but it would work just as well for 5, 10 or however many columns you want to do the formatting on. The ColumnChanged column is just a single line of text column. 

0-SP-List.png

I've just used the out-of-the-box conditional formatting and set the background color of Arrival to green if the ColumnChanged column is equal to Arrival, the RoomType background color to green if the ColumnChanged column is equal to RoomType and the Departure background color to green if the ColumnChanged column is equal to departure:

0a-SP-Format.png

 

The overall flow looks like this:


0-FlowOverall.png

 

The trigger  is the SharePoint "when an item or a file is modified" and you select your site and list.

The first action is "get changes for an item or a file (properties only). For the ID field select ID from the dynamic content box that appears in the bottom right of the screen when you click in the field. 

 

1-Flow.png

 

For the Since field you can either select Trigger Window Start Token from the dynamic content box or, as you've got versioning turned on, you can click in the field, select the Expression tab and paste in the following expression to check the current version against the previous one:

sub(int(triggerOutputs()?['body/{VersionNumber}']),1)

 

1a-Flow-Expression.png

 

Next for the first column you want to check, in this example it's Arrival, add a Condition and in the left field select Has Column Changed: Arrival leave the middle field as is equal to and in the right field type true.

 

Leave the If no red channel empty. In the gree if yes channel add an "update item" action, select your site and list and select the ID and Title columns from the dynamic content box. For the ColumnChanged field type Arrival:

 

2-Flow.png

 

You can then copy the condition to your clipboard from the 3 dots at the top right of Condition header. Then add an action, select My Clipboard and paste in the Condition, changing the Has Column Changed to the next column, in this case RoomType and the ColumnChanged field to RoomType. Do the same from all the other columns you want the flow to check.

 

Save the flow and then you can test it. I changed the RoomType in Martinique from Double to Family. It can take about 60 seconds for the flow to trigger, add RoomType into the ColumnChanged column and for the background-color to change. You might need to refresh the to see the changes. Back in Power Automate, if you look at the flow history you'll notice that it actually runs twice, the first time on the RoomType change and the second on the ColumnChanged change. This is the result: 

3-SP-List.png

 

Another change, this time to change the Departure column for Cayman Isalnds from 1 July 2023 to 2 July 2023:

4-SP-List.png

 

This is the result:

5-SP-List.png

 

The quickest way to clear the ColumnChanged column and therefore reset the formatting back to the default is just to go into grid view and delete anything in the ColumnChanged column there. 

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User