Tutorial: Form Master / Detail (Update)

A master–detail interface displays a master list and the details for the currently selected item. A master area can be a form, list or tree of items, and a detail area can be a form, list or tree of items typically placed either below or next to the master area.

A master–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 belonging to each purchase order
  • An expense report with a set of expense line items
  • A department with a list of employees belonging to it.

An application can use this master-detail relationship to enable users to navigate through the purchase order data and see the detail data for line items only related to the master purchase order selected.

PHP Master–detail Example

In this example a complete master / detail application is developed. To build this application two forms are created. The first (Master) is based on the orders table and the second (Detail) is based on the orders_details.

master-detail

Creating the Master form

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

2. In the application menu click in Fields Positioning.

3. Remove the fields: ShipVia, ShipRegion e ShipPostalCode

4. In the application menu click in Blocks from the Layout folder.

5. Change the block name and label to General Info

6. Click in Create New Block.

7. Enter the values for the attributes Name, Label and Ship Info

8. Click in Create New Block again.

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

10. Edit the created blocks properties according to the image below:

11. In the application menu click in Select Fields to organize the field according to the table below:

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 table order_details

2. Generate the detail form source code by clicking in Generate Source in the ScriptCase toolbar.

1. Return to the Master Form by clicking on the of applications tab

2. In the application menu click in New Detail from the Master/Detail Form folder.

3. Enter Details for Name and Label. Click in Create.

4. Select the detail form and Next.

5. Following the wizard assign the OrderID fields from 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 menu Fields Positioning.

2. Remove the fields OrderDetailsID and OrderID.

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

4. Change Data Type to Select.

5. Access the Edition Lookup tab for the field ProductID.

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

SELECT ProductID, ProductName 
FROM products 
ORDER BY ProductID

7. Change the attribute Allow negative value to Yes.

8. Select the New Field option on the Field menu.

9. Enter 1 to field quantity and e Click in Next.

10. Select Currency for the property Type and enter Total for Name and Label . Click in 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 folder Programming->Methods PHP .

2. Create a new method called calculate_total

3. Enter the following source code to the calculate_total method:

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

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

4. Create a new method called check_stock

5. Enter the following source code to 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 items of a particular request.

6. Create a new method called update_master

7. Enter the following source code to 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 events Ajax for the detail form

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

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

3. In the event editor call the method calculate_total.

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

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 field Quantity.

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 field UnitPrice.

9. In the event editor call the method calculate_total.

Calling the methods from the form created events

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

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

1. Generate the detail form source code by clicking in Generate Source in the ScriptCase toolbar.

2. Run the Master application through the button in the ScriptCase toolbar.

 

To see more examples created with Scriptcase click here

Tutorials in the same category

Form with special fields

In this example, we'll see how to configure a couple of Scriptcase special fields, in a form applica...

Master-Detail (Single-to-Single)

In this example, a Master / Detail complete application, where we get an edit form of categories and...

Generate PDF from Forms

In this example we will see how to configure the application form to display the button to print the...

Form Mobile

Scriptcase Form applications are generated with mobile responsiveness automatically. Selectin...

Comment this post