Master-Detail (MD) Maps

A transaction in a business application may need to update data from a field in one table into a row in another table. MDMaps are the process maps used to perform this operation.

The transaction from which data is retrieved is called the detail transaction. The Tstruct that is updated is called the master transaction. The MDMap is always inserted in the detail Tstruct and the properties are set so as to link the detail Tstruct and the master Tstruct.


Consider a scenario where you have a Loan application Tstruct and Repayment Tstruct.

The Loan Application Tstruct contains a field named RepaidAmount. The Repayment Tstruct captures repayment data .It is here that the user enters amount that is being repaid. Now, this repaid amount from the Repayment Tstruct has to be updated to the loan application tstruct. When changes are made to a repayment transaction, the corresponding changes are to be done to the RepaidAmount field in loan application structure. This is achieved by creating an MDMap in the Repayment Tstruct.

In this case the definition will be as below. (Read MdMap properties for further explanation of the different properties of MDMap)

Master transaction = Loan application  (the tstruct that is to be updated)

Master field = RepaidAmount (the field in the master tstruct that is to be updated)

Detail field = PaidAmount (the field in this tstruct that is to be updated to the master)

Master Search Field = ApplicationNo

Detail Search Field = ApplicationNo

Update type = Add

The Master Search Field & Detail Search Field relate the master & detail table.This gets converted to an SQL statement as below when the repayment transaction is saved.

update loanapplication set repaidamount=repaidamount + PaidAmount where loanamount.applicationno=:applicationno

The ApplicationNo is a field in the Repayment Tstruct & this is passed to the :applicationno parameter in the above SQL.

The data from the detail structure can be added to the master field, subtracted from the master field or replaced into the master field.

When data is changed in the detail structure & saved, corresponding changes are done to the master field too. That is, if PaidAmount is reduced, the RepaidAmount is also reduced and vice versa. If the repayment transaction is deleted or cancelled, then the paidamount is reduced from RepaidAmount.

Conditional update

If an update in the Master should happen only on a condition, the use the Control Field property. Select a field from the current TStruct as a control field. This field should contain T or F. The MD Map will be applied only if this field contains T. You can use an expression with IIF function to implement a rule.

Consider a case when the value of this field was T when a transaction is saved. So, the update will be done. Now, the user loads the transaction in edit mode and because of which the value in the control field changes to F. In this case, Axpert will do a negative update. That is if an Add type of update was defined, the detail value will be reduced from the master field. Similar update happens when the value changes from F to T. A control field cannot have its Save Value property set to false.

You can also define the MD Map to be made active only if the transaction is approved or rejected. This is applicable only if there is a work flow attached with the TStruct. Set these property values to Yes or No depending on need.

Example – Conditional Update

When a PROFORMA Invoice (invoice generated for the sake of courtesy)  is entered, the quantity is not to be updated to the Item Master.

In the Invoice TStruct, create a field called PROFORMA which accepts ‘Y’ or ‘N” from the user.

Create a field called UPField where the Expression is entered as follows

IIF ( PROFORMA = ‘Y’, {F} , {T})

Select UPField as the ControlField in the MDMap properties.

The result :  If PROFORMA has the value ‘Y’, the control field is updated as F and the update is not done. In case of ‘N’, the control field will read T and  update is carried out.