Filtered lookups

By default, the Lookup field includes all entry data from another form. However, you can use the Include option to filter a subset of choices or even filter choices based on selections that people make on the form.

Create a product catalog

Using the Lookup field, you can easily create a product catalog to track prices and inventory:

  1. Create an inventory form (ex: Cognito Coffee Store Inventory).
  2. Populate the form entries with any necessary details about each item (price, size, available quantity, etc.): inventory-2.png
  3. Create a form to collect payment (ex: Cognito Coffee Store).
  4. In the builder, click into an empty placeholder to add a new field. Select Lookup from the Add Field menu.
  5. In the Lookup field settings, find the Lookup Choices From option and select the Locations form. By default, the Choice Label option should populate with the first text field from the source form – in this case, the ‘Product Name’ field.
  6. Save and open up your form. Now, when you make changes to your product catalog, the inventory on your order form will update automatically.

Set smart defaults for your fields

Set smart defaults for your form fields using data from selected lookup entries. In this example, we can set a default order pick-up location for a bulk coffee order based on the customer’s address:

  1. Create a form to list of the locations (ex: Cognito Coffee Locations).
  2. Populate the form entries with the location name and address: locations.png
  3. Create a form to collect bulk orders (ex: Cognito Coffee Bulk Coffee Order).
  4. In the builder, click into an empty placeholder to add a new field. Select Lookup from the Add Field menu.
  5. In the Lookup field settings, find the Lookup Choices From option and select the Locations form.
  6. By default, the Choice Label option should populate with the first text field from the source form – in this case, the Location field. This Lookup field will allow customers to select their pick-up store location based on the list of locations. pick-up.png
  7. Set the Default Choices option, and set the default value to the location that corresponds to customer’s city: filtered-location.png
  8. Save and open up your form. Now, the Location field will automatically default to a location depending on the customer’s address.

Capture lookup values on your form

Capture a point-in-time lookup value that won't change when lookup entry data changes. For example, let’s say that the price of coffee changes based on the market place. By copying the lookup value, you can capture the original purchase price in case of a price change and/or a refund:

  1. Create an inventory form (ex: Cognito Coffee Store Inventory).
  2. Populate the form entries with any necessary details about your suppliers, including country of origin, number of pounds, cost per pound, and the number of pounds available: entries.png
  3. Create an order form that references the inventory form (ex: Cognito Coffee Bulk Coffee Order).
  4. In the builder, click into an empty placeholder to add a new field. Select Lookup from the Add Field menu.
  5. In the Lookup field settings, find the Lookup Choices From option and select the Inventory form. By default, the Choice Label option should populate with the first text field from the source form – in this case, the ‘Origin’ field.
  6. Add a Textbox field to your form labelled ‘Per Pound Price Paid’. Set the default value to: =PricePerPound
  7. Make sure to set the Per Pound Price Paid field to internal view only. internal-view.png
  8. Save and open up your form. The ‘Per Pound Price Paid’ field will reflect the price per pound from the original purchase, and won’t change - even when you update the prices in your inventory.

Hide inactive options by filtering your choices

What happens when an item goes out of stock, or a job opening is no longer available? Rather than deleting data, you can use the Include option to simply change the included choice values at any time. Plus, even when you adjust the filter, the previously selected choice value will still be there when you update the entry.

Let’s say you have a job application form. Using the filter, you can filter out positions that are no longer open:

  1. Create a form to list job openings (ex: Cognito Coffee Job Openings).
  2. Include a Yes/No field to indicate if the position is active.
  3. Populate the form entries with job details, including the position and whether it’s open or not. jobs.png
  4. Open up your job application form (ex: Cognito Coffee Job Application).
  5. In the builder, click into an empty placeholder to add a new field. Select Lookup from the Add Field menu.
  6. In the Lookup field settings, find the Lookup Choices From option and select the Job openings form.
  7. Select the field that you want to filter (‘What position are you applying for?’).
  8. Find the Include option in the field settings. Set the filter to only include choices where the position is open: position-open.png
  9. Save and open up your form. Now, the ‘Position’ dropdown will only display positions that are currently open.

Filter lookup choices by date

With the ability to filter lookup choices by date, you can display values only available in the future or even between specific time periods. For example, let’s say our Cognito Coffee Store offers special holiday discounts:

  1. Create a form to list your coupon codes (ex: Cognito Coffee Discount Codes).
  2. Populate the form entries with details about each discount code, including the code text, discount rate, start date, end date, etc.: discounts.png
  3. Create an order form (ex: Cognito Coffee Store).
  4. In the builder, click into an empty placeholder to add a new field. Select Lookup from the Add Field menu.
  5. In the Lookup field settings, find the Lookup Choices From option and select the discount codes form. By default, the Choice Label option should populate with the first text field from the source form – in this case, the ‘Code Text’ field.
  6. Find the Include option in the field settings. Set the filter to only include choices where the start date is in the past, and the end date is in the future. Then, find the Default Choices option and follow those same steps: coupon.png
  7. Add a Textbox field labelled Coupon Code. Using the Show Custom Error option in the field settings, set an error message (ex: “Uh-oh. Looks like your code may be expired. Please check for typos and try again!”) to display when the code entered doesn’t match up with the available code from the Lookup field: =(CouponCode != null and CouponCode != AvailableDiscountCodes.CodeText)
  8. Add a Price field labelled Discount, and set the Amount value to the following expression: =if CouponCode = AvailableDiscountCodes.CodeText then -Products.Sum(ItemTotal) * AvailableDiscountCodes.Discount else 0 This expression evaluates whether the discount code a customer enters matches the discount code currently available. If the code does match, then the discount is applied to the order. Learn more about creating coupons and discounts.