Advanced PowerBuilder

Parent-Child Tables Data-Entry

Till now you have learned about updating DataWindows with single and multiple-tables. They are typically, either the master files ( independent tables ) or related tables ( joins, for example, department & employees ). In product_master/trans tables, any entry in "trans" has to update "product_master". If the transaction is a receipt, product_balance in product_master has to be increased, decreased in case of issue and so on. That means, you need to write script to update product_master table also, instead of just giving Update() for the "trans" table.

Other than updating the master file, you need to consider one more thing. Take "trans" table, values for some columns repeat for the transactions. For example, a receipt with 10 items, has the same "tran_no", "tran_date" and "tran_type" for all 10 items. You have to make provision for the user, to enter these repeated values only once. To do this, create a DataWindow with GROUP presentation style or create a DataWindow with tabular presentation style and use GROUP BY clause for the repeated columns.

In both the cases, by default, the columns that are part of the group have 0 ( Zero ) tab order. Even if you change it at design time, at run-time these are set back to 0 ( Zero ) and the user can"t enter data in the columns that are part of the group.

The solution for this is, create two DataWindows for the "trans" table. One DataWindow to capture header data, "tran_no", "tran_date" and "tran_type", another to capture transaction details such as serial_no, item_no and quantity.

For the header DataWindow, select only three columns from the "trans" table, i.e., "tran_no", "tran_date" and "tran_type" as the data source. For detail DataWindow, unlike the header DataWindow, select all columns from the "trans" table and display only "tran_item_no", "tran_qty" and "tran_serial_no" on the screen. Delete the rest of columns from the screen ( not from the SELECT list in the SELECT statement ), i.e. that are present in the first DataWindow.

We are using the header DataWindow, to capture repeated values in the transaction. We never update the header DataWindow. Whenever user wants to save the changes to the database, we populate the detail DataWindow from the header DataWindow. Now, detail DataWindow has all the information about a transaction. Now, we update the detail DataWindow.

Using two DataWindows, one for the header info and other for the detail info, is the solution for "How to avoid the user from entering duplicate info?". We still don't have the solution for updating the "product_master" table, for changes done in the "trans" table. Actually, we already explained the solution in "Multiple Table DataWindow Update" section. That section requires you ( as a PowerBuilder programmer ) to know the relationship between tables in the database. This is also called as Fat Client, i.e., most of the logic is written in the client process.

Push some load onto the server using "triggers". These days, all most all RDBMSs support "Triggers". A Trigger is a special type of stored procedure maintained and executed automatically in the database. You can write one trigger for each INSERT, DELTE, UPDATE operations. That means, if you write one trigger for the INSERT operation, the database automatically executes the trigger for each INSERT operation on that table. In the trigger logic, we can update the "product_master" table.

In our application, let's implement Server Side logic. We already painted the required DataWindow objects.

Create a new window "w_transactions". Place three DataWindow controls as shown in the above picture.

Assign "m_sheet_menu" menu to this window.

Write the following in the "Open" event of "w_transactions"

// Object: w_transactions
// Event: Open

dw_product.SetTransObject( SQLCA )
dw_tr_head.SetTransObject( SQLCA )
dw_tr_detail.SetTransObject( SQLCA )
dw_product.Modify( "DataWindow.ReadOnly=Yes" )

What we plan to do is,  when the user enters a product_no, we retrieve information about the product and displaying it in the "dw_product" DataWindow control. Since, we are using "dw_product" DataWindow for display purpose only, it would be better to make it read-only. When a DataWindow is read-only, PowerBuilder doesn't maintain all buffers, i.e., "Deleted", "Filtered", "Original" for it. That means efficient use of resources. We are making "dw_product" DataWindow as read-only, by calling Modify() function.

Now, we need to allow the user to add new record. Declare a user-defined event "ue_add" for the window. ( Select "Declare/User Events" from the menu. Make sure that you are creating this event for the window, and not for any control in the window ). You can map it to any custom event, say, pbm_custom01. Write the following script to the event.

// Object: w_transactions
// Event: ue_add

Long l_NewRowNo

If dw_tr_head.RowCount() = 0 and &
       dw_tr_detail.RowCount() = 0 Then
   dw_tr_head.InsertRow(0)
   dw_tr_detail.InsertRow(0)
   SetFocus( dw_tr_head )
   dw_tr_head.SetColumn( "tran_no" )
