In this post, I want to expand on a technique I shared last week: using the values in one single select menu to dynamically populate the values in a second single select. In Part 1, I did this with table queries and aggregations. In this post, I’ll use the Tulip API.
In broad strokes, here’s what you need to do:
- Create a new bot and retrieve your Tulip API credentials (or use and existing one)
- Create a new connector and configure it using the credentials
- Create a connector function that uses a GET command to retrieve records from the desired table
- Configure a connector input to take the value selected in the first single select menu
- Add a query parameter to filter the desired fields based on the inputs of your connector function
- Add a connector output to return a list of values you’d like to populate the second single select
- Add a trigger to your first single select to run your connector function
It might look like a lot from the list, but the only really tricky part is getting your query parameters right. So let’s jump right in and look at how to do it.
Configure Your Connector
Go ahead and configure a new connector for the Table API, or use an existing connector.
The basics of the Tulip API and connectors are outside of the scope of this post. Connectors and the API are covered in Tulip University, and are well represented in the knowledge base.
If you need to find Tulip’s API documentation, you can access it here.
Create a New Connector Function
Fundamentally, this technique is using the table API to pull a list of records from a table, and then using query parameters to filter the query results down based on user input. That means that we need to use the GET List of Records from a Table query.
The URL syntax for this query is as follows:
The only path parameter you’ll need to configure is the {tableId}. In between the two slashes, replace {tableId} with your table’s unique ID, which you can find in the table’s URL.
Your full URL should look something like this in your connector function:
Structure the Connector Function’s Outputs
Start by testing your connector’s outputs by clicking the TEST button in the top right. Take a moment to look at the JSON object the call returns. It should resemble the structure of your table. For me, that looks something like this:
In this case, what I’m interested in doing is returning a Part Number based on a Part Class, and in the query that equates to using an entries value in the “eubmc_value” field to filter a list of “lbpur_number” results. So the output we’re ultimately interested in is the value in “lbpur_number”.
Once you’ve tested the function and returned results, click on the field you’re ultimately interested in filtering for in the application. It should look as follows:
THIS IS SUPER IMPORTANT, YOU’RE NOT DONE YET: You need to make two critical adjustments to this output.
- You need to change the output type so that it’s a list
- You need to adjust the body of the return so that it returns all values, not just the first item in the series
To do this, click on the datatype symbol in the output and select list using the slider. In the body field, just erase the “0.” at the beginning of the body. When done, it will look like this:
At this point, your connector function will return every value in the selected output field.
Add Query Parameters to Filter by App Input
At this point, we’re ready to add new query parameters to filter our output based on the value that a user selects. To do this, we’ll need to do two things:
- Add an input field in your connector function that will correspond to the input in the application
- Add query parameters to check if the input value exists in any of the records returned by the API call.
First, add an input to your query. I’m going to call my input “Consumable” because the user’s use-case here was inventory management, but give it any name you’d like. Make sure the type matches that datatype of the input in application.
Next, let’s add query parameters.
The fully configured version looks like this:
Let’s take a second to unpack what’s happening here. Query parameters of this type need three fields to be complete, 1.) “field” in the JSON object to reference, 2.) an argument (our input), and, 3.) a function type. The list of available filter operations and function types can be found in the Tulip API documentation. For my purposes, I want to filter on the value field, using an argument $Consumable$, and I’m testing for equivalence.
To get the value in your argument field to highlight in grey (and thus be available for use in an app), enclose the text using the $ symbol. For me, that would read $Consumable$. Be careful, the text needs to be an exact match, including sensitivity. If the argument matches the text in the input, you’ll see the text snap to the new, bolded formatting.
This connector function returns the desired, filtered results, as you can see in this screenshot:
Configure the Inputs in your application
For this application, you’ll need two single select menus. For the first single select menu, configure it such that the user’s options are fed by a unique values table aggregation on the field you’d like to use as your filter condition. For me, that meant creating a unique values aggregation for the Class of Parts column. If you need a refresher on getting unique values from table aggregations, it’s covered in Part 1.
The next thing you need to do is write a trigger that runs the connector function when a user selects an option. To do this, highlight your first single select menu, and add a trigger in the widget pane.
For me, that’s a simple one line trigger. Choose to RUN CONNECTOR FUNCTION, select the function you’ve built, and then make sure your input variable matches the variable assigned to your first single select. Save your result into a new variable.
NOTE: if your connector return is complex (e.g. multiple fields, object list type), you may have some additional steps to get your data into a useable format. Let me know if there’s interest here and I can cover it in another post.
If we go to the variables menu, we can see that the new variable matches the output structure of our connector.
The last thing we’ll need to do is set the second single select menu to populate with the output of the connector function. Because our variable is already structured to give us the Part Numbers we’re interested in, we just need to select our desired connector output from beneath the broader object.
At this point, your app should be properly configured, and your single selects populating as intended.