Format Maximum Date in Multi-Value SharePoint List Lookups
Question details
The user needs to dynamically format a Required Start Date column in red if it is earlier than any predecessor due date retrieved from a multi-value lookup.
- Product
- Microsoft Lists
- Device & OS
- not provided
- Scenario
- Applying custom column formatting to a Microsoft List where multi-value lookup dates must be individually compared against a start date.
- Observed behavior
- Due dates from a multi-value predecessor lookup appear as a single semicolon-delimited string, which prevents standard date comparison rules from evaluating each date accurately.
Ensure you have Design or Edit permissions for the Microsoft List or SharePoint library so that you can access the Column settings and apply custom JSON formatting.
Use Column Formatting JSON with the Split Function
Apply a custom JSON schema to the list column that uses the split function to separate the delimited dates and evaluate them individually in real-time.
By utilizing the split function in SharePoint column formatting, you can iterate through the semicolon-delimited string of predecessor dates directly within the list view. This allows the formatting to apply instantly without waiting for background workflows to update the items.
Navigate to your Microsoft List, click the column header for the 'Required Start Date', and select 'Column settings' followed by 'Format this column'.
At the bottom of the column formatting pane, click on 'Advanced mode' to enable custom JSON input.
Paste your custom JSON schema. Use the 'forEach' property with the expression "choice in split(@currentField, ';')" to break the delimited string into individual date elements.
Within the loop's style configuration, add a background-color condition to compare the dates. For example: "=if(Date([$choice]) > Date([$Start Date]), '#ff0000', '')". This will apply the red highlight if the condition is met.
Click 'Save' at the bottom of the formatting pane. Refresh your list view to verify that the start dates correctly highlight in red when earlier than any evaluated predecessor date.
Process Multi-Value Dates Using Power Automate
For more complex business logic or when you need to physically store the maximum date in a separate column, use an automated Power Automate flow.
Experience Seamless Document Management with WPS Office
While Microsoft SharePoint and Lists handle advanced database formatting, WPS Office provides an outstanding, lightweight alternative for your daily document, spreadsheet, and presentation needs. It empowers you to effortlessly view, edit, and collaborate on essential office files without the heavy subscription fees.
Frequently Asked Questions
Why do my multi-value lookup dates appear as a single string?
By default, Microsoft Lists and SharePoint output multi-value lookups as a single continuous string separated by semicolons in the list view. To evaluate each item individually, the string must be parsed or split.
Can I use standard conditional formatting rules for multi-value columns?
No, the standard point-and-click conditional formatting interface does not support splitting and iterating over multi-value strings. You must use 'Advanced mode' to write custom JSON schema.
Is it possible to use Power Apps instead of JSON column formatting?
Yes. If you are customizing the Microsoft List form using Power Apps, you can utilize Power Fx functions like Split() and Max() to parse the semicolon-delimited values and evaluate the lookup data directly within the customized form.
