a

Form Master / Detail (Update)

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.

   

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.

   

 

Creating the link between the Master and Detail forms.

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.