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 should to be increased, and should be 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.

	DataWindow control Name	DataWindow Object
Left Top	dw_tr_head	d_trans_data_entry_header
Left Bottom	dw_tr_detail	d_trans_data_entry_detail
Right	dw_product	d_display_product

Assign m_sheet_menu menu to this window and 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 display 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. (Click on the 'Event List' tab page in the Script view, invoke popup menu and select Add menu option. Make sure that you are creating this event for the window, and not for any control within 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 Format/Tab Order from the menu and set the tab order to 10, 20, 30 from left to right. Select Format/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, the answer is 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.

Sybase's Adaptive Server Anywhere supports four types of triggers, INSERT, DELETE, UPDATE, and UPDATE specific columns. This example requires the first three triggers. Invoke the database painter and write the following triggers in the ISQL view and execute them.

Home	Previous Lesson: Multiple Table DataWindow Update Next Lesson: INSERT Trigger On trans Table