6 - Use Data in a Stacked Form

Last updated 15/12/2023

This documentation is to demonstrate how you can use the display type _LookupQuery and use the data in a stacked form to filter data on the current form. To do this, we use the

form

property on the

Five

object.

The

form

property provides the dynamic properties

<actionID>.<fieldID>

calculated from your form stack.

When we ran the Portfolio application we saw that the Buy field on the Allocations form was listing the buy transaction dates for all stocks. We will write a query interacting with Five's API to know the transaction dates specific to the stock selected on the Sells form.

The Sells form is stacked above the Allocations form.

Navigate to Queries

1. Click Data in the menu.

2. Click Queries in the sub-menu.

Figure 1 - Queries menu item

Add the BuyStockRemaining Query

1. Click the Add Item button.

2. Type BuyStockRemaining in the Data Source ID field.

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

Figure 2 - Add the BuyStockRemaining query

4. Click the SQL tab.

Figure 3 - SQL tab

5. Click the Copy button for the below syntax.

BuyStockRemaining

SELECT
    BuyKey,
    DATE_FORMAT(TransactionDate, "%Y-%m-%d") AS TransactionDate,
    Quantity - IFNULL((SELECT SUM(Allocation.Quantity) FROM Allocation WHERE Allocation.BuyKey = Buy.BuyKey),0) AS Holding,
    Buy.PortfolioKey AS PortfolioKey,
    Buy.StockKey AS StockKey
FROM
    Buy
HAVING
    (PortfolioKey = ?) AND
    (StockKey = ?)
ORDER BY
    TransactionDate

6. Paste the syntax in the SQL Editor.

info

The query is going to use the TransactionDate and Holding query fields to know how much we are currently holding of each stock per transaction date.

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

Figure 4 - Save button

8. Click the Fields tab.

Figure 5 - Fields tab

9. Select the TransactionDate record.

Figure 6 - TransactionDate record

10. Either click the Edit button in the form app bar, or click directly in the Data Type field.

11. Click the lookup icon in the Data Type field and select Date.

12. Type 8 in the Size field.

13. Click the lookup icon in the Default Display Type field and select _Date.

Figure 7 - Edit the TransactionDate field

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

Figure 8 - Save button

15. Select the Holding record.

Figure 9 - Holding record

16. Either click the Edit button in the form app bar, or click directly in the Data Type field.

17. Click the lookup icon in the Data Type field and select Integer.

18. Type 4 in the Size field.

19. Click the lookup icon in the Default Display Type field and select _Integer.

Figure 10 - Edit the Holding field

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

Figure 11 - Save button

21. Click the Parameters tab.

Figure 12 - Parameters tab

22. Click the Add Parameters button.

Figure 13 - Add Parameters button

23. Type PortfolioKey in the Parameter ID field.

24. Type

{{five.stack.Portfolio.PortfolioKey}}

in the Parameter field.

Figure 14 - Add the PortfolioKey parameter

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

Figure 15 - Save button

26. Click the Add Parameters button.

Figure 16 - Add Parameters button

27. Type StockKey in the Parameter ID field.

28. Type

{{five.form.Sells.StockKey}}

in the Parameter field.

Figure 17 - Add the StockKey parameter

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

Figure 18 - Save button

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

Figure 19 - Save button

Navigate to Forms

info

The BuyStockRemaining query needs to be attached to the Buy field on the Allocations form using Five's display type _LookupQuery.

1. Click Visual in the menu.

2. Click Forms in the menu.

Figure 20 - Forms menu item

Attach the BuyStockRemaining Query

Path: Allocations form > General page > Buy field

1. Select the Allocations record in the list.

2. Click the Pages tab.

Figure 21 - Pages tab

3. Select the General record.

Figure 22 - General record

4. Click the Fields tab.

Figure 23 - Fields tab

5. Select the Buy record.

Figure 24 - Buy record

6. Either click the Edit button in the form app bar, or click directly in the Display Type field.

7. Click the lookup icon in the Display Type field and select _LookupQuery.

8. Click the Cancel button in the Reference Form field.

9. Click the lookup icon in the Lookup Query field and select BuyStockRemaining.

Figure 25 - Attach the BuyStockRemaining query

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

Figure 26 - Save button

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

Figure 27 - Save button

Run the Portfolio Application

tip

This is a good time to run the Portfolio application to see how the BuyStockRemaining query works!

1. Click the Run button in Five's toolbar.

Figure 28 - Run button

2. Select the Growth Portfolio record.

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

Figure 29 - Down button

Test the BuyStockRemaining Query

1. Select Buys in the menu.

info

In the Buys list, there are two NAB buy transaction records:

• 2023/12/13 - 1000 shares
• 2023/12/14 - 200 shares

Figure 30 - NAB records

2. Select Sells in the menu.

3. Click the Add Item button.

4. Click the lookup icon in the Stock field and select NAB.

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

6. Type 1100 in the Quantity field.

7. Type 1.00 in the Price field.

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

Figure 31 - Add a NAB sell transaction

9. Click the Add Allocations button.

Figure 32 - Add Allocations button

10. Click the lookup icon in the Buy field.

info

The Buy Stock Remaining query is now filtering the list to only show the buy transaction dates for the selected stock (NAB)!

Figure 33 - Filtered buy lookup

11. Select the date 2023-12-13 in the Buy field.

12. Type 1100 in the Quantity field.

Figure 34 - Add an allocation record

Problem

At this point we have been allowed to enter 1100 in the Quantity field. This is because in total we own 1200 shares in the NAB stock, however, we need to validate we aren't selling more than we own for a specific transaction date. The reason for this is the stock has been bought at different prices on different dates and we want to ensure the value we are selling. In Five, we can attach a function to the Quantity field that will use the metadata provided in the Buy field to allocate stock to a specific date.

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

Figure 35 - Cancel button

14. Close the browser tab and return to Five.