Chapter 4

Working with data, scripted logic

Overview

In this chapter you'll begin to work with data and connect some scripted logic to UI events. We'll make our Order Details grid editable, add a button and code to create a new order, create a more advanced calculation field and more.

Key Concepts Covered

• Editing Data

• Foundsets

• UI Events

• Scripted Logic

• Calculations (Review)

Make the Grid Editable

Let's begin by adjusting our Order Details grid to accept user input.

Set the grid column editType property

1. In the Form Editor, select the Product column on your Order Details grid

2. In the Component Properties Editor, set the editType propery to TYPEAHEAD

3. Do the same for the Quantity column, this type setting the property to TEXTFIELD

4. Repeat the step for the Unit Price column

Save your changes and preview the result in the NG Client. You will be able to double-click into the fields and edit them. Note that the subtotal calculation is not editable .

Preview the eidtable grid

Calculation Refresh Try editing the Quantity or Unit Price column. You'll notice that the subtotal calculation is instantly updated when you change the inputs.

TAB Key Support After you begin editing a grid cell, try tabbing through them with the TAB key.

Create new Records

Now that we can edit all the data on an order, it's time to give the user the ability to add new records. Let's begin by placing a button for new orders and hooking it up to some code.

Place a button for new orders and create an onAction handler

1. From the pallet, drag a Button component to the form

2. In the Component Properties Editor, set the text property to "New Order"

3. Set the cssPosition property to have the button anchor right: 45,10,-1,-1,140,30

4. Double-click the onAction property to open the Method Selection Wizard

5. Select the option to create a method in the form and give it a name: newOrder

6. Choose Create Private and click OK.

The newOrder method stub is created

The orders.js file opens in the Script Editor and the new method stub newOrder is created. The onAction event of the New Order button is handled by this method. You are ready to fill in the logic.

Each form has its own .js file to manage the scripting for the form. This is where most event handlers and other UI logic will be implemented.

Add the New Record Logic

Next, you'll add some code to create the record.

function newOrder(event) {
	foundset.newRecord();
}

Here you will use the form's foundset object, which manages all of the data access. Invoke the newRecord method to create a new record object in the foundset.

Code Complete Type CTRL-SPACE at any time to get code completion. You will also get completion after the dot (.) character.

Lookup Ship Info from Customer

Let's explore a bit more the idea of handling UI events in code. This time we will lookup the order's shipping info from the related customer record.

Add Ship Info Fields

First, let's get some of the ship info fields on the form.

1. Click the Place Fields button to show the Place Fields Wizard. (You used this wizard when you first created the form)
2. Choose the following fields: shipaddress, shipcity and shipcountry.

3. You can adjust their cssPosition property to align next to the other fields: left=calc( 25% + 200px)

Add a Data Change Handler

The ship info will be empty when a new record is created, but we can add some logic to lookup from the related customer record whenever the customerid changes.

1. Select the Customer field and double-click its onDataChange event to open the Method Selection Wizard.

2. Create a new method in the form named onDataChangeCustomer

3. Create Private and click OK

The new method stub is created in the same file as the newRecord method.

Add the Lookup Logic

In the new method, enter the following code to lookup the address info from the related customer and enter it as shipping info.

function onDataChangeCustomer(oldValue, newValue, event) {
	
	// Lookup ship info from customer address
	shipaddress = orders_to_customers.address;
	shipcity = orders_to_customers.city;
	shipregion = orders_to_customers.region;
	shippostalcode = orders_to_customers.postalcode;
	shipcountry = orders_to_customers.country;
	
	return true;
}

That's it! Just a few lines to copy the data over. Save all your editors and preview the changes in the NG Client.

Preview of the ship info lookup in the NG Client

You can see that a blank, new record is created and when the user selects the customer for the order, the ship info is immediately looked-up from the related table.

Create Related Records

Now that we can create order records, the user will want to add Order Detail records as well. Let's add a button and a method to create the related records.

1. From the pallet, drag a button on to your form. Set the text to "Add Item"

2. Double-click the onAction event and create a new method in the form called addItem.

3. Set the cssPosition property to have the button anchor right: 240,10,-1,-1,115,30

4. Create private. Click OK and Show.

The addItem method stub is created in the orders.js file and ready for your logic. Enter the following code to your method.

function addItem(event) {
	
	// create the record 
	orders_to_order_details.newRecord();
	
	// set the quantity default to 1
	orders_to_order_details.quantity = 1;
}

