Chapter 3
Add related data, calculated data and format numbers
Last updated
Add related data, calculated data and format numbers
Last updated
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.
Calculations
Formatting Numbers
Value Lists (review)
Relations (review)
Data Grid (review)
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.)
Let's create a relation so that we can show all the details of a single order.
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.
Next, we'll place another Data Grid on the form, this time choosing a related data source.
When the Columns Configurator appears, click the ... button to choose a different data source.
Expand Related Foundset to find your relation orders_to_order_details. Select it and click OK.
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 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.
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.
Next, let's make that Unit Price column look nicer by applying a Number Format.
Select the Unit Price column
Edit the format property and click the ... to open the Number Format Editor.
Choose ¤#.00 for the Display Format
Choose #.00 for the Edit Format (we'll make this grid editable in the next chapter)
Click OK and save your editor. You can preview your changes in the NG Client.
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.
Select the Quantity column and set the max-width property to 150.
Do the dame for the Unit Price column.
Save and preview in the NG Client.
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.
For the last exercise in this chapter, we are going to create a calculation, to compute the subtotal of an order detail record.
From the Solution Explorer, locate your order_details table:
Resources > Database Servers > example_data > order_details
Double-click to open it in the Table Editor
At the bottom of the Table Editor, select the Calculations tab.
Click the Add button and name your calculation subtotal.
Select NUMBER as the data type and save. Then, click 'Open selected calculation' to open the script editor.
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.
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.
Now you may place your calculation on your grid, just like any other column.
From the pallet, drag a Column onto your grid component
Double-click the dataprovider property to open the Data Provider Chooser
You will see a calculations node, expand it and select your subtotal calculation
Set the headerTitle property to "Subtotal"
Set the max-width property to 150
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.
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!
