Lookup Another Collection

Doc Auto Team
Doc Auto Team
  • Updated

The lookup function allows us to access collections within another collection.



From within a collection, use the lookup function and a shared unique variable to connect the two collections.

{#collection 1}{#collection 2 | lookup:shared unique variable | [insert filters] | [insert functions]}{/}{/}

Example 1:  Amalgamation

Amalgamating Company #1:  Accounting LLC
Principals: Ben Wyatt - Chief Executive Officer
Amalgamating Company #2: Citi Bank LLC
Principals: Leslie Knope - President
  Ron Swanson - Vice-President


Collection #1: entities
shared unique variable: entityID entityName
6307a94aafa5f90a9f67ba74 Citi Bank LLC
6307a95dafa5f90a9f67ba7d Accounting LLC


Collection #2: affiliations
shared unique variable: entityID participant_fullName title
6307a94aafa5f90a9f67ba74 Leslie Knope President
6307a94aafa5f90a9f67ba74 Ron Swanson Vice President
6307a95dafa5f90a9f67ba7d Ben Wyatt Chief Executive Officer

Desired Result:

If we want to find and list the officers within each amalgamating entity, we would start by opening the entities collection to identify the different entities that are now amalgamating together and then use the shared unique variable, entityID, to identify the officers and their positions from the affiliations collection for each entity.


 {#tasks | grab:1:'amalgamatingEntities'}{entityName}

Name Title
{#affiliations | lookup:entityID | status:'confirmed' | filter:'role':'Officer'}{participant_fullName} {title}{/}



City Bank LLC

Name Title
Leslie Knope President
Ron Swanson Vice-President

Accounting LLC

Name Title
Ben Wyatt Chief Executive Officer


Example 2: Shareholdings

In this use case, we will begin with the shareholdings collection and pulling the shareholding name and number of shares and then pulling the class name and voting rights from the shareclasses collection.  Below is the sample share data:


Collection #1: shareholdings
shared unique variable: shareclassID shareholdingName numShares
234f0g9lq Michael Scott 500
234f0g9lq Dunder Mifflin Paper Co. 1000
234f0g9lq Jim & Pam Halpert, jointly 250
Darryl Philbin 100


Collection #2: shareclasses
shared unique variable: shareclassID className votingRights
234f0g9lq Common true
581sfp8hd Preferred false
vf0456kfs Special false

Desired Result:

In sentence format, the name each shareholder, followed by the number of shares, voting rights and class of shares they hold.


{#shareholdings}{shareholdingName} holds {numShares | commaSeparate} {#shareclasses | lookup:shareclassID}{#votingRights}voting {/}{#!votingRights}non-voting {/}{className} shares.{/}


Looping the shareholdings collection, insert the name of each shareholding, the number of shares, the voting rights, and the name of the class they hold.  The lookup function will connect these two collections by the shareclassID (shared unique variable).  This allows you to pull the relevant data that is pertinent to each specific shareholder that is contained in the shareclasses collection. 


Michael Scott holds 100 voting Common shares.

Dunder Mifflin Paper Co. holds 1,000 voting Common shares.

Jim and Pam Halpert, jointly holds 250 voting Common shares.

Darryl Philbin holds 100 non-voting Special shares.



Related to