Updating fields in DB tables – MD Map

Numeric data entered in a field in a form may need to be aggregated into a field in a master table. Similarly, a string or date field may need to be replaced into a field in a master table. Some use cases for this may be to aggregate total sales in customer table from invoice form, replace the last sale date in customer form with invoice date, etc. The form from which data is to be updated is called the detail form. The form to which the data is to be updated is called the Master form. This update is defined using a Master-Detail Map (MD Map).

The field to be updated is defined by selecting the master form and master field. The detail field is the field from which data entered in the details form is updated to the master field.

The relationship between the master table and detail table is selected in the Master Search Field and Detail Search Field.

An MD Map definition is converted into an “Update” SQL statement and posted to database. A typical update statement looks like :
Update <TableName> set MasterFieldName=MasterFieldName+DetailFieldName where MasterSearchField=DetailSearchField

Example 1: Consider an invoice form as below:

Consider customer master form as below:

To update the TotalSaleValue in Customer form from SaleValue field in invoice form, define an MD Map as below:

• Master form – Customer master
• Master field – TotalSaleValue
• Detail field – SaleValue
• Master search field – Default (This refers to the primary field of the customer master table)
• Detail search field – CustomerName
• Update type – Add

In the invoice form, if the customer name field is not save normalized, then:

• Master Search field – Name in the Customer form
• Detail search field – CustomerName in invoice form.

Control field

A control field controls the MD Map execution. The MD Map will be posted only if this field value is T.
If the control field value changes from T to F, the already updated value will be reversed. If the control field value changes from F to T, the value will be updated.

Example 2:
In example 1 given above, consider the invoice header has another drop-down field named Invoice type with drop-down values Proforma, Tax Invoice. In run time, if the user selects Proforma in this field, then the MD Map should not be executed. Add a field named CanUpdateMaster with expression iif(InvoiceType={Proforma}, {F}, {T})

Execute on approve/reject

An MD Map can be executed only when the data in the form is approved or rejected. The MD Map will not be executed when the transaction is saved. This can be enabled by checking those two options.

Append MD Maps (AMD Maps)

MD Maps will search the master table using the master search field and detail search field. Matching records in the master are updated. If matching records are not found, a new record can be added to the master table. This can be achieved by ticking the Append option in MD Map definition.

MD Map definition as script

An MD Map can be defined as a script like below:

• tablename = Give the name of the table to be updated
• transid =transid of the form in which this MD Map is defined
• sourceframe = if all rows from a grid DC should be updated, give gird DC number here else give 1
• @mastersearchfield = detailsearchfield
• +masterfield1= detailfield1
• *masterfield2= detailfield2
• append = true

Create a Tstruct with an MDMap. Open the Maptext with ellipsis on the side.

Insert the script in the pop-up editor as shown below

The script is a collection of name-value pairs. Name-value pairs prefixed with the@ symbol is considered as a master search field and detail search field. Name-value pairs prefixed with the * symbol are considered as master field and detail field with update type replace. Name-value pairs prefixed with + symbol is considered as master field and detail field with update type add. The @ symbol in the script helps find the keyword. The other special characters that can be used in the script are as follows:

• @ find
• + Add
• – Less
• = Replace
• > Highest
• < Lowest
• ^ Increment
• ! Decrement

Include the username, usergroup, startdate, endate ( the column names of the axuserlevelgroups table) based on the details in the columns of your table. And make sure the keyword append is set to true. Save the script and click on run. Insert new values into the form.

Open the list view and find the new entry.

Now goto Dbeaver and run the SQL query to fetch the columns of axuserlevelgroups and find the new username entry in the list.

Try Out

Update the employee and department count in the corresponding master tables.