6 - Use Data in a Stacked Form

Last updated 16/12/2025

This documentation will explain how to use the _LookupQuery display type and stacked form data to filter information 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 deployed 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.

In the Portfolio application, the Sells form is stacked above the Allocations form.

The BuyStockRemaining query's

SELECT

statement uses the the BuyKey and TransactionDate fields for the buy dates to be displayed in the Buy field's lookup list.

The

Holding

field calculates how many buy shares are still held and looks up all allocation records linked to the BuyKey and sums their quantities.

The

HAVING

clause is used to retrieve data for a selected Portfolio record and the selected stock in the form stack.

Add the BuyStockRemaining Query

1. Click Data in the menu followed by Queries in the sub-menu.

Figure 1 - Queries menu item

2. Click the Add Item button and type BuyStockRemaining in the Data Source ID field.

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

Figure 2 - Add BuyStockRemaining query

4. Click the SQL tab.

Figure 3 - SQL tab

5. Click the Copy button for the syntax below.

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.

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. Edit the following fields:

• Select Date in the Data Type field
• Type 8 in the Size field
• Select _Date in the Default Display Type field

Figure 7 - Edit TransactionDate field

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

Figure 8 - Save button

12. Select the Holding record.

Figure 9 - Holding record

13. Edit the following fields:

• Select Integer in the Data Type field
• Type 4 in the Size field
• Select _Integer in the Default Display Type field

Figure 10 - Edit Holding field

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

Figure 11 - Save button

15. Click the Parameters tab.

Figure 12 - Parameters tab

16. Click the Add Parameters button.

Figure 13 - Add Parameters button

17. Type PortfolioKey in the Parameter ID field.

18. Type

{{five.stack.Portfolio.PortfolioKey}}

in the Parameter field.

Figure 14 - Add PortfolioKey parameter

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

Figure 15 - Save button

20. Click the Add Parameters button.

Figure 16 - Add Parameters button

21. Type StockKey in the Parameter ID field.

22. Type

{{five.form.Sells.StockKey}}

in the Parameter field.

Figure 17 - Add StockKey parameter

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

Figure 18 - Save button

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

Figure 19 - Save button

Attach the BuyStockRemaining Query

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

Path: Allocations form > General page > Buy field

1. Click Visual in the menu followed by Forms in the sub-menu.

Figure 20 - Forms menu item

2. Select the Allocations record in the list and 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. Select _LookupQuery in the Display type field, click the Cancel button in the Reference Form field, and select BuyStockRemaining in the Lookup Data Source field.

Figure 25 - Edit Buy record

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

Figure 26 - Save button

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

Figure 27 - Save button

tip

This is a good time to deploy/run the Portfolio application to see how the BuyStockRemaining query works on the Allocations page on the Sells form!

Test the Allocations Page

1. Select the Growth Portfolio record in the list and click the Down button in the form app bar.

Figure 28 - Down button

2. Select Buys in the menu.

info

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

• 2025/11/30 - 10000 shares
• 2025/12/02 - 3000 shares

Figure 29 - TSLA buy records

3. Select Sells in the menu and click the Add Item button.

4. Select TSLA in the stock field and the current date in the Transaction Date field.

5. Type 11000 in the Quantity field, 400 in the Price field, 10.00 in the Fees field, and press tab.

Figure 30 - Add TSLA sell record

6. Click the Add Allocations button.

Figure 31 - Add Allocations button

7. Click the lookup icon in the Buy field.

info

The BuyStockRemaining query is now filtering the list to only show the buy transaction dates for the selected stock (TSLA).

Figure 32 - Buy field

8. Select 2025-11-30 in the Buy field.

9. Type 11000 in the Quantity field.

Figure 33 - Add allocation record

Problem

At this point we have been allowed to enter 11000 in the Quantity field. This is because in total we own 14000 shares in TSLA 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.

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

Figure 34 - Cancel button

11. Close the browser tab and return to Five.