SharePoint Document Issues

How to Automatically List SharePoint Customers by Product

Kushani Nimanthika Sep 30, 2026 869 views

Question details

The user needs to automatically match and list customer contacts for specific products across different SharePoint lists.

How to Automatically List SharePoint Customers by Product

Product: Microsoft SharePoint
Device & OS: not provided
Scenario: Aggregating customer contacts based on product matches across multiple SharePoint lists.
Observed behavior: SharePoint's built-in calculated columns and JSON formatting cannot reliably compare values across different lists or aggregate matching contacts into a single column.

Before you start

Ensure you have adequate permissions to create and manage Power Automate flows within your Microsoft 365 environment, and verify that both your Products and Customers lists are fully populated.

Solution 1Recommended

Create a Power Automate Flow to Match and Update Contacts

Since built-in SharePoint features cannot cross-reference lists, creating a Power Automate flow is the recommended method to dynamically aggregate customers by product.

SharePoint calculated columns and JSON formatting are limited to the current list's context. A Power Automate workflow acts as a bridge, retrieving data from one list and automatically updating another whenever a product change is detected.

Trigger the Flow on Product Changes

Go to Power Automate and create an Automated cloud flow. Select the trigger 'When an item is created or modified' and point it to your SharePoint Products list.

Retrieve Matching Contacts

Add the 'Get items' action for your Customers list. Use an OData filter query to return only the contacts where the product field matches the product value from your trigger.

Aggregate and Update the Column

Use a 'Select' or 'Join' data operation to format the retrieved customer names into a single string. Then, add the 'Update item' SharePoint action to write this aggregated string into the CUSTOMERS column of your target list.

Seek Community Help for Advanced Implementation

If you encounter complex formatting or logic issues, visit the Microsoft Power Automate Community (powerusers.microsoft.com) for specialized workflow implementation guidance.

Create a Power Automate Flow to Match and Update Contacts

Power Automate Licensing: Standard SharePoint triggers and actions are generally included in your standard Microsoft 365 subscription and do not require premium standalone Power Automate licenses.

Free Microsoft Office alternative

Enhance Your Document Productivity with WPS Office

While SharePoint and Power Automate handle complex list automation, managing the actual exported reports and documents is much easier with WPS Office. As a free, lightweight, and fully compatible Office alternative, it helps you seamlessly edit Word, Excel, and PDF files without heavy subscriptions.

1. Download WPS Office: Visit the official WPS website and click 'Download WPS Office Free'.
2. Install the Software: Run the lightweight installer and follow the on-screen prompts to complete the setup.
3. Open Microsoft Formats Seamlessly: Launch WPS Office and directly open your exported SharePoint .xlsx or .docx files with perfect formatting compatibility.

Highly compatible with Microsoft Excel (.xlsx) and Word (.docx) formatsLightweight installation with a familiar, easy-to-use tabbed interfaceBuilt-in PDF editing tools for comprehensive document managementFree alternative to costly office suite subscriptions

microsoft office alternative - wps office

Frequently Asked Questions

Can I use SharePoint calculated columns to pull data from another list?

No, SharePoint calculated columns only support formulas based on other columns within the exact same list. They cannot cross-reference or look up data from external lists.

Does JSON column formatting allow me to aggregate items?

JSON formatting can change how data is displayed visually within a specific view, but it cannot fetch external list data or perform complex aggregations across multiple contacts.

Where can I get help building complex Power Automate flows for SharePoint?

You can find specialized workflow implementation guidance, troubleshooting tips, and user-submitted templates by visiting the official Microsoft Power Automate Community forum.

Document & File Issues