Chapter 3

Add related data, calculated data and format numbers

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_detailstable. (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.

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.

Place the Order Details Grid

Next, we'll place another Data Grid on the form, this time choosing a related data source.

Place Related Data Grid

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.

Order Details Grid Preview in NG Client

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.

Value List Editor: Products Value List

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 dame 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.

Create a Calculation

1. From the Solution Explorer, locate your order_details table: Resources > Database Servers > example_data > order_details

2. Double-click to open it in the 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.

function subtotal()
{
	return quantity * unitprice;
}

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.

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.

Calculation added to grid

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!