Sharepoint formatting using lookup column

Question

I'm trying to format a list in sharepoint, based on a column that is retrieved from another list (and is not the main lookup column). The main list is formatted based on the same conditions, and it works just fine, but when I try on the secondary list, the result is that all rows are formatted in red.

So far, I've tried something like this:

{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json","schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json","additionalRowClass": "= if([$ProjectName_TargetDate.lookupValue]>@now &&[$ProjectName_TargetDate.lookupValue]<=@now+7776000000,'sp-css-backgroundColor-BgRed sp-field-fontSizeMedium sp-css-color-BlackText', if([$ProjectName_TargetDate.lookupValue]>@now+7776000000 && [$ProjectName_TargetDate.lookupValue]<=@now+15552000000,'sp-css-backgroundColor-BgGold sp-field-fontSizeMedium sp-css-color-BlackText',if([$ProjectName_TargetDate.lookupValue]>@now+15552000000,'sp-css-backgroundColor-BgGreen sp-field-fontSizeMedium sp-css-color-WhiteText','sp-css-backgroundColor-BgGray sp-field-fontSizeMedium sp-css-color-WhiteText' )))" }

Is there any other way to do it?

Answer 1

Hi Anaid,

We have tested from our side. We can reproduce the same behavior in the secondary list as you described. We are afraid it's the expected behavior. Thanks for your understanding.

If you are willing, you may try to use Microsoft Power Automate flow to get data in the second list, not use lookup column. Then set view formatting for the secondary list.

To make sure you get the dedicated assistance about flow solution, we sincerely recommend you post the question in Microsoft Power Automate Community, it is the specific channel handling flow related queries, members and experts are professional and will assist you further.

Thanks for your understanding and patience so far.

Have a nice day and stay safe!

Best Regards,

Tina

Answer 2

Hi Anaid,

Thanks for posting back and sharing the detailed information with us.

I go through the post carefully and understand the issue is that when you try the same formatting rules in the secondary list, it doesn't work. The secondary list has lookup column to get data from the main list.

Please allow us some time to test lists from our side. If we get any update we will post back and let you know.

Appreciate your patience and understanding.

Best Regards,

Tina

Answer 3

Hello,

In my main list I have the data below formatted correctly:

based on Target Date:

-when the Target Date is in the past or today, the color is grey

-when the Target Date is within the next 3 months, the color is red

-when the Target Date is in the interval (Today+3 months,Today+6 months], the color is yellow

-when the Target Date is more than 6 months in the future from today, the color is green.

In my secondary list, the column Project Name is the lookup column, and the data for Customer Name and Target Date is retrieved from the main list.

I've applied the same formatting rules as in my first list, based on Target Date, but the result is:

Answer 4

Hi Anaid,

Per the description shared, I would like to request you to share detail condition of your requirement with some sample screenshot of your list data so we can try our best to create a condition at our end and share it with you.

a. Sample screenshot of your data.

b. Conditions for Output requirements to format the column.

Best Regards,

Chitahaas