Skip to content

Customizing a Data Dictionary for the Inventory Parts Table

In this section, you will customize the cInventoryDataDictionary 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 Inventory table to display its Columns, Indexes, Relationships, and Data Dictionaries. You will see cInventoryDataDictionary listed.

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

  3. Select the Item_Id column and set the Auto Find EQ, Capslock, and Protect Value (Key) options in the Data Entry Option group to True.

  4. Select the Vendor_Id column and set the Capslock option to True.

  5. Select the Unit_Price column, then select the Validation Type option in the Lookup/Validation group.

  6. Click on the Prompt (...) button in the Validation Type option form. This will open the Column Validation dialog.

  7. Select the Range radio button. Enter 0 in the Range From form and 999999.99 in the To form, then click Ok.

Back on the Columns tab page of the Data Dictionary Designer, you will see 0, 999999.99 filled in as the Validation Type. This disallows any negative entries or entries over $1 million.

  1. For the On_Hand column, create another range validation and enter -999999 and 999999 in Range From and To, respectively.

  2. Select the Unit_Price column and set the Mask Type option in the Appearance group to Mask_Currency_Window.

  3. Select the Item_Id column and set the following options in the Appearance group:

  4. Long Label: Inventory.Item Id
  5. Short Label: Item Id
  6. Status Help: Inventory Item ID - user defined identification

Select the Description column and set the following options: - Long Label: Inventory.Description - Short Label: Description - Status Help: Inventory Part Description

Select the Vendor_Id column and set the Status Help option to Vendor Number.

Select the Vendor_Part_Id column and set the Status Help option to Vendor ID name for this item.

Select the Unit_Price column and set the Status Help option to Retail unit price.

Select the On_Hand column and set the Status Help option to Units currently available.

  1. Click on the Structure tab page of the Data Dictionary Designer.

  2. For Key Foreign Field Options, verify that Find Required and No Put are checked.

  3. For Indexed Foreign Field Options, only No Put should be checked.
  4. For Default Foreign Field Options, only Display Only should be checked.

Business Rule #4 states that if an item appears on an order, you cannot delete the item from the Inventory Report. Restated in database terms: if a parent record has child records, do not delete the parent record. More specifically, if an item appears in the OrderDetail table, then it cannot be deleted from the Inventory table.

  1. In the Properties window, set the Cascade_Delete_State property to False.

    Business Rule #3 states that the quantity sold of an item cannot exceed the number that exists in inventory stock. We can restate this business rule in DataFlex database terms as "you cannot order an item if the value in Inventory.On_Hand would be less than zero afterwards."

  2. In the Properties window, click on the Events tab.

    • Double-click on Validate_Save. When you have completed this process, the Validate_Save event will be listed in the code editor (and the Studio has switched from the Data Dictionary Designer to the code editor).

  3. Modify the Validate_Save method to look like the code below:

    Function Validate_Save Returns Integer
    Integer iRetVal
    Forward Get Validate_Save To iRetVal
    If iRetVal Function_Return iRetVal
    If (Inventory.On_Hand < 0) Begin
        Send UserError "Insufficient Inventory Stock" ""
        Function_Return 1
    End
End_Function

    The code above for the function that implements this business rule simply augments the Validate_Save message to return an error if the user tries to enter more of an item than is available. A save is only performed on a function return value of 0, so returning 1 (or a non-zero value) means no save takes place.

    Notice that we did not try to accomplish this validation by using set Field_Value_Range. The inventory quantity check can only be performed while the record is being saved and the record is locked. Validate_Save is called right before the actual save. All required records are locked and all columns have been updated. The value in the column is the value that will be saved (i.e., any quantity adjustments have already taken place).

    All other validations occur during data entry or at the start of a save process before any locking has taken place. Validate_Save is special in that it occurs in a locked and fully updated state. These validations should be as quick as possible. Also note that the error command in this function will not be executed in a locked state. Before the error is reported, the transaction is canceled and rolled back as required. DataFlex does this for you automatically.

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

Next Step

Customizing a Data Dictionary for the Order Detail Table