# Chapter 3 ## Overview In this chapter, we will add the order details to show which products have been ordered. We'll create several new relations and a data grid. We'll add a calculated value and format a number to the localized currency. ### Key Concepts Covered * **Calculations** * **Formatting Numbers** * **Value Lists** (review) * **Relations** (review) * **Data Grid** (review) ### Create a Relation First let's create a new relation to set up our next data grid. We will create relations to the `order_details`table. (This is a review from the previous chapter, so please review that it you need to.) ### Orders to Order Details Let's create a relation so that we can show all the details of a single order.

Relation Editor: orders_to_order_details

Create a new relation object. Select `example_data.orders` as the *from* data source. Select `example_data.order_details` as the *destination*. {% hint style="info" %} Notice that the Relation Editor correctly guesses the foreign key `order_details.orderid`. This is because it has the same name as the PK column in the source table. {% endhint %} ## Place the Order Details Grid Next, we'll place another Data Grid on the form, this time choosing a related data source.

1. When the Columns Configurator appears, click the `...` button to choose a different data source. 2. Expand Related Foundset to find your relation `orders_to_order_details`. Select it and click OK. 3. Now you will see the data providers for the `order_details` table. Choose the following data providers, set their title text and click OK. * productid * quantity * unitprice #### Position the Grid Position your grid to take the remaining 75% of the width and stretch near the bottom edge. Set the `cssProperty` of the grid to the following values: * left: calc(25% + 10px) This is the same value as the other fields and labels * right: 10px * bottom: 10px Save your editor and preview the changes in the NG Client. You can see that as you select different order record, the related order details records of each order are loaded in the grid. However, this grid does not look great. Let's make it nicer by adding a product lookup and formatting the price.

### Create a Products Value List Let's create a value list to show a nicer lookup on the product name for the product id foreign key. This will also help us when we make the grid editable.

Create a new Value List with the following properties. * Name: products * Type: Table Values * Data Source: `example_data.products` * Display Value : `productname` * Real Value: `productid` Save the editor and attach the new Value List to the product column in the grid by editing the column's `valuelist` property and choosing the `products` Value List. ### Format the Unit Price Next, let's make that Unit Price column look nicer by applying a Number Format. 1. Select the Unit Price column 2. Edit the format property and click the `...` to open the **Number Format Editor**. 3. Choose `¤#.00` for the Display Format 4. Choose `#.00` for the Edit Format (we'll make this grid editable in the next chapter) 5. Click OK and save your editor. You can preview your changes in the NG Client. ### Adjust the Column Widths You may have noticed that the column widths are auto-sized, which is convenient. However, the quantity and the unit price are both reliably skinny values. Let's set a default width for these columns to leave more room for the product name. 1. Select the Quantity column and set the `max-width` property to `150`. 2. Do the same for the Unit Price column. 3. Save and preview in the NG Client.

Order Details Grid with Value list, formatting and column width settings

Notice your product name is displayed instead of the ID. The Unit Price is formatted with the localized currency, and while the product column stretches, the other two maintain a max-width. ## Create a Calculation For the last exercise in this chapter, we are going to create a **calculation**, to compute the subtotal of an order detail record.

1. From the [Solution Explorer](https://docs.servoy.com/reference/servoy-developer/solution-explorer), locate your `order_details` table:\ `Resources > Database Servers > example_data > order_details` 2. Double-click to open it in the [Table Editor](https://docs.servoy.com/reference/servoy-developer/object-editors/table-editor) 3. At the bottom of the Table Editor, select the Calculations tab. 4. Click the `Add` button and name your calculation `subtotal`. 5. Select `NUMBER` as the data type and save. Then, click 'Open selected calculation' to open the script editor. ### Write the Calculation Script In the script editor, you will see a JavaScript function stub called `subtotal`. Every calculation is a function that must return a value, in this case, you will simply multiply the quantity by the unit price. Enter the following. ```javascript function subtotal() { return quantity * unitprice; } ``` {% hint style="info" %} When you edit code in a calculation, a number of objects, including other data providers are in scope. You can get code complete by typing `CTRL-SPACE`. This is helpful to find the names of available data providers. {% endhint %} ### Add the Calculation to Your Grid Now you may place your calculation on your grid, just like any other column.

Add the subtotal calculation to the grid

1. From the pallet, drag a Column onto your grid component 2. Double-click the `dataprovider` property to open the Data Provider Chooser 3. You will see a **calculations** node, expand it and select your `subtotal` calculation 4. Set the `headerTitle` property to "Subtotal" 5. Set the `max-width` property to `150` 6. Finally, set the display `format` property to `¤#.00`, as you did for the Unit Price column Save your editor and preview your changes in the NG Client. You can see that the subtotal column displays the calculated results for each line in each order.

{% hint style="success" %} Calculations are called when they are displayed and referenced in your scripts. But they are also refreshed when data is changed. This means they are *always* up to date! {% endhint %}