Skip to content

Customizing a Data Dictionary for the Order Detail Table

In this section, you will customize the cOrderDetailDataDictionary that was automatically created when you created the table. You will add some business rules and other settings to the Data Dictionary. The reason for each Data Dictionary setting you apply will be explained.

The exceptions are business rules already explained when you created the previous Data Dictionaries in the Customizing the Data Dictionaries book. Most explanations can be read on the Customizing a Data Dictionary for the Customer Table page, since this is the first Data Dictionary created.

Steps to Customize the Data Dictionary

  1. In Table Explorer, expand the OrderDetail table to display its Columns, Indexes, Relationships, and Data Dictionaries. You will see cOrderDetailDataDictionary listed.

  2. Double-click on the cOrderDetailDataDictionary class. The class will now open in the Studio's Data Dictionary Designer.

  3. On the Data Dictionary Designer's Columns tab page:

  4. Select the Order_Number column and set the No Put option in the Data Entry Option group to True.
  5. Select the Detail_Number column and set the No Put option to True.

These options are set because the order and detail numbers are set automatically, so we don't want users able to change them manually. OrderDetail.Order_Number relates to OrderHeader.Order_Number and will be assigned as part of the save. OrderDetail.Detail_Number is assigned by the Auto Increment included in this Data Dictionary.

  • Select the Extended_Price column and set the Display Only option to True.

This option is set because the extended price is calculated automatically, so we do not want users to be able to change it manually. This value is calculated when the detail line is saved.

  1. Select the Detail_Number column, then select the Auto Increment option in the Other group.
  2. Click on the Prompt (...) button in the Auto Increment option form. This will open the Select Auto Increment Table and Column dialog.
  3. Expand the OrderHeader table and select the Last_Detail_Number column, then click Ok.

This satisfies Business Rule #11, which states that each line item in the invoice's table (or grid) must be numbered automatically. Notice that the source auto-increment table and column is not a system table; it is the parent order-header record. In this sample, each order-detail item number is assigned a consecutive value from the parent order record.

  1. Set the following Mask Type options in the Appearance group:
  2. Select the Qty_Ordered column and set the Mask Type option to Mask_Numeric_Window.
  3. Select the Price column and set the Mask Type option to Mask_Currency_Window.

  4. Select the Extended_Price column and set the Mask Type option to Mask_Currency_Window.

  5. Set the following label options in the Appearance group:

  6. Select the Qty_Ordered column:
    • Set the Long Label option to Quantity Ordered.
    • Set the Short Label option to Quantity.
    • Set the Status Help option to Number of items ordered.
  7. Select the Price column:
    • Set the Long Label option to Price per Unit.
    • Set the Short Label option to Price.
    • Set the Status Help option to Price per Unit.

  8. Select the Extended_Price column:

    • Set the Long Label option to Extended Price.
    • Set the Short Label option to Total.
    • Set the Status Help option to Total extended price.

  9. Press F7 to switch to the code editor. Add a method to the cOrderDetailDataDictionary class by adding the following code:

Procedure Adjust_Balances Number Qty Number Amt
    Subtract Qty from Inventory.On_Hand
    Add Amt to OrderHeader.Order_Total
End_Procedure
  1. Press Ctrl+F7 or select cOrderDetailDataDictionary in Code Explorer. In the Properties window, click on the Events tab.

  2. Double-click on Backout and Update. When you have completed this process, the corresponding events will be listed in the code editor (and the Studio has switched from the Data Dictionary Designer to the code editor).

  3. Modify the Backout and Update methods to look like the code below:

Procedure Update
    Forward Send Update
    Move (OrderDetail.Price * OrderDetail.Qty_Ordered) to OrderDetail.Extended_Price
    Send Adjust_Balances OrderDetail.Qty_Ordered OrderDetail.Extended_Price
End_Procedure

Procedure Backout
    Forward Send Backout
    Send Adjust_Balances (-OrderDetail.Qty_Ordered) (-OrderDetail.Extended_Price)
End_Procedure

Whenever a detail line is saved, edited, or deleted, a number of other balances must also be adjusted.

  • Business Rule #5 states that when an item is ordered, you must decrement the quantity ordered from the quantity on hand. This will maintain the amount on hand for each item.
  • Business Rule #6 states that the order's Total column is the sum of all the extended prices.
  • Business Rule #9 states that to get the extended price for each item, you must multiply the quantity ordered by the unit price.

The Update method is called when a record is being saved or edited, and the Backout method is called when a record is being deleted or edited. The calculations performed in these methods are opposites. They both call the method Adjust_Balances with the appropriate parameters to adjust the data as required.

