1

I'm pretty new to JSON.

I have a SharePoint list with employee schedules (shifts) as seen below: enter image description here

My OnClock column uses the following to determine if (now) time is within the stipulated shift start and end times. This works great.

JSON

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if((Number(@now)-21600000)/1000%86400>Number(Date([$Time])-28800000)/1000%86400,if((Number(@now)-21600000)/1000%86400<Number(Date([$EndTimeNeeded])-28800000)/1000%86400,'Yes','No'),'no')"
}

However I'm having trouble since Flow does not "pick up" the value. As a matter of fact if I open an item up --> OnClock is otherwise empty ( "-" ).

enter image description here

Is it possible to have the value that's visually displayed actually write to the true value of the item so Flow can read it?

Thanks in advance.

Improve this question

2 Answers 2

Reset to default
1

It's not possible to have the value displayed in JSON formatting actually write to the true value of the item.

As a workaround, I would suggest you use calculated column instead.

Improve this answer
1
  • Thanks for confirming, can you show me an example of how to create a calculated column to replicate what I'm trying to accomplish to have Flow read it's value? Commented Dec 11, 2020 at 15:05
0

This is not possible using JSON formatting. Using JSON formatting, you can only show/display the value and you cannot save it to the list.

Possible workarounds:

You can manage the calculation using either of below:

  1. By creating calculated column and using its value in flow.
  2. Directly calculate the value based on dates in flow. You can use it in flow as well as you can update the value back to SharePoint.

Update from comments:

Check below links as references for calculated columns in SharePoint:

  1. Date and time formulas
  2. Examples of common formulas in lists
Improve this answer
5
  • Thank you for confirming. Can you show me an example of how I can go about creating a calculated column to replicate what I'm trying to do? I'm under the assumption it would be in fact easier to have a calculated column and have Flow use it's value than doing the math in Flow. Thanks again. Commented Dec 11, 2020 at 15:01
  • Check my updated answer for reference links. Your calculated column formula will be something like: =IF(AND(TODAY() > [Start Date], TODAY() < [End Date]), "Yes", "No"). Replace column names with your actual column names (use display names, the one which you see in list/table view). Commented Dec 11, 2020 at 17:17
  • (TODAY) takes date into consideration also right? How can I only have it take the Time into consideration? My [Start Date] and [End Date] have dates in them but they can be ignored. Just wanted to consider time stamps. Ex: (7:00 AM), (4:00 PM). Commented Dec 11, 2020 at 17:35
  • Check "Date and time formulas" link in my answer. you can calculate the difference between two times as well. Commented Dec 11, 2020 at 17:39
  • Right, but I want to ignore the date completely. My column needs to be of type Date and Time but the Date value needs to be otherwise irrelevant. How can my calculated column ignore the fact that there's a Date in [Start Date] and [End Date]? I read the documentation and aren't able to wrap my head around this. Commented Dec 11, 2020 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.