Filtered vs. Dynamic Lists

Let's say you have tables like items, companies, and item_companies. Your goal is to display customers only for revenue items and suppliers only for expense items.

A typical query for listing companies looks like this:

SELECT id, name FROM companies

If your data query includes a parameter like @item_id, you can incorporate it into the value list. For example, the following SQL command demonstrates this:

SELECT c.id, c.name FROM companies c INNER JOIN item_companies ic ON ic.company_id = c.id WHERE ic.item_id = @item_id

This is an example of a dynamic list. SaveToDB products reload these lists whenever a user changes the @item_id parameter.

Another scenario involves having the item_id column in the selected data. In this case, you can display a list of customers when an item cell contains a revenue ID and a list of suppliers when it contains an expense item ID.

To achieve this, return the list while adding a third column to serve as a filter, such as item_id. For instance, you can use the following SQL command:

SELECT c.id, c.name, ic.item_id FROM companies c INNER JOIN item_companies ic ON ic.company_id = c.id

This is an example of a filtered list. Here, the SaveToDB add-in creates Excel validation lists with dynamic formulas. Other SaveToDB products filter cell list values using the third column.

Sometimes, the task can be simplified by using tables or views. For example, consider a table of states with the columns: id, country_id, and state. If your data query includes the @country_id parameter, you can configure the table query in the HANDLER_CODE field like this:

id, +state, @country_id

SaveToDB products will generate the following SQL code to select data:

SELECT id, state FROM states WHERE country_id = @country_id ORDER BY state

To create a filtered list, specify the following code in the HANDLER_CODE field (using country_id as the third column):

id, state, country_id

SaveToDB products will then produce the following SQL code:

SELECT id, state, country_id FROM states

You can omit the + or - signs used for sorting, as the products handle sorting internally.