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.

field.<fieldID>

- is a property used on the

Five

object which gives the fields for the current form.

success()

- is a function used on the

Five

object that returns a

FiveError

indicating success.

Navigate to the Code Editor

1. Select Logic in the menu.

2. Select Code Editor in the sub-menu.

Figure 1 - Code Editor menu item

Add the CalculateTotal Function

1. Click the 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.

Figure 3 - Add the CalculateTotal function

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

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.

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!

Figure 6 - Open existing function

Navigate to Forms

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.

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.

Figure 8 - Pages tab

3. Select the General record.

Figure 9 - General record

4. Click the Fields tab.

Figure 10 - Fields tab

5. Select the Total record.

Figure 11 - Total record

6. Click the 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.

Figure 13 - On Enter field

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

Figure 14 - Save button

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

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.

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.

Figure 17 - Run button

2. Select the Growth Portfolio record in the list.

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

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.

Figure 19 - Add an AMP buy record

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

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.

Figure 21 - Sells form

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

Figure 22 - Cancel button

5. Close the browser tab and return to Five.

Navigate to Tables

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.

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.

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

Figure 25 - Import Buy.csv

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

Figure 26 - Save button

7. Click the OK button on successful import.

Figure 27 - Ok button

Navigate to Queries

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.

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.

Figure 29 - Open Five's Query Builder

3. Click the SQL tab.

Figure 30 - SQL tab

4. Click the Copy button for the syntax below.

UPDATE Buy Table

UPDATE Buy SET PortfolioKey = (SELECT PortfolioKey FROM Portfolio)

5. Paste the Syntax in the SQL Editor.

6. Click the Run button.

Figure 31 - Run button

info

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

Figure 32 - Results

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

Figure 33 - Cancel button

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

Figure 34 - Cancel button