# Chapter 4 ## 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.

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 .

{% hint style="success" %} **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. {% endhint %} {% hint style="info" %} **TAB Key Support**\ After you begin editing a grid cell, try tabbing through them with the TAB key. {% endhint %} ## 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 `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. {% hint style="info" %} 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. {% endhint %} ### Add the New Record Logic Next, you'll add some code to create the record. ```javascript function newOrder(event) { foundset.newRecord(); } ``` Here you will use the form's **foundset** object, which manages all of the data access. Invoke the [newRecord](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsfoundset#newrecord) method to create a new record object in the foundset. {% hint style="info" %} **Code Complete**\ Type `CTRL-SPACE` at any time to get code completion. You will also get completion after the dot (`.`) character. {% endhint %} ## 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. ![](https://3933488479-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjpWd52BKwABWxF2lScUK%2Fuploads%2Fgit-blob-062f26d335858535a607491b3e1c3c7127cc3991%2Fimage.png?alt=media) 1. Click the ![](https://3933488479-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjpWd52BKwABWxF2lScUK%2Fuploads%2Fgit-blob-089f3161df74a90af58d432c9bfeebc9367f03e2%2Fimage.png?alt=media)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. ```javascript 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. ```javascript 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. {% hint style="success" %} **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! {% endhint %} ## 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: ```javascript 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. {% hint style="success" %} **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. {% endhint %} ## 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**](https://docs.servoy.com/reference/servoy-developer/object-editors/servoy-resource-locator) to quickly find and open the table.

1. From the main toolbar, click the ![](https://3933488479-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjpWd52BKwABWxF2lScUK%2Fuploads%2Fgit-blob-7b885c4fe460b398af6cf439f6ad12203c120ac6%2Fimage.png?alt=media) 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`.

{% hint style="info" %} **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. {% endhint %} ```javascript 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

{% hint style="success" %} **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. {% endhint %}