Data - How to find the common rows from 3 or more collections
When working with data, there is often a need to find common rows across multiple data sources or collections.
With two data sources, the process can be relatively straightforward. However, as the number of data sources increases, the complexity of the task also increases.
This post provides a strategy to address this challenge and demonstrates the process using three collections.Example - Creating source collections
Formula to find matching records across 3 collections
With two data sources, the process can be relatively straightforward. However, as the number of data sources increases, the complexity of the task also increases.
This post provides a strategy to address this challenge and demonstrates the process using three collections.
Example - Creating source collections
For this example, let's take the following 3 example collections - Employees, Customers, and Users.
Notice how the "Tim Leung" user appears in all 3 collections. This demonstration will build a formula to identify this user.
ClearCollect(
colEmployees,
{ Name: "Alice Johnson", Email: "[email protected]" },
{ Name: "Bob Smith", Email: "[email protected]" },
{ Name: "Tim Leung", Email: "[email protected]" },
{ Name: "Charlie Brown", Email: "[email protected]" }
);
ClearCollect(
colCustomers,
{ Name: "David Lee", Email: "[email protected]" },
{ Name: "Tim Leung", Email: "[email protected]" },
{ Name: "Eve Adams", Email: "[email protected]" },
{ Name: "Alice Johnson", Email: "[email protected]" }
);
ClearCollect(
colUsers,
{ Name: "Frank White", Email: "[email protected]" },
{ Name: "Grace Green", Email: "[email protected]" },
{ Name: "Tim Leung", Email: "[email protected]" },
{ Name: "Bob Smith", Email: "[email protected]" }
);
Formula to find matching records across 3 collections
To find records that appear in all three collections, we can use the following syntax:
Filter(
colEmployees,
!IsBlank(
LookUp(colCustomers, Name = colEmployees[@Name] && Email = colEmployees[@Email])
) &&
!IsBlank(
LookUp(colUsers, Name = colEmployees[@Name] && Email = colEmployees[@Email])
)
)
The formula takes one of the tables as a base (colEmployees).
It filters this collection and returns records where calling LookUp on the colCustomers and colUsers collections returns a non-blank value. The presence of a non-blank value means the value(s) must also exist in the colCustomers and colUsers collections.
The call to LookUp matches both the Name and Email values. We can modify this to include more or fewer columns.
To verify the result, the screenshot below illustrates how the formula returns the expected record from our source collections (the Tim Leung record).
Conclusion
By using the provided strategy and formula, we can efficiently identify common records across multiple data collections. This method simplifies the process, and we can extend the calls to !IsBlank/Lookup to support a greater number of data sources.
