DELETE_WHERE
= optional in PARAM
Parameter (col A in PARAM) | Mandatory/Optional | Value in col C (example) | Value in col D (example) | Occurrence in PARAM sheet | Comment | |
|---|---|---|---|---|---|---|
FORMS | REPORT | |||||
DELETE_WHERE |
| n/a | <OBJECT> | <Object value> | x number of times | A where-clause for deleting existing records when updating data. Can be defined several times. |
DELETE_WHERE is an optional FORM specific parameter. By default, Bizview creates the delete where-clauses based on the parameter values in PARAM. In some scenarios, the designer wants to decide which parameter values should be added to the delete query. When templates are created and DELETE_WHERE is used, the designer needs to thoroughly consider how the deletions should be done when values are updated in the database, in order to avoid future errors in the database. The object and object values defined in the DELETE_WHERE parameter will be added as a where-clause to the SQL query to limit the deletion in the database.
Note: By default, all global filters specified in the PARAM sheet will automatically be added to the delete when an update of values is done. However, if a DELETE_WHERE is added in PARAM, then ONLY those values will be added to the delete clauses (and any other global filters added in PARAM will NOT be added to the delete).
In conclusion, if DELETE_WHERE is used, then the designer needs to add all global filters that should be included in the delete as DELETE_WHERE parameter values. If no DELETE_WHERE is used, then ALL specified global filters in PARAM will be added in the delete.
If you have stated DELETE_WHERE parameter in PARAM, then Bizview will only add these parameters values to the filtering of the data that will be deleted:
- CLIENT
- VERSION
- VALT
- CURR
- FORM_TAG
- DELETE_WHERE
- row_status = 0
When using DELETE_WHERE, Bizview cannot use change detection and only update the cells that have been changed. If this parameter is used, then all cells will be updated every time a save is done to the database, even though the user might only have changed values in a few cells. For more information, see DELETE_WHERE.
Access Delete Where in the Template Design right panel by selecting Parameters > Data Update > Delete Where.
In the Delete Where grid you can view existing delete where parameters defined in the template. You can also add new, edit existing, or delete parameters.
- Object ID and Object value(s) need to be defined for each DELETE_WHERE parameter. If several values need to be defined for a specific object, use SQL Smart to define those values. For example, separate with # if several specific values should be included (10#11#12) or .. if you want to use an interval (10..12).
- You can add several DELETE_WHERE parameters for different objects.
If the FORM is dynamic and the user can select the dimensions values expanded in the rows, then this parameter needs to correspond to the dimension selections made by the end-user when generating the FORM. Otherwise, all data rows (for all dimension values) will be deleted when updating this particular FORM. In other words, if the user can decide which values and how many rows should be generated in the form rows, DELETE_WHERE needs to be added and correspond to the particular user selection.