FORM Delete Strategies

When saving data in a Bizview form, the data is saved to the data_cells, data_dimensions, and data_attributes tables. When the same form is saved again, the previously saved data must first be deleted before the new data can be saved.

To execute this delete operation, Bizview first evaluates which rows in data_cells to delete. This is based on an evaluation of the template and associated param sheet. Finding the correct records to delete in data_cells is a time consuming task, but there are different strategies or methods to make the delete operation faster.

This topic contains the following sections:

• Evaluation of the PARAM-Sheet Settings

• Evaluation of the Template Sheet

• Speeding up the Delete

• Conclusion

Evaluation of the PARAM-Sheet Settings

When Bizview is evaluating which rows to be deleted, the following happens:

1. Bizview checks for the following occurrences in the PARAM-sheet:

   • CLIENT
   • VERSION
   • VALT
   • CURR
   • ADDSHEET
   • FORM_GROUP

If these exist in PARAM, they will always be used in the “WHERE” criteria. The designer cannot disable these in the template. If the designer wants to remove any of these occurrences, the designer must create an event procedure (see later in this document).

2. If parameter DELETE_WHERE exists in PARAM-sheet, the specified objects and values are used in the WHERE criteria. Objects in PARAM will be ignored!
3. If no DELETE_WHERE exists in PARAM-sheet, Bizview searches for other objects that exists in PARAM and use them in the WHERE clause.

Bizview now has all the global parameters to determine which data_cells records need to be deleted based on the PARAM-sheet.

Evaluation of the Template Sheet

Column A in the template where values have been entered is now analysed. The outcome of this evaluation can result in two different scenarios:

1. If Bizview only finds TIMEUNIT and TIMEVAL in column A, and every TIMEUNIT is the same in every column, then TIMEUNIT and TIMEVALUEs are used in the WHERE clause (TIMEUNIT = ‘ISOMONTH’ and TIMEVAL in (‘201401’,’201402’,’201403’…) etc.

2. If Bizview finds different TIMEUNITs, or column A contains objects, then the delete evaluation must be performed on every CVAL column with the values that are stated on each CVAL column.

Speeding up the Delete

Objects that are stated in column A can be removed from the delete WHERE-clause by adding the parameter DELETE_FILTER on row 1 and putting FALSE on the object row. Below, you can see that the form has the objects SPEC and ACT stated in column A. We don' need those in the delete, so we enter FALSE under DELETE_FILTER:

If the second scenario above occurs, the evaluation of which records to delete can be slow, especially when data_cells and related data_dimensions contain a lot of data. Many times, Bizview shouldn't need to consider the values in the template columns because the values in PARAM have narrowed the criteria down so the correct rows will be deleted. However, the problem is that Bizview cannot know that automatically; it's all dependent on the design of the template and what the designer wants to achieve. To give the designer more freedom to control the delete-operation, the following additional methods are available.

DISABLE_TEMPL_DELETE

If the delete executes slowly, the designer can consider using the new parameter (Bizview v6.2) DISABLE_TEMPL_DELETE. If this parameter is TRUE, Bizview will NOT read the template-sheet at all for fetching the where criteria in the delete. Only objects in PARAM are used, with the same rules as stated earlier.

It's now up to the designer to narrow the delete. Perhaps the objects that exist in PARAM are good enough, but if you want more criteria, you need to add DELETE_WHERE (described earlier).

Let's say that the designer has UNIT and FORM in PARAM and VALT and ACT in the template. The designer would probably then want to add the ACT object in the delete, but not VALT. To include ACT in the delete you must create three DELETE_WHERE (FORM, UNIT, and ACT) and add the correct values for each.

Custom Event Procedure

If the designer wants even more control over the delete, it's possible to control the exact delete criteria by creating a custom procedure that manipulates the table #delete_clauses.

In the “Before Values Deleted” event you can assign the procedure that take cares of special things for the delete.

In the procedure you can modify the tables #delete_joins and #delete_clauses in the way you want the delete to work. To do that you must understand how these tables works.

#delete_joins

The table #delete_joins contains all objects that need to be joined to dat.data_cells. The columns in the table are:

• ws_name:      The name of the template sheet with values.

• ws_col:        0 or the CVAL column in the template sheet.

• obj_id:             The object name.

The table can look like this:

ws_name        ws_col        obj_id

11        22        UNIT

11        22        FORM

11        23        UNIT

11        23        FORM

Here we can see that Bizview will join UNIT and FORM for column 22 and 23 in the template. If Bizview found that values in the template should not be read, all ws_col would have been 0 (zero), which means all objects are fetched from PARAM.

#delete_clauses

This table contains the actual where for the objects and can look like this:

ws_name        ws_col        seqno        col_name                col_filter        

11        22        0        UNIT.objval_id        {11}

11        22        0        client_id                1        

11        22        0        version_id                BU2        

11        22        0        valtype_id                AMOUNT        

11        22        0        FORM.objval_id        {BUDGET_01}

11        22        1        timeunit_id                ISOMONTH

11        22        2        timeval_id                201101

11        22        3        currcode_id                USD

11        23        0        UNIT.objval_id        {10}

11        23        0        client_id                1

11        23        0        version_id                BU2

11        23        0        valtype_id                AMOUNT

11        23        0        FORM.objval_id        {BUDGET_01}

11        23        1        timeunit_id                ISOMONTH

11        23        2        timeval_id                201102

11        23        3        currcode_id                USD

This template has UNIT in column A, so Bizview will look up records in dat.data_cells for all CVAL columns. This means the content highlighted yellow above will result in one where and the regular text in another.

If the template doesn't need to read the template, and only objects in the PARAM should be used, the tables could look like this:

#delete_joins

ws_name        ws_col        obj_id

Template        0        ACCOUNT

#delete_clauses

ws_name        ws_col        seqno        col_name                col_filter

Template        0        1        client_id                ADP_VG

Template        0        2        version_id                BUD

Template        0        3        valtype_id                AMOUNT

Template        0        4        currcode_id                SEK

Template        0        6        timeunit_id                ISOMONTH

Template        0        7        timeval_id                201101

Template        0        5        ACCOUNT.objval_id        7110#7110#7110

In this case, the delete only does one join with ACOUNT, compared to the example above where the delete should do a join with UNIT and FORM for every CVAL column.

If you want to, for example, remove the currcode_id from the delete, you can put following in the event procedure:

alterprocedure[cust].[p_before_delete_values]

as

begin

    deletefrom#delete_clauses

    wherecol_name='currcode_id'

end

Conclusion

There are five ways to manage the delete:

1. Let Bizview take care of the delete entirely. The rows that will be deleted are strongly mapped to what is in the template.
2. Add a DELETE_WHERE in PARAM to override objects in PARAM.
3. Add a DELETE_FILTER in the template and write FALSE on objects that should not be used in the delete.
4. Set DISABLE_TEMPL_DELETE to TRUE in PARAM to only use parameters in PARAM.
5. Write a procedure that changes the content of #delete_joins and #delete_clauses.

The delete will be slow if following statements is true:

dat.data_cells has many rows and DISABLE_TEMPL_DELETE = false

and

Different TIMEUNITs in column A in the template

Or

VALT, CURR or OBJECT (with DELETE_FILTER = TRUE) exists in column A in the template.

Caution: The delete operation in data_cell and related tables is very important and sensitive. If manipulating the default delete mechanism, there is a risk that when one user saves data, the data saved by another user in another form is deleted if not performed correctly. Any adjustment described above, and especially the usage of a custom procedure to manipulate the delete must be developed and tested in dev/test-enviroment before put into production.