4 - Use Metadata in a Query
Last updated 14/12/2023
This documentation is to demonstrate how you can create a query with metadata and attach the query to a field with Five's _LookupQuery display type. This will filter the results and only list what you want.A query in Five is a data source and can be used multiple times. We will create a data view using the same query to see the results when selecting the menu item.
When we ran the Portfolio application, you saw that we were able to select a stock that we do not own to sell as all stocks in the system are being listed in the lookup. We will write a query that contains metadata and attach it to the Stock field on the Sells form to only show the stocks we own.
In its most minimal form a _LookupQuery display type must have:
- A primary key field to have uniqueness; and
- A value field to display in the lookup list
Navigate to Queries
1. Click Data in the menu.2. Click Queries in the sub-menu.
Figure 1 - Queries menu item
Add the CurrentStockHolding Query
1. Click the Add Item button.2. Type CurrentStockHolding in the Data Source ID field.
3. Click in the Query field to open Five's Query Builder.
Figure 2 - Add the CurrentStockHolding query
4. Click the SQL tab.
Figure 3 - SQL tab
5. Click the Copy button for the syntax below.
CurrentStockHolding
SELECT
StockKey,
StockCode,
Name,
SUM(Bought - Sold) AS Holding,
SUM(OriginalCost * (Bought - Sold) / Bought) AS Investment
FROM (
SELECT
Stock.StockKey AS StockKey,
Stock.StockCode AS StockCode,
Stock.Name AS Name,
Buy.Quantity AS Bought,
Buy.PortfolioKey AS PortfolioKey,
IFNULL((SELECT SUM(Allocation.Quantity) FROM Allocation WHERE Allocation.BuyKey = Buy.BuyKey),0) AS Sold,
Buy.Total AS OriginalCost
FROM
Buy INNER JOIN Stock ON Buy.StockKey = Stock.StockKey
HAVING
(PortfolioKey = ?) AND
(Bought > Sold)
) AS Summary
GROUP BY
StockKey,
StockCode,
Name
6. Paste the syntax in the SQL Editor.
info
If you look at the
The
The
SELECT
statement you can see we are using the StockKey and the StockCode fields for the StockCode to be displayed in the Stock field's lookup list.The
SUM(Bought - Sold)
will let us know how much of each stock we are currently holding.The
HAVING
clause is used to see if we have more bought than sold for each stock to know whether to display the stock code in the lookup list.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 Holding record.
Figure 6 - Holding 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 Integer.
12. Type 4 in the Size field.
13. Click the lookup icon in the Default Display Type field and select _Integer.
Figure 7 - Edit the Holding field
14. Click the Save button in the form app bar.
Figure 8 - Save button
15. Select the Investment record.
Figure 9 - Investment 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 Float.
18. Type 8 in the Size field
19. Click the lookup icon in the Default Display Type field and select _Currency.
Figure 10 - Edit the Investment 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.info
Using Five's API we can get the data from the Portfolio table stacked above us using the property on the
Five
object.five.stack
works at the table-level and you will need to append the name of the table and the primary key for the table.Figure 14 - Add the PortfolioKey parameter
25. Click the Save button in the form app bar.
Figure 15 - Save button
26. Click the Save button in the form app bar above the list.
Figure 16 - Save button
Working with Parameters
The?
placeholder needs to be provided in your syntax, and the parameters need to be in order of the placeholders in your syntax.If you want to find transactions for just one Portfolio record, you can by setting a parameter type of Constant, this will only pull the data for that particular portfolio.
Constant Example
WHERE PortfolioKey = ‘6b087bf8-110d-4e9f-aff0-59d99858c437’
To use the query repeatedly to get data from different Portfolio records, you can parameterize the query with an Expression and use the
five.stack
property. This will make it that when you are under a selected record you will have the data for that specific record as it is not hard-coded.Expression Example
WHERE PortfolioKey = {{five.stack.Portfolio.PortfolioKey}}
Navigate to Forms
info
The CurrentStockHolding query needs to be attached to the Stock field on the Sells form using Five's display type _LookupQuery.
1. Click Visual in the menu.
2. Click Forms in the sub-menu.
Figure 17 - Forms menu item
Attach the CurrentStockHolding Query
Path: Sells form > General page > Stock field1. Select the Sells record in the list.
2. Click the Pages tab.
Figure 18 - Pages tab
3. Select the General record.
Figure 19 - General record
4. Click the Fields tab.
Figure 20 - Fields tab
5. Select the Stock record.
Figure 21 - Stock 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 CurrentStockHolding.
Figure 22 - Attach the CurrentStockHolding query
10. Click the Save button in the form app bar.
Figure 23 - Save button
11. Click the Save button in the form app bar above the list.
Figure 24 - Save button
Navigate to Data Views
info
The Current Stock Holding data view needs to be created using the CurrentStockHolding query as the data source so we can view the results in the Portfolio application.
1. Click Visual in the menu.
2. Click Data Views in the sub-menu.
Figure 25 - Data Views menu item
Add the Current Stock Holding Data View
1. Click the Add Item button.2. Type Current Stock Holding in Title field.
3. Click the lookup icon in the Data Source field and select CurrentStockHolding (Query).
Figure 26 - Add the Current Stock Holding data view
4. Click the Data Fields tab.
Figure 27 - Data Fields tab
5. Select the StockKey record.
Figure 28 - StockKey record
6. Either click the Edit button in the form app bar, or click directly in the Show If field.
7. Type
false
in the Show If field.info
The StockKey field is a key field in the database that holds GUIDs (globally unique identifers). By typing
false
in the Show If field, the field will be hidden on the data view.Figure 29 - Edit the StocKKey field
8. Click the Save button in the form app bar.
Figure 30 - Save button
info
When you create a data view, Five sets some default field width values. We are going to change the widths of these fields to suit our returned results better.
tip
You may need to play around with the widths to suit the size of your screen!
9. Select the StockCode record.
Figure 31 - StockCode record
10. Either click the Edit button in the form app bar, or click directly in the Width field.
11. Click the Cancel button in the Width field and type 30.
Figure 32 - Edit the StockCode record
12. Click the Save in the form app bar.
Figure 33 - Save button
13. Repeat the same steps for the Name, Holding, and Investment fields.
14. On completion, click the Save button in the form app bar above the list.
Figure 34 - Save button
Navigate to Menus
info
The Current Stock Holding data view needs a menu item created so the results can be viewed in the Portfolio application.
1. Click Visual in the menu.
2. Click Menus in the sub-menu.
Figure 35 - Menus menu item
Add a Menu Item for the Current Stock Holding Data View
1. Click the Add Item button.2. Type Current Stock Holding in the Caption field.
3. Type 2 in the Menu Order field.
4. Click the lookup icon in the Action field and select CurrentStockHolding (Data View).
5. Click the lookup icon in the Parent Menu field and select Portfolios (Portfolios).
6. Optional: Click the lookup icon in the Icon field and open an image file.
Figure 36 - Add the Current Stock Holding menu item
7. Click the Save button in the form app bar.
Figure 37 - Save button
Edit Menu Records
info
The Buys and Sells menu items were created by the Form Wizard, these need to be reordered.
Edit the Buys Menu
1. Select the Buys record in the list.2. Either click the Edit button in the form app bar, or click directly in the Menu Order field.
3. Click the Cancel button in the Menu Order field and type 3.
4. Optional: Click the lookup icon in the Icon field and open an image file.
Figure 38 - Edit the Buys menu item
5. Click the Save button in the form app bar.
Figure 39 - Save button
Edit the Sells Menu
1. Select the Sells record in the list.2. Either click the Edit button in the form app bar, or click directly in the Menu Order field.
3. Click the Cancel button in the Menu Order field and type 4.
4. Optional: Click the lookup icon in the Icon field and open an image file.
Figure 40 - Edit the Sells menu item
5. Click the Save button in the form app bar.
Figure 41 - Save button
Run the Portfolio Application
tip
This is a good time to run the Portfolio application to see how the CurrentStockHolding query works on the Sells form and in the data view!
1. Click the Run button in Five's toolbar.
Figure 42 - Run button
2. Select the Growth Portfolio record.
3. Click the Down button in the form app bar.
Figure 43 - Down button
Test the Current Stock Holding Data View
The first thing we now see in the child menu is the Current Stock Holding data view and it shows us the stock we own with our share holding, and what we have invested. These are the results from the CurrentStockHolding query attached to the data view.note
The data view is showing us that we currently own 5500 SUN (Suncorp) shares.
Figure 44 - Current Stock Holding data view
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.
info
We now only have the stocks we own in the Stock lookup list!
Figure 45 - Filtered Stock lookup
4. Select SUN in the Stock lookup list.
5. Type 6000 in the Quantity field, press tab.
Problem
The Current Stock Holding view showed us that we only own 5500 shares in the BOQ stock, yet we are able to enter 6000 in the Quantity field. In Five, we can fix this by creating a validation and attaching it to the Quantity field on the Sells form.
Figure 46 - No quantity validation
6. Click the Cancel button in the form app bar.
Figure 47 - Cancel button
7. Close the browser tab and return to Five.