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.
In the Form Editor, select the Product column on your Order Details grid
In the Component Properties Editor, set the
editType
propery toTYPEAHEAD
Do the same for the Quantity column, this type setting the property to TEXTFIELD
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 .
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.
From the pallet, drag a Button component to the form
In the Component Properties Editor, set the
text
property to "New Order"Set the
cssPosition
property to have the button anchor right:45,
10
,-1,-1,140,30
Double-click the
onAction
property to open the Method Selection WizardSelect the option to create a method in the form and give it a name:
newOrder
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.
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.
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.
Choose the following fields:
shipaddress
,shipcity
andshipcountry
.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.
Select the Customer field and double-click its
onDataChange
event to open the Method Selection Wizard.Create a new method in the form named
onDataChangeCustomer
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.
That's it! Just a few lines to copy the data over. Save all your editors and preview the changes 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.
From the pallet, drag a button on to your form. Set the
text
to "Add Item"Double-click the
onAction
event and create a new method in the form calledaddItem
.Set the
cssPosition
property to have the button anchor right: 240,10,-1,-1,115,30Create 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.
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
![](https://docs.servoy.com/~gitbook/image?url=https%3A%2F%2Ffiles.gitbook.com%2Fv0%2Fb%2Fgitbook-x-prod.appspot.com%2Fo%2Fspaces%252FjpWd52BKwABWxF2lScUK%252Fuploads%252Fi18lqU7HzrZrMADOJlRp%252Fimage.png%3Falt%3Dmedia%26token%3Df9ffa144-5011-473c-beb9-7f15c58ff43b&width=768&dpr=4&quality=100&sign=ac062068&sv=1)
order_details_to_produtcs
Create a new relation object. Select
example_data.order_details
as the from data source. Selectexample_data.products
as the destination.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.
Select the Order Details grid and in the Component Properties Editor, double-click the
onColumnDataChange
event to open the Method Selection Wizard.Create the new method in the form and click OK
Once again the method stub is added to your
orders.js
file
Enter the following code into your method stub:
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.
Type the first few characters of the file you are searching for, i.e. "orders"
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
.
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.
In this code, we iterate over the Order Details and tally the subtotal of each line.
Create a local variable for the sum
Create a for loop (use code completion -
CTRL-SPACE
) to iterate over records in the relatedorders_to_order_details
foundset.Access each
order_detail
record and add itssubtotal
calculation to the sum using the+=
operator.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.
From the pallet, drag the Data Label component onto your form
Double-click the dataProvider property in the Component Properties Editor and select the
order_total
calculation.Edit the
format
property and apply a format to a localized currency.You may edit the
cssPosition
property to align it next to your Add Item button:270,130,-1,-1,80,30
Edit the styleClass property and add a style of
font-weight-bold
You may add another label next to it with the text "Order Total"
Save your editors and preview your work in the NG Client.
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.
Last updated