Add a Lookup Field

Create a New Lookup Field

You can create a lookup field from any field in your chart's data source which is not an embedded object and which contains matching data with a field in another collection. The remote collection must be:

• An existing Charts data source.

• Part of the same database as the local collection.

To add a lookup field, mouse over an existing field and click on the ellipsis (...) to the right of the field name. Select Lookup field from the dropdown menu. A modal window appears:

click to enlarge

Select the desired collection and field from the dropdown menus. The remote field must contain at least one document with data that matches the local field, or the lookup field will be empty.

You have the option to either return all matching documents from the foreign collection or only the first matching document. Returning all matching documents is recommended for one-to-many relationships, such as parent/child and primary key relationships. Returning only the first document is recommended for one-to-one and many-to-one relationships, such as reference data codes. If you want to return only the first matching document, check the Return only first matching document radio button.

Charts suggests a name for the new field, but you can enter a name of your choosing if you prefer. Click Save to create the new field.

The new field appears with a binoculars icon, indicating that it is a lookup field.

To remove a lookup field from your field panel, mouse over the lookup field and click on the ellipsis (...) to the right of the field name. Select Remove field from the dropdown menu.

Example

The following example uses two data sources, one called product_catalog and one called orders.

The product_catalog collection contains the following documents:

{ "_id" : 76234, "item" : "21 inch monitor" }
{ "_id" : 38921, "item" : "USB C cable" }
{ "_id" : 21167, "item" : "keyboard" }
{ "_id" : 90252, "item" : "60 GB external hard drive" }

The orders collection contains the following documents:

{ "_id" : 1, "sku": 38921, "quantity": 50 }
{ "_id" : 2, "sku": 21167, "quantity": 75 }
{ "_id" : 3, "sku": 76234, "quantity": 15 }
{ "_id" : 4, "sku": 21167, "quantity": 20 }

Records in the orders collection use the sku field to reference the _id field in the product_catalog collection.

The goal is to create a column chart showing the number of ordered items. The following chart uses orders as its data source. The lookup field sku_lookup_product_catalog is created from the orders.sku field. It uses the product_catalog collection as its remote data source and product_catalog._id as its remote field.

The chart uses product_catalog.item as its X axis and orders.quantity as its Y axis.

click to enlarge