# How to use lookup tables
CalcBuilder supports lookup tables. This tutorial will show you how to:
- Upload your own tabular data
- Sort the data
- Filter the data (either by constant or dynamic values)
- Assign looked up values to variables for use in subsequent calculation steps, or to return to the user
# Creating the table
The first thing we will need to do is to create a table. CalcBuilder supports TSV tables. These can be created using Excel. Start by opening a new Excel worksheet and in your first row write the names of your columns. In the second row we will be putting the data type for this column. These relate to CalcBuilder card types and are defined by one of these 4 names:
- text
- number
- boolean (for true/false)
- integer
Once we have the headings and the data types we need to fill it with data. Make sure to do this following the data type specified in the second row. See below for an example of how to layout your table
| Rank | Colour |
|---|---|
| integer | string |
| 1 | Orange |
| 2 | Purple |
| 3 | Yellow |
| 4 | Pink |
| 5 | Red |
| 6 | Green |
| 7 | Blue |
Once you have completed your table you will want to save it. To do this, make sure to select Tab-delimited Text (.txt) from the file format dropdown when saving.
# Add table to calculation
Now that we have a table we can add it to our CalcBuilder calculation. Insert an add table card into your calculation + > Add tabular data > Add new table. Name your table appropriately and select the file you just created from the file picker.
# Use a table
Now our table has been added to the calculation we can use it. Add a use table card and select your table + > Add tabular data > Use a table
To check if our table is working correctly we can now insert a show table card within the use table block. + > Add tabular data > Show a table
And there it is! Now lets try and output the top value from the table. Add a variable assignment card in your table block + > Add tabular data > Assign a variable Give the value a symbol, select the column you want to output and add a description if desired.
If we now check the report we'll see our top row value has been assigned to the variable
TIP
Tables always use the top row when you assign them to a variable
If we wanted to export the bottom value instead we could add a sort table card + > Add tabular data > Sort the table. Select the column you want to sort by and direction then the output will change
You will notice that the report has been sorted as well
You can also filter the table down to select a subset of information, you can do this by adding a filter table card + > Add tabular data > Filter the table
And now the table has been filtered and the report updated to match.
Now you have all the tools you need to start using tables in your CalcBuilder calculations