Blog

Data - How to find the common rows from 3 or more collections

In an app with multiple collections, how can we find identical rows across three or more collections? This post describes the formula that we can use.

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

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: "alice.johnson@example.com" },
    { Name: "Bob Smith", Email: "bob.smith@example.com" },
    { Name: "Tim Leung", Email: "tim.leung@example.com" },
    { Name: "Charlie Brown", Email: "charlie.brown@example.com" }
);

ClearCollect(
    colCustomers,
    { Name: "David Lee", Email: "david.lee@example.com" },
    { Name: "Tim Leung", Email: "tim.leung@example.com" },
    { Name: "Eve Adams", Email: "eve.adams@example.com" },
    { Name: "Alice Johnson", Email: "alice.johnson@example.com" }
);

ClearCollect(
    colUsers,
    { Name: "Frank White", Email: "frank.white@example.com" },
    { Name: "Grace Green", Email: "grace.green@example.com" },
    { Name: "Tim Leung", Email: "tim.leung@example.com" },
    { Name: "Bob Smith", Email: "bob.smith@example.com" }
);

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.
  •   Categories: 
  • data
Previous
Next
Related posts