Skip to main content

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 property on the
Five
object.


The
form
property provides the dynamic properties
<formID>.<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.
1. Click Data in the menu.

2. Click Queries in the sub-menu.


Queries Menu Item
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.


Add the Buy Stock Remaining Query
Figure 2 - Add the BuyStockRemaining query

4. Click the SQL tab.


SQL Tab
Figure 3 - SQL tab

5. Click the Copy button for the below syntax.

MySQL
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.


Save Button
Figure 4 - Save button

8. Click the Fields tab.


Fields Tab
Figure 5 - Fields tab

9. Select the TransactionDate record.


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.


Edit the TransactionDate Field
Figure 7 - Edit the TransactionDate field

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


Save Button
Figure 8 - Save button

15. Select the Holding record.


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.


Edit the Holding Field
Figure 10 - Edit the Holding field

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


Save Button
Figure 11 - Save button

21. Click the Parameters tab.


Parameters Tab
Figure 12 - Parameters tab

22. Click the Add Parameters button.


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.



Add the PortfolioKey Parameter
Figure 14 - Add the PortfolioKey parameter

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


Save Button
Figure 15 - Save button

26. Click the Add Parameters button.


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.



Add the StockKey Parameter
Figure 17 - Add the StockKey parameter

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


Save Button
Figure 18 - Save button

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


Save Button
Figure 19 - Save button
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.


Forms Menu Item
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.


Pages Tab
Figure 21 - Pages tab

3. Select the General record.


General Record
Figure 22 - General record

4. Click the Fields tab.


Fields Tab
Figure 23 - Fields tab

5. Select the Buy record.


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.


Attach the BuyStockRemaining Query
Figure 25 - Attach the BuyStockRemaining query

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


Save Button
Figure 26 - Save button

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


Save Button
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.


Run Button
Figure 28 - Run button

2. Select the Growth Portfolio record.

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


Down Button
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

NAB records
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.


Add an NAB Sell Transaction
Figure 31 - Add a NAB sell transaction

9. Click the Add Allocations button.


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)!

Filtered Buy Lookup
Figure 33 - Filtered buy lookup

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

12. Type 1100 in the Quantity field.


Add an Allocation Record
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 could of 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.


Cancel Button
Figure 35 - Cancel button

14. Close the browser tab and return to Five.