Refreshing Dependent Lists
Suppose we have the following configuration:
ID | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | EVENT_NAME | HANDLER_SCHEMA | HANDLER_NAME | HANDLER_TYPE | HANDLER_CODE |
---|---|---|---|---|---|---|---|---|
s02 | usp_cashbook | company_id | ValidationList | s02 | companies | TABLE | id, +name | |
s02 | usp_cashbook | company_id | ParameterValues | s02 | companies | TABLE | id, +name |
In the sample, both the company_id column and parameter have values from the companies table.
If someone adds or edits companies, users with the opened usp_cashbook have to reload data and configuration or validation lists to get the new values.
SaveToDB 10 has an improved solution if a user updates source tables in the same workbook.
For example, if a user saves changes to the companies table, the add-in automatically refreshes company lists for columns and parameters.
The case above is simple as the lists are loaded from the table that is updated.
Suppose we have the following configuration, replacing the companies table with a procedure that selects values from the table:
ID | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | EVENT_NAME | HANDLER_SCHEMA | HANDLER_NAME | HANDLER_TYPE | HANDLER_CODE |
---|---|---|---|---|---|---|---|---|
s02 | usp_cashbook | company_id | ValidationList | s02 | xl_list_company_id | PROCEDURE | ||
s02 | usp_cashbook | company_id | ParameterValues | s02 | xl_list_company_id | PROCEDURE |
Even in this case, the add-in will refresh lists automatically if it can parse the procedure definition and find that the procedure selects data from the table.
However, you can point the dependency explicitly using the DependsOn event name.
For example:
ID | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | EVENT_NAME | HANDLER_SCHEMA | HANDLER_NAME | HANDLER_TYPE | HANDLER_CODE |
---|---|---|---|---|---|---|---|---|
s02 | usp_cashbook | company_id | DependsOn | s02 | companies | TABLE |
This way is simple as you can copy a line and change ValidationList to DependsOn.
However, it requires duplicated lines for every object and column.
You have an alternative way:
ID | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | EVENT_NAME | HANDLER_SCHEMA | HANDLER_NAME | HANDLER_TYPE | HANDLER_CODE |
---|---|---|---|---|---|---|---|---|
s02 | xl_list_company_id | DependsOn | s02 | companies | TABLE |
This configuration tells that the list procedure depends on the companies table.
So, the add-in will update all lists based on xl_list_company_id when the companies table has been saved.
Moreover, you can set the dependency between edit forms and underlying tables.
For example, let's add a usp_companies procedure used to edit companies and set the dependency:
ID | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | EVENT_NAME | HANDLER_SCHEMA | HANDLER_NAME | HANDLER_TYPE | HANDLER_CODE |
---|---|---|---|---|---|---|---|---|
s02 | usp_companies | DependsOn | s02 | companies | TABLE |
In this case, when a user saves changes for the usp_companies procedure, the add-in will update lists based on the companies table and the xl_list_company_id procedure.