Else
   l_NewRowNo = dw_tr_detail.InsertRow(0)
   dw_tr_detail.ScrollToRow( l_NewRowNo )
   SetFocus( dw_tr_detail )
   dw_tr_detail.SetColumn( "tran_serial_no" )
End If

If the user is adding a new record, we need to take the header information also. That's why, we are inserting rows in header as well as in detail DataWindows. SetFocus() function changes the focus to the specified control. We want the user to enter the header information first, so, we are setting the focus to the "dw_tr_head" DataWindow. We need to use SetColumn() function, to set the focus to a column within the DataWindow. If the user has already entered the header info, we insert only in the detail DataWindow. In which case, the cursor focus is set to the "tran_serial_no" column.

When the user enters a product_no and tabs out of "tran_item_no" column, we want to display product information in the "dw_product" DataWindow. So, write the following code for the "ItemChanged" event in the "dw_tr_detail" DataWindow.

// Object: "dw_tr_detail" DataWindow in "w_transactions" window.
// Event: ItemChanged

If This.GetColumnName() = "tran_item_no" Then
   dw_product.Retrieve( Integer( Data ) )
   If dw_product.RowCount() <> 1 Then
      MessageBox( "Error", "Product No: '" + Data + &
                  "' is not existing in the master file." + "~n" + &
                  "Pl. Enter in the Master File First.", StopSign!, OK!,1)
      i_DoNotDisplayErrMsg = True
      Return 1
   End If
End If

If you run the application at this time, you may not be able to enter the data in "dw_tr_head" DataWindow. If you take a look at the SELECT statement for this DataWindow, you will find that the SELECT statement doesn't have all the columns from the "trans" table. Since, all the columns in the "trans" table are defined as "NOT NULL", and are not included in the SELECT statement, PowerBuilder automatically turns off the "Allow Update" property. So, what do we do? Click on the "dw_tr_head" DataWindow with the right mouse button and select the "Modify DataWindow" option. PowerBuilder now opens the "d_trans_data_entry_header" in the DataWindow painter. Select "Rows/Update Properties" from the menu. Turn on the "Allow Updates"option. Select "trans" for the "Table to Update" prompt. Select all the columns under "Updateable Columns". Select "Tran_no" from "Unique Key Column(s)" option and click the OK CommandButton.

Now, select "Design/Tab Order" from the menu and set the tab order to 10, 20, 30 from left to right. Select "Design/Tab Order" from the menu again and close the DataWindow painter. Enable "V Scrollbar" for the "dw_tr_detail" DataWindow. Turn off borders for the transaction DataWindows. Want to be fancy, then draw a rectangle around the transaction DataWindows, to make the user believe it as a single object.

You need to write the following script for both "m_mdi_menu" and "m_sheet_menu" menus.

// Object: "m_sheet_menu", "m_mdi_menu" menus.
// event: Clicked event for "Module/Transactions" menu
// option.

w_transactions l_sheet1
OpenSheet( l_sheet1, ParentWindow, 0, Cascaded! )

We are done. Right? No. We haven't written code for saving data in the database. Then declare a user-defined event "ue_save" for "w_transactions" window and map the event to "pbm_custom02" event id. Write the following code to that event.

// Object: "w_transaction" window
// Event: ue_save

Int lTranNo, lUpdateStatus
Long lTotalItems, i
Date lTranDate
String lTranType
dwItemStatus lRowStatus

If dw_tr_head.Rowcount() = 0 Then Return 0
lTotalItems = dw_tr_detail.RowCount()
lTranNo = dw_tr_head.GetItemNumber(1, "tran_no")
lTranDate = dw_tr_head.GetItemDate(1, "tran_date")
lTranType = dw_tr_head.GetItemString(1, "tran_type")

For i = 1 to lTotalItems Step 1
   lRowStatus = dw_tr_detail.GetItemStatus( i,0,Primary!)
   If ( lRowStatus <> New! ) and &
      ( lRowStatus <> NotModified! ) Then
        dw_tr_detail.SetItem( i, "tran_no", lTranNo )
        dw_tr_detail.SetItem( i, "tran_date", lTranDate )
        dw_tr_detail.SetItem( i, "tran_type", lTranType )
   End If