The value of OrderDetail.Extended_Price is calculated when the detail record is saved. Other methods are used (and described below) which will keep this column current during data entry (before the save). These other methods (Adjust_Display_Total and Entering_Price) are used only to supply the user with useful information during data entry. The calculation that occurs during the save is the calculation (in procedure Update) that ensures that the extended price is correct.

  1. Add another method to the cOrderDetailDataDictionary class by adding the following code:

    Procedure Adjust_Display_Total Integer iField Number nAmnt
    Integer iQty
    Get Field_Current_Value Field OrderDetail.Qty_Ordered to iQty
    Get Field_Current_Value Field OrderDetail.Price to nAmnt
    Set Field_Current_Value Field OrderDetail.Extended_Price to (nAmnt * iQty)
    // note we set value, but not changed state!
End_Procedure

    During data entry, we always want the extended price to be up-to-date. Anytime the item price (OrderDetail.Price) or the quantity ordered (OrderDetail.Qty_Ordered) is changed, we want to adjust the extended price column (OrderDetail.Extended_Price). We will perform this update as part of the column-exiting process. When an item based on Qty_Ordered or Price is exited, Procedure Adjust_Display_Total will be called. This method will set the column value for Extended_Price based on the column values of Qty_Ordered and Price.

    When the extended-price column is changed, all data entry object (DEO) items using this table and column will be updated. This keeps our display up-to-date.

    It is instructive to see what actually happens to make this all work:

    1. The user exits an object (or item) in a DEO.
    2. As part of item exiting, the column-exit message for the Data Dictionary Object (DDO) is called.
    3. The column-exit message calls a procedure that changes the value of another column.
    4. When the column value is changed, all DEOs using that column are updated with the new value.

    We use this only to display the extended total. When the record is saved, the extended total is calculated again and this new value is saved. We do this because you cannot be certain that a view will contain the Qty_Ordered and Price columns and, if present, you cannot be certain that a user will enter and exit these columns before saving the record. You should never assume that a column will be entered or exited within a view. It is very important that you understand this.

  2. Add another method to the cOrderDetailDataDictionary class by adding the following code:

    Procedure Entering_Price Integer Field# Number nAmnt
    Handle hoInventoryDD
    Boolean bHasRecord
    Get Data_Set Inventory.File_Number to hoInventoryDD
    If (hoInventoryDD) Begin
        Get HasRecord of hoInventoryDD to bHasRecord
        If (nAmnt=0 and bHasRecord) Begin
            Get File_Field_Current_Value File_Field Inventory.Unit_Price to nAmnt
            Set Field_Changed_Value Field# to nAmnt
            Send Adjust_Display_Total Field# nAmnt
        End
    End
End_Procedure

    A detail line is created by selecting an inventory item. The inventory item contains a unit price for the item. This is the suggested unit price. The actual unit price must be entered by the user. We would like to use this suggested price as our default price. We will do this by creating a column-entering message for OrderDetail.Price. When entering the control associated with the column, the program will check to see if the current column value is zero. If it is, it will assume that a price has not yet been assigned and set it to the inventory item's unit price.

    Procedure Entering_Price demonstrates the following:

    • We only update the value if the current value is zero. The current value is passed to the procedure.
    • We need to get a column value from a foreign table (i.e., the column is not part of the current Data Dictionary; it is part of a parent-table DDO). When we do this, we must Get the File_Field_Current_Value and not the Field_Current_Value. Also, we must use the File_Field parameter and not the field parameter. When accessing values from a foreign DDO (or from within a DEO), you must remember to use the File_Field_ prefix.
    • After setting the new column value (Field_Changed_Value), we will send the message Adjust_Display_Total. This will adjust the extended-price column.
    • Note that all of the column-entry and exit messages perform actions that make data entry more convenient. They do not perform validation or integrity-checking operations. This is typical of column entering and exiting. Validations are performed by column-validation messages and by the Validate_Save function.

  3. Press F7 to switch back to the Data Dictionary Designer.

    • On the Columns tab, select the Qty_Ordered column and select Adjust_Display_Total from the combobox dropdown list in the Exit Message option in the Other group.
    • Select the Price column and select Adjust_Display_Total from the combobox dropdown list in the Exit Message option.
    • Select the Price column and select Entering_Price from the combobox dropdown list in the Entry Message option.

  4. Click the Save File toolbar button (or press Ctrl + S) to save the Data Dictionary.

Next Step

Creating Windows Data Entry Components