Filtered vs. Dynamic Lists

Suppose you have tables like items, companies, and item_companies.

You want to show customers only for the revenue item and suppliers only for the expenses item.

A typical list of companies uses a query like

SELECT id, name FROM companies

If your data query contains a parameter like @item_id, you can also use it in the value list.

For example, in an SQL command like

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 sample is of the dynamic list.

SaveToDB products reload such lists whenever a user changes the @item_id parameter.

Another case is when you have the item_id column in the selected data.

In this case, you can show a list of customers when an item cell contains a revenue id and a list of suppliers for the expense item id.

To solve this task, return the list, adding the third column used as a filter, like item_id in the sample.

For example, in an SQL command like

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

This sample is of the filtered list.

In this case, the SaveToDB add-in creates Excel validation lists with dynamic formulas.

Other SaveToDB products filter cell list values using the third column.

Sometimes, the case can be easier and allows using tables or views.

For example, you have a table of states with the columns: id, country_id, state.

If your data query contains the @country_id parameter, you can configure the table query in the HANDLER_CODE field like

id, +state, @country_id

SaveToDB products create 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 (country_id as the third column):

id, state, country_id

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

SELECT id, state, country_id FROM states

You can omit + or - signs used to sort data as the products sort such lists internally.