SharePoint list - Column-Formating mit JSON

Question

SharePoint list - Column-Formating mit JSON

Enrico Kreisig 26

Hello everyone,

i have a question, I want to use the following code to format column_B red in a SharePoint list if it is more than 14 days after the date in column_A.

When I apply the code to date type columns, the code works fine. However, if a column is a lookup column that gets its date value from another table, the code no longer works. Probably I need to convert the value looked up to a date value first?

How do I do that? Can someone help?

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField.lookupValue",
"attributes": {
"class": "=if([$Spalte_A] + 1209600000 < [$Spalte_B], 'ms-fontColor-redDark ms-fontWeight-semibold', '')"
}
}

Xuyan Ding - MSFT 7,601 Reputation points

2022-05-31T02:31:18.227+00:00

This is a quick note to let you know that we are performing research on this issue.

4 answers

Your answer

Xuyan Ding - MSFT 7,601 Reputation points

2022-05-31T02:31:18.227+00:00

This is a quick note to let you know that we are performing research on this issue.

Answer 1

Hi @Enrico Kreisig ,

Change the return value of the lookup type to date, and then add 14 days through the addDays function as a comparison condition with another column.
1.If A is a column of type lookup, the code is as follows:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"color": "=if(Date (@currentField) > addDays(Date([$A.lookupValue]), 14), '#ff0000', '')"
}
}

2.If B is a column of lookup type, you can make corresponding conditional changes.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Enrico Kreisig 26

Thanks very much!

Unfortunately the code doesn't work.
The formatting is applied although the condition is not met.

Xuyan Ding - MSFT 7,601 Reputation points

2022-06-02T04:59:01.28+00:00

Hi @Enrico Kreisig ,

Per my test, the column displayed directly in the form of lookup:date does not meet the conditions when its column name is used, so I suggest that you could change the idea as an alternative and create a lookup date column with a custom column name , use this column when referencing, so there will be no problem. Of course, the troublesome thing about this is that after editing and filling in lookup:name, the lookup date column still needs to be manually selected.

Answer 3

Enrico Kreisig 26

Too bad I can't do it.
But many thanks for the help.

Xuyan Ding - MSFT 7,601 Reputation points

2022-06-06T03:36:38.9+00:00

Maybe you could create a new test list and try again.
Multiple non-create/delete modifications to a list may cause some problems.
Xuyan Ding - MSFT 7,601 Reputation points

2022-06-07T06:02:50.533+00:00

Hi @Enrico Kreisig ,

Please confirm with you again, whether you have created a new list or column to try, the lookup column must be directly added as the lookup column, and the reference column shown in your previous screenshot (column_A is not in the form of Name:A) ) is not possible.
After my many tests, the code works:
Enrico Kreisig 26 Reputation points

2022-06-07T09:06:01.627+00:00

Thank you very much for your support!

I have a new setup:
List 1: (testInfo)
column: (Title) type single line
column: (InfoDate) type Date

List 2: (testOverview)
column: (Name) type lookup > (Title) > (testInfo)
column: (Name:InfoDate) type lookup > (InfoDate) > (testInfo)
column: (Start) type Date
column: (testDate) type Date

JSON in column 'testDate' works fine:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"color": "=if(Date([$testDate]) > addDays(Date([$Start]), 14), 'red', 'green')"
}
}

JSON in column 'Name:InfoDate' it does not work:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"color": "=if(Date([$Name_x003a_InfoDate.lookupValue]) > addDays(Date([$Start]), 14), 'red', 'green')"
}
}
Xuyan Ding - MSFT 7,601 Reputation points

2022-06-08T09:25:30.85+00:00

Hi @Enrico Kreisig ,

Maybe I need to add some pictures to better match your understanding of my previous description.
1.code works
Create a lookup column of infoDate directly.

2.code invalidation
Like you do: Create a lookup column of Name, after checking infoDate.
Enrico Kreisig 26 Reputation points

2022-06-11T08:32:49.54+00:00

Thanks very much!

I do not get it:
In columns of type lookup, the formula with the less-than sign works:

In columns of type date, the formula with the greater-than sign works:

I will keep watching.
Xuyan Ding - MSFT 7,601 Reputation points

2022-06-13T01:29:12.6+00:00

Hi @Enrico Kreisig ,

I'm glad to hear that your issue has been resolved, although it may not be that perfect.

If this answer helpful to you, please remember to accept it. It will help others who meet the similar question in this forum. Thank you for your understanding.
Enrico Kreisig 26 Reputation points

2022-06-15T06:47:24.023+00:00

Unfortunately the problem is not solved.
the function is buggy.

Answer 4

Enrico Kreisig 26

Can that be the problem?

https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/list-form-conditional-show-hide

Share via

SharePoint list - Column-Formating mit JSON

4 answers

Your answer