Here you can see that the relation, orders_to_order_details, can be used in code to reference the related JSFoundSet object and the newRecord method is available. You can also reference the data providers of the related foundset, such as quantity. Save your editors and try it out in the NG Client.

Related Foundsets You just created a record through a related foundset. The Servoy platform understands your relation and will insert the record with the correct foreign key automatically!

Lookup Product Price

Let's add a little more logic to our form, again using UI event handlers. This time, let's lookup the price of a product and auto-fill the Unit Price column for an Order Detail.

Create a Relation to Products

To be able to lookup the price of the product, we must first create a relation to the products table. You've done a few of these by now, so it should just take a moment

Relation Editor: order_details_to_produtcs

1. Create a new relation object. Select example_data.order_details as the from data source. Select example_data.products as the destination.

2. Match the productid column from both tables. Save your editor and continue.

Create a Handler for Data Change Event

Next, we will implement a handler for the data change event, so that we can update the price based on the selected product.

1. Select the Order Details grid and in the Component Properties Editor, double-click the onColumnDataChange event to open the Method Selection Wizard.

2. Create the new method in the form and click OK

3. Once again the method stub is added to your orders.js file

Enter the following code into your method stub:

function onColumnDataChange(foundsetindex, columnindex, oldvalue, newvalue, event, record) {
	
	// Check if the first column (Product) was changed
	if(columnindex == 0){
		orders_to_order_details.unitprice = 
			orders_to_order_details.order_details_to_products.unitprice;
	}
	return true;
}j

This data change event is a little different than the one for the Type Ahead used previously for the Custom field. This event is called when any of the grid's columns (or rows) have a data change. Fortunately, more information is passed in as arguments to help us figure it out.

Evaluate which column changed The columnindex parameter can be used to infer which column has changed. The Product column is the first column in the grid and therefore index 0.

Lookup Product Price Once again, using the relations you have made, you can assign a value to the unitprice column in the order_details table from the unitprice column in the products table.

Relation Chaining Notice how you can chain relations together to easily traverse your data model in code. The Servoy platform will handle all the querying and updating without a hitch.

Create an Order Total Calculation

In the final step in this chapter, we will create another, more complex, calculation to derive the total value of an order.

Using the Servoy Resource Locator

Open your orders table in the Table Editor. This time we'll learn to use the Servoy Resource Locator to quickly find and open the table.

The Servoy Resource Locator

1. From the main toolbar, click the button (alt+shift+k) to open the Servoy Resource Locator.
2. Type the first few characters of the file you are searching for, i.e. "orders"

3. Use the mouse or down arrow key to choose the resource to open and click OK or type ENTER.

Create the Calculation Script

Create a new calculation, just as you did in the previous chapter. The name should be order_total and the data type should be NUMBER.

Using Code Completion CTRL-SPACE

Code Completion is Great! Notice in this image that code completion can be used to generate a template of a for loop and other constructs. After which you can TAB through each element of the template.

function order_total()
{
	var sum = 0;
	for (var i = 1; i <= orders_to_order_details.getSize(); i++) {
		var record = orders_to_order_details.getRecord(i);
		sum += record.subtotal;
	}
	return sum;
}

In this code, we iterate over the Order Details and tally the subtotal of each line.

1. Create a local variable for the sum

2. Create a for loop (use code completion - CTRL-SPACE) to iterate over records in the related orders_to_order_details foundset.

3. Access each order_detail record and add its subtotal calculation to the sum using the += operator.

4. Every calculation must return a value, in this case the sum variable.

Place a Data Label - Order Total

We are finally ready to place the order_total calculation on the form. Let's use a new component, Data Label to show the total. This component is a regular label, but binds directly to a data provider and allows us to apply a format.

1. From the pallet, drag the Data Label component onto your form

2. Double-click the dataProvider property in the Component Properties Editor and select the order_total calculation.

3. Edit the format property and apply a format to a localized currency.

4. You may edit the cssPosition property to align it next to your Add Item button: 270,130,-1,-1,80,30

5. Edit the styleClass property and add a style of font-weight-bold

6. You may add another label next to it with the text "Order Total"

Save your editors and preview your work in the NG Client.

Fully editable records and dynamic calculations

Chapter Complete. Nice work! Now you should be able to:

• Create a new order record

• Lookup and attach a customer, which does an auto-fill of the ship info.

• Next you should be able to add order detail records.

• As you lookup the product, you should get an auto-fill of the unit price.

• Finally, you should see your calculations for subtotal and order total automatically refreshing as you make edits.