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 theFive
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 field1. 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.