Tutorial: Form Master / Detail (Update)

A master-detail interface shows a master list and details for the currently selected item. A master area can be a form, a list or an element tree, and a detail area can be a form, a list or an element tree that is usually placed below or next to the master area.

A teacher-detail relationship can be a one-to-many type relationship. Examples of a master-detail relationship are:

• A set of purchase orders and a set of line items that belong to each purchase order.
• An expense report with a set of expense items.
• A department with a list of employees that belong to it.

An application can use this master-detail relationship to allow users to navigate through the purchase order data and view the detailed data of the individual items related only to the selected master purchase order.

PHP Master – Detail

example This example develops a master application / complete detail. To build this application two forms are created. The first (Maestro) is based on the order table and the second (Detail) is based on orders_details.

Creating the master form

1. Create a new simple form application based on the table orders.

2. In the application menu, click Field Positioning.

3. Delete the fields: ShipVia, ShipRegion and ShipPostalCode

4. On the application menu, click Blocks in the Design folder.

5. Change the name and label of the block to General Information

6. Click Create New Block.

7. Enter the values ​​for the Name, Label and Shipping Information attributes.

8. Click Create New Block again.

9. Enter the values ​​for the Name, Label and Order Details attributes.

10. Edit the properties of the blocks created according to the following image:

11. On the application menu, click Select fields to organize the field according to the following table:

Block	Field
General Info	OrderID
CustomerID
EmplyeeID
OrderDate
RequiredDate
PriceOrder
Ship Info	ShippedDate
ShipAdress
Freight
ShipCity
ShipState
ShipCountry
Order Details

Creating the detail form

1. Create a new grid application of type Editable grid view based on the order_details table

2. Generate the source code of the details form by clicking Generate source in the ScriptCase toolbar.

Creating the link between the Master and Detail forms.

1. Return to the master form by clicking on the applications tab

2. On the application menu, click New detail in the Master form / detail folder.

3. Enter the details for Name and Label. Click on Create.

4. Select the detail form and Next.

5. Following the wizard, assign the OrderID fields of the two forms and save it.

Ready, the Master / Detail form is already created, but now we will customize the fields and create validation rules for the application.

Customizing the form fields

1. Still in the detail form, open the Positioning fields menu.

2. Remove the OrderDetailsID and OrderID fields.

3. In the application menu, click Fields >> ProductID.

4. Change the type of data to select.

5. Access the edit search tab for the ProductID field.

6. Enter the following instructions for the SQL Select Statement:

SELECT ProductID, ProductName 
FROM products 
ORDER BY ProductID

7. Change the Allow negative value attribute to Yes.

8. Select the New field option from the Field menu.

9. Enter 1 for field quantity and click Next.

10. Select Currency for the Property Type and enter Total for Name and Label. Click on Create.

Creating methods for form details

The first method is used to calculate the total value of the sum of all items for each order.

1. In the application menu, select the Programming-> PHP Methods folder.

2. Create a new method called total_calculation

3. Enter the following source code for the calculation method:

{total} = ({unitprice} * {quantity}) - {discount};

The second method will be used to verify that the item to be inserted into the application still exists in stock.

4. Create a new method called check_stock

5. Enter the following source code for the check_stock method:

sc_lookup (dataset, "SELECT unitsinstock 
FROM products 
WHERE productid = {productid}");

if ({dataset} [0] [0] <{quantity}) {
sc_error_message ("Insufficient Quantity");
sc_set_focus ('quantity');
}
if ({unitprice}! = 0 && {quantity}! = 0) {
{total} = ({unitprice} * {quantity}) - {discount};
}

The third method is used to update the request form, the total value of the sum of all the elements of a particular request.

6. Create a new method called update_master

7. Enter the following source code for the update_master method:

sc_lookup (dataset, "select SUM ((quantity * unitprice) - discount) FROM order_details 
WHERE orderid = {orderid}");

if (! empty ({dataset [0] [0]}))
{
$ total = {dataset [0] [0]};
sc_exec_sql ("UPDATE orders SET priceorder = $ total WHERE orderid = {orderid}"); 
sc_format_num ($ total, '.', ',', 2, 'S', '1', '');
sc_master_value ('priceorder', $ total);
} else {

$ total = 0;
sc_format_num ($ total, '.', ',', 2, 'S', '1', '');
sc_master_value ('priceorder', $ total);

}

Creating Ajax events for the detail form.

1. In the application menu, select the Ajax Events folder and click New Ajax Event.

2. Select the Discount field and select the onChange event. Click on the Create event button to finish.

3. In the event editor, call the method calculate_total.

4. Create a new Ajax event for the ProductID field.

5. Enter the following source code for the onChange event:

sc_lookup (dataset, "select unitprice from products 
WHERE productid = '{productid}'");

if (! empty ({dataset} [0] [0])) {

{unitprice} = {dataset} [0] [0];
sc_set_focus ('quantity');
}
else {
{unitprice} = 0;
sc_set_focus ('unitprice');
}

6. Create a new Ajax event for the Quantity field.

7. Enter the following source code for the onChange event:

sc_lookup (dataset, "select unitprice from products 
WHERE productid = '{productid}'");

if (! empty ({dataset} [0] [0])) {

{unitprice} = {dataset} [0] [0];
sc_set_focus ('quantity');
}
else {
{unitprice} = 0;
sc_set_focus ('unitprice');
}

8. Create a new OnChange Ajax event for the UnitPrice field.

9. In the event editor call the total_calculated method.

Calling the methods from the form created events.

1. In the application menu, select the Events folder (see image below). As was done for field events, the methods are called in the form events. In the table below there is a list of methods that should be called in each event.

Event	Method
onBeforeInsert	check_stock ();
onAfterInsert	update_master ();
onBeforeUpdate	check_stock ();
onAfterUpdate	update_master ();
onAfterDelete	update_master ();
onLoadRecord	check_stock ();

2. Generate the source code of the details form by clicking Generate Source on the ScriptCase toolbar.

3. Run the master application through the button on the ScriptCase toolbar.

To see more examples created with Scriptcase, click here.