Next

lUpdateStatus = dw_tr_detail.update()
If lUpdateStatus = 1 Then
   Commit using sqlca ;
   dw_tr_head.Reset()
   dw_tr_detail.Reset()
   dw_product.Reset()
Else
   Rollback using sqlca ;
   MessageBox( "Database Error", SQLCA.SQLErrText )
   Return -1
End If

We are checking if the header DataWindow has a row or not. If not, there is nothing to process, so return, but, if there is a row, we read the information into variables. We are populating the same information in the detail DataWindow. We now have all the information required in the detail DataWindow. We then update the detail DataWindow. If the update is successful, we reset all the DataWindows, i.e., empty them, in order to make them ready for the next transaction.

Note: Even though you see few columns in the detail DataWindow, the actual SELECT statement has all columns in its list. Even though you delete some columns from the screen, values of the deleted columns ( from the screen ) are still available in the script ( as long as they are present in the SELECT list ).

Now, we are ready to run the application. Right? Again no. In the above script, you are simply calling the Update() for the transaction details DataWindow. What about updating the master table? For that, we need to write triggers in the database.

Watcom SQL ( Sybase's SQL AnyWhere ) supports four types of triggers, INSERT, DELETE, UPDATE, and UPDATE specific columns. This example requires the first three triggers. Invoke the database painter. Invoke the database administration painter from there.

INSERT Trigger on "trans" table

CREATE trigger tran_new_trig after insert on trans
REFERENCING NEW AS new_tran
FOR EACH ROW
BEGIN
   If "new_tran"."tran_type" = 'R' Then
      update "product_master"
         set "product_master"."product_balance" =
             "product_master"."product_balance" + "new_tran"."tran_qty"
         where "product_master"."product_no" = "new_tran"."tran_item_no"
   Elseif "new_tran"."tran_type" = 'T' Then
      update "product_master"
         set "product_master"."product_balance" =
             "product_master"."product_balance" + "new_tran"."tran_qty"
         where "product_master"."product_no" = "new_tran"."tran_item_no"
   Elseif "new_tran"."tran_type" = 'I' Then
       update "product_master"
          set "product_master"."product_balance" =
              "product_master"."product_balance" - "new_tran"."tran_qty"
          where "product_master"."product_no" = "new_tran"."tran_item_no"
   End If
END;

Type the above code in the database administration painter and execute it. Do the same for the next two triggers. We don't want to go into details, because, it has just few simple SQL statements.

DELETE Trigger on "trans" table

CREATE trigger tran_del_trig after delete on trans
REFERENCING old AS old_tran
FOR EACH ROW
BEGIN
   If "old_tran"."tran_type" = 'R' Or
      "old_tran"."tran_type" = 'T' Then
      Update "product_master"
         Set "product_master"."product_balance" =
             "product_master"."product_balance" - "old_tran"."tran_qty"
         Where "product_master"."product_no" = "old_tran"."tran_item_no"
   Elseif "old_tran"."tran_type" = 'I' Then
      Update "product_master"
         Set "product_master"."product_balance" =
             "product_master"."product_balance" + "old_tran"."tran_qty"
        Where "product_master"."product_no" = "old_tran"."tran_item_no"
   End If
END;

UPDATE Trigger on "trans" table

CREATE trigger tran_upd_trig after update on trans
REFERENCING old AS old_tran new AS new_tran
FOR EACH ROW
BEGIN
   If "old_tran"."tran_type" = 'R' Or
      "old_tran"."tran_type" = 'T' Then
      Update "product_master"
         Set "product_master"."product_balance" =
             "product_master"."product_balance" -
             "old_tran"."tran_qty" + "new_tran"."tran_qty"
         Where "product_master"."product_no" = "old_tran"."tran_item_no"
   Elseif "old_tran"."tran_type" = 'I' Then
      Update "product_master"
         Set "product_master"."product_balance" =
             "product_master"."product_balance" +
             "old_tran"."tran_qty" - "new_tran"."tran_qty"
         Where "product_master"."product_no" = "old_tran"."tran_item_no"
   End If
END;

Now is the right time to close database painter and run the application and test it. Check if "product_master" is properly updated or not.

Home	Previous Lesson: Multiple Table DataWindow Update Next Lesson: DropDown/Child DataWindows