Table variables store formulas related to a table that can be used throughout the knowledgebase. They are useful for creating custom summary reports and items on the Dashboard, such as Numerical Result Widgets.

The table variable library is located at Setup > System > Manage Table Variables. This screen contains all table variables and allows you to create new variables. Whenever a table variable is created elsewhere in the system, it appears in this library where it can be edited or deleted. You can also view existing table variables from the Global Variables tab of the Formula Help wizard.

Table variables use a combination of saved searches and formulas to calculate a single value that can be used in other system-wide operations. In cases where a saved search is used, the search finds a set of records that are then acted on by a formula to calculate the result as a number value. Otherwise, the formula calculates a number value based on all records in a table.

Create a Table Variable

To create a new table variable:

1. Click the Setup gear in the top-right corner and go to System > Manage Table Variables.
2. Click New.
3. Enter the variable name. The name cannot contain spaces or special characters.
4. Select the table to which the variable applies.
5. Choose whether the variable uses all records in the table or only those filtered by a saved search. If using a saved search, you can select an existing search or create a new one.
6. Choose an option for the formula:
   • Record Count returns a value equal to the number of records.
   • [Calculation] of field [Field Name] performs a calculation based on the value(s) in a chosen field. Choices include the average, minimum, maximum, total, or standard deviation.
     

7. Select whether or not the system interprets null values, or empty fields, as zero. Interpreting a null value as zero includes the value in the data set as zero; otherwise, it's excluded. For instance, imagine you're using a field's value in four records to calculate an average: 10, 10, 20, and null. If you interpret null values as zero, the system calculates an average of 10. Otherwise, it calculates an average using only the first three records and returns a value of about 13.3.

   This option has no effect on calculating the total of a field's value or when selecting the Record Count option.

8. Click Generate Variable. The system populates the variable into the text field.
9. Click Finish. The variable now appears on the Global Variables tab of the Formula wizard, where you can add references to it as needed.

Use Case: My Assigned Contracts

Consider a situation where you want to display the number of contracts assigned to the logged in user. Maybe you want to display the number with a Numerical Result widget on the user's dashboard. Let's step through the process of creating a table variable to accomplish this.

1. From the Manage Table Variables screen, create a new variable with a name like "ContractOwnerMe" or something similar. Remember, the name cannot contain spaces or special characters. If you want to use spaces, consider using underscores.
2. You also need to create a new saved search to find the appropriate records. To find only contracts assigned to the logged in user, create the search with the Contract Owner field equal to the logged in user's record ID, which uses the $global.my_id variable.
   
3. For the formula applied to the saved search, you want to return the total number of records that meet the saved search criterion, so select the Record Count option. Skip the next section for interpreting null values as zero because it has no effect on record counts.
   
4. Click Generate Variable to populate the text box with the table variable. The variable contains your saved search inside the formula for counting the total number of records. 
   
5. Click Finish.

When creating your Numerical Result widget, you can now click Choose Variable on the Options tab and find the variable you just created.