Skip to main content

3 - Execute a Calculation

Last updated 14/12/2023

This documentation will explain how to add a calculation function and attach it to the On Enter event to execute client-side when entering a form field.

When in Five's Code Editor you will be interacting with Five's API. In the
CalculateTotal()
function you will be using the following functions and properties:


actionID()
- is a function used on the
Five
object to return the current action ID.


- is a property used on the
Five
object which gives the fields for the current form.


- is a function used on the
Five
object that returns a
FiveError
indicating success.

1. Select Logic in the menu.

2. Select Code Editor in the sub-menu.


Code Editor Menu Item
Figure 1 - Code Editor menu item

Add the CalculateTotal Function

1. Click the Add New Code button.


Add New Code Button
Figure 2 - Add New Code button

2. Type CalculateTotal in the Function ID field.

3. Click the lookup icon in the Language field and select TypeScript.

4. Click the OKAY button.


Add the CalculateTotal function
Figure 3 - Add the CalculateTotal function

5. Click the Copy button on the code block below.

TypeScript
CalculateTotal
function CalculateTotal(five: Five, context: any, result: FiveError) : FiveError  {
if (five.actionID() === 'Buys') {
five.field.Total = five.field.Quantity * five.field.Price + five.field.Fees;
} else {
five.field.Total = five.field.Quantity * five.field.Price - five.field.Fees;
}
return five.success();
}


6. Paste the code block over the template in Five's Code Editor.

7. Click the Save Current Tab button.


Save Current Tab Button
Figure 4 - Save Current Tab button

tip
Five's Code Editor allows you to have multiple code blocks open, you can move between each code block by clicking the tabs at the top of the editor!

info
Once you have saved the CalculateTotal function, you can find the record in the Functions view! To navigate to the Functions view, click Logic in the menu followed by clicking Functions in the sub-menu.

Figure 5 - Navigate to the Functions view

tip
You can also open the function again in the Code Editor by clicking the Open Existing Function button and selecting the function!

Open Existing Function
Figure 6 - Open existing function
info
The CalculateTotal function needs to be attached to the Total field on the Buys and Sells forms. When executed, the function will calculate the total by multiplying the values in the Quantity and Price fields and if on the Buys form add the fees, else subtract the fees if on the Sells form.

1. Click Visual in the menu.

2. Click Forms in the sub-menu.


Forms Menu Item
Figure 7 - Forms menu item

Attach the CalculateTotal Function

Edit the Buys Form

Path: Buys form > General page > Total field > On Enter event

1. Select the Buys record in the list.

2. Click the Pages tab.


Pages Tab
Figure 8 - Pages tab

3. Select the General record.


General Record
Figure 9 - General record

4. Click the Fields tab.


Fields Tab
Figure 10 - Fields tab

5. Select the Total record.


Total Record
Figure 11 - Total record

6. Click the Events tab.


Events Tab
Figure 12 - Events tab

7. Either click the Edit button in the form app bar, or click directly in the On Enter field.

8. Click the lookup icon in the On Enter field and select CalculateTotal.


On Enter Field
Figure 13 - On Enter field

9. Click the Save button in the form app bar.


Save Button
Figure 14 - Save button

10. Click the Save button in the form app bar above the list.


Save Button
Figure 15 - Save button

Edit the Sells Form

Path: Sells form > General page > Total field > On Enter event

1. Select the Sells record in the list and repeat the above steps.


On Enter Field
Figure 16 - On Enter field

Run the Portfolio Application

tip
This is a good time to run the Portfolio application to see how the CalculateTotal function works!

1. Click the Run button.


Run button
Figure 17 - Run button

2. Select the Growth Portfolio record in the list.

3. Click the Down button in the form app bar.


Down Button
Figure 18 - Down button

Test the Buys Form

1. Click the Add Item button.

2. Click the lookup icon in the Stock field and select AMP.

3. Click the calendar icon in the Transaction Date field, select the current date and click the OK button.

4. Type 5000 in the Quantity field.

5. Type 1.20 in the Price field.

6. Type 10.00 in the Fees field, press tab.

info
The Total field now automatically calculates on entering the field.

Add an AMP Buy Record
Figure 19 - Add an AMP buy record

7. Click the Save button in the form app bar.


Save Button
Figure 20 - Save button

Test the Sells Form

1. Select Sells in the menu.

2. Click the Add Item button.

3. Click the lookup icon in the Stock field.

Problem
Currently, all the stocks are listed in the Stock lookup list, we need to make it that only the stocks we own are listed. We can fix this in Five by creating a query with metadata and attaching it to the Stock field to know what we currently own.

info
The Allocations page is on the same page as the Sells form as we set the navigation to continuous.

Sells Form
Figure 21 - Sells form

4. Click the Cancel button in the form app bar.


Cancel Button
Figure 22 - Cancel button

5. Close the browser tab and return to Five.
note
We'll import the Buy.csv file so that we have more data to work with.

1. Click Data in the menu.

2. Click Tables in the sub-menu.


Tables Menu Item
Figure 23 - Tables menu item

Import the Buy.csv File

1. Click here to download the Buy.csv file.

2. Click the Import CSV into Table button.


Import CSV into Table Button
Figure 24 - Import CSV into Table button

3. Click the lookup icon in the table field and select Buy.

4. Click the Choose File button.

5. Navigate your files, select, and open Buy.csv


Import Buy.csv
Figure 25 - Import Buy.csv

6. Click the Save button in the form app bar.


Save Button
Figure 26 - Save button

7. Click the OK button on successful import.


OK Button
Figure 27 - Ok button
info
You will need to update the buy data to use your Portfolio Key in order to see the data you just imported. We can do this by using an
UPDATE
statement in Five's SQL editor.

1. Click Data in the menu.

2. Click Queries in the sub-menu.


Queries Menu Item
Figure 28 - Queries menu item

Run an UPDATE Statement

info
There is no need to name or save the query as we are simply just going to run an
UPDATE
statement to update the Portfolio Key, so the imported data will use your Portfolio record.

1. Click the Add Item button.

2. Click in the Query field to open Five's Query Builder.


Open Five's Query Builder
Figure 29 - Open Five's Query Builder

3. Click the SQL tab.


SQL tab
Figure 30 - SQL tab

4. Click the Copy button for the syntax below.

MySQL
UPDATE Buy Table

UPDATE Buy SET PortfolioKey = (SELECT PortfolioKey FROM Portfolio)


5. Paste the Syntax in the SQL Editor.

6. Click the Run button.


Run Button
Figure 31 - Run button

info
You will see 18 rows have been updated, as shown in the image below.

Results
Figure 32 - Results

7. Click the Cancel button in the SQL Editor app bar.


Cancel Button
Figure 33 - Cancel button

8. Click the Cancel button in the form app bar.


Cancel Button
Figure 34 - Cancel button