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.):
    chrome_seGx51N7Zj.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 inventory 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 the locations (ex: Cognito Coffee Locations).

  2. Populate the form entries with the location name and address:
    chrome_gLTVFSF0TX.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.
    mSDXibqpAY.png

  7. Set the Default Choices option, and set the default value to the location that corresponds to customer’s city:
    chrome_l7J7VPD4vb.png

  8. Save and open up your form. Now, the Location field will automatically default to a location depending on the customer’s address.

lookup.gif

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, etc.
    chrome_6opUaYWWAR.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 only appear for internal roles.
    chrome_SOFjcpoTid.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.
    chrome_88Zo8jQjUg.png1. Open up your job application form (ex: Cognito Coffee Job Application).

  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 Job openings form.

  6. Select the field that you want to filter (‘What position are you applying for?’).

  7. Find the Include option in the field settings. Set the filter to only include choices where the position is open:
    chrome_1Egf0dDdof.png

  8. 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.:
    chrome_oYe5Ry3rRZ.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:
    chrome_6AEGYu0Fq0.png1. 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)

  7. 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.

Filter choices on your form using cascading lookups

Cascading (or dependent) dropdowns can help narrow choices on a form depending on which options your users select. For example, on our Cognito Coffee Customer Survey, we can display a list of store locations that changes depending on the city selected. Here’s how it works:

  1. Create a form for your list of stores (ex: Cognito Coffee Locations).

  2. Populate the form entries with your store information (location, city, address, etc.):
    chrome_Crqg1QmXYA.png

  3. Create a customer survey form (ex: Cognito Coffee Customer Survey).

  4. In the builder, click into an empty placeholder and select Lookup from the Add Field menu. In the Lookup field settings, find the Lookup Choices From option and select the locations form. Then, set the Choice Label to pull in the location field:
    Include the list of locations as the Lookup Choice label.

  5. Next, find the Include option and select Add Cascade Filter. Because our store has multiple locations in each city, we’re going to filter our list of locations by city:
    Filter choice option by city.

  6. Once selected, this option will automatically create a separate filter field on our form containing the list of cities:
    chrome_jQn7fl3Oi2.png

  7. Optionally, select the Show This Field - When option to set the location lookup field to only appear once the city field is filled out: =(City != null)
    The location field conditionally appears when the city field is filled out.

  8. Save and open your form. Now, selecting a city will automatically filter the list of store locations:
    lookup-field.gif

Calculate sales tax based on the Address field

You can use the Lookup field to determine a sales tax rate based on a customer’s address, and then apply the tax rate to their order total. In this example, we can calculate the sales tax based on the state entered in the Address field:

  1. Create a form to list all of the tax rates, and populate the form entries with a list of state names and tax rates. If you already have a list of tax rates in Excel, you can use the import feature on the Entries page to easily upload them in bulk.
    Populate the Entries page with the full list of states and tax rates.

  2. Create a form to collect bulk orders (ex: Cognito Coffee Bulk Coffee Order). In the builder, click into an empty placeholder to add a new field. Select Lookup from the Add Field menu. Make sure the Lookup field is set to internal view only.

  3. In the Lookup field settings, find the Lookup Choices From option and select the Tax Rates form. Set the Choice Label option to the Tax Rate field. Then, set the Default Choices option to match the state entered in the Address field on your order form.
    Default choices based on the customer's address.

  4. Add a Calculation field to your form and set it to the Currency type. Here, you can calculate the order total – for example, you can use a simple equation to add up all of the payment fields on your form (ex: =PaymentField1 + PaymentField2 + PaymentField3). Or, if your form has repeating sections, you can add a field labelled ‘Total’ inside of the repeating section to calculate the item total (ex: =Product_Price*Quantity). Then, you can use the .Sum function in your Calculation field to add up every repeating item (ex: =MyOrder.Sum(Total)).

  5. Add a Price field to your form, and set the Amount option to multiply the order total from your Calculation field by the tax rate: =CoffeeOrderTotal*Int32.Parse(Lookup.TaxRate) Here, we’re using the Int32.Parse function to convert the sales tax rate percentage to a decimal. Optionally, set the item name that appears in the order receipt (ex: “Sales Tax”).
    Use a Price field to multiply the order total by the sales tax rate.

  6. Save your changes, and open up your form. Now, the tax rate will automatically be applied to the order based on the customer’s address.