Skip to main content

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
1. Click Data in the menu.

2. Click Queries in the sub-menu.


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


Add the Current Stock Holding Query
Figure 2 - Add the CurrentStockHolding query

4. Click the SQL tab.


SQL Tab
Figure 3 - SQL tab

5. Click the Copy button for the syntax below.

MySQL
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
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
SUM(OriginalCost * (Bought - Sold) / Bought)
will let us know how much we have invested in each stock.


The
HAVING
clause is used to retrieve data for a selected Portfolio record and ensures we have stock.

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 Holding record.


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.


Edit the Holding Field
Figure 7 - Edit the Holding field

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


Save Button
Figure 8 - Save button

15. Select the Investment record.


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.


Edit the Investment Field
Figure 10 - Edit the Investment 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.


info
Using Five's API we can get the data from the Portfolio table stacked above us using the property on the
Five
object.


The
stack
property works at the table-level and you will need to append the name of the table and the primary key for the table.


{{five.stack.<tableID>.<primaryKey>}}

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 Save button in the form app bar above the list.


Save Button
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
stack
property on the
Five
object. 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}}
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.


Forms Menu Item
Figure 17 - Forms menu item

Attach the CurrentStockHolding Query

Path: Sells form > General page > Stock field

1. Select the Sells record in the list.

2. Click the Pages tab.


Pages Tab
Figure 18 - Pages tab

3. Select the General record.


General Record
Figure 19 - General record

4. Click the Fields tab.


Fields Tab
Figure 20 - Fields tab

5. Select the Stock record.


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.


Attach the Current Stock Holding Query
Figure 22 - Attach the CurrentStockHolding query

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


Save Button
Figure 23 - Save button

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


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


Data Views Menu Item
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 the Title field.

3. Click the lookup icon in the Data Source field and select CurrentStockHolding (Query).


Add the Current Stock Holding Data View
Figure 26 - Add the Current Stock Holding data view

4. Click the Data Fields tab.


Data Fields Tab
Figure 27 - Data Fields tab

5. Select the StockKey record.


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.

Edit the StockKey Field
Figure 29 - Edit the StocKKey field

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


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


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.


Edit the StockCode Record
Figure 32 - Edit the StockCode record

12. Click the Save in the form app bar.


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


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


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


Add the Current Stock Holding Menu Item
Figure 36 - Add the Current Stock Holding menu item

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


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


Edit the Buys Menu Item
Figure 38 - Edit the Buys menu item

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


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


Edit the Sells Menu Item
Figure 40 - Edit the Sells menu item

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


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


Run Button
Figure 42 - Run button

2. Select the Growth Portfolio record.

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


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

Current Stock Holding Data View
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!

Filtered Stock Lookup
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.

No Quantity Validation
Figure 46 - No quantity validation

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


Cancel Button
Figure 47 - Cancel button

7. Close the browser tab and return to Five.