4 - Use Metadata in a Query

Last updated 15/12/2025

This documentation will explain how you can create a query with metadata and attach the query to a field with Five's _LookupQuery display type.

A query in Five is a data source and can be used multiple times. The CurrentStockHolding query will be used on the Stock field and on a data view.

When we deployed the Portfolio application, we saw that we were able to select a stock that we do not own to sell as all stocks in the system are listed in the Stock lookup. The CurrentStockHolding query will have metadata that we can use to only list the stocks we own in the Stock field on the Sells form.

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

The CurrentStockHolding query's

SELECT

statement uses the StockKey and the StockCode fields for the stock codes 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.

Add the CurrentStockHolding 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 CurrentStockHolding in the Data Source ID field.

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

Figure 2 - Add 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.

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

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

Figure 8 - Save button

12. Select the Investment record.

Figure 9 - Investment record

13. Edit the following fields:

• Select Float in the Data Type field
• Type 8 in the Size field
• Select _Float.2 in the Default Display Type field

Figure 10 - Edit Investment record

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.

info

Using Five's API we can get the data from the Portfolio table stacked above us using the

stack

property on the

Five

object.

The

stack

property works at the table-level and you will need to append the data source ID and the primary key for the table.

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

Figure 14 - Add PortfolioKey parameter

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

Figure 15 - Save button

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

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}}

Attach the CurrentStockHolding Query

The CurrentStockHolding query needs to be attached to the Stock field on the Sells form using Five's display type _LookupQuery.

Path: Sells form > General page > Stock field

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

Figure 17 - Forms menu item

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

Figure 22 - Edit Stock record

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

Figure 23 - Save button

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

Figure 24 - Save button

Add the Current Stock Holding Data View

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 followed by Data Views in the sub-menu.

Figure 25 - Data Views menu item

2. Click the Add Item button and type Current Stock Holding in the Title field.

3. Select CurrentStockHolding (Query) in the Data Source field.

Figure 26 - Add 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. Edit the Show If field and type

false

.

info

The StockKey field is a key field in the database that holds GUIDs (globally unique identifiers). By typing

false

in the Show If field, the field will be hidden on the data view.

Figure 29 - Edit StocKKey record

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

8. Select the StockCode record.

Figure 31 - StockCode record

9. Edit the following fields:

• Add a space between Stock and Code
• Click the Cancel button in the Width field and type 30

Figure 32 - Edit StockCode record

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

Figure 33 - Save button

11. Repeat the same steps in the Width field for the Name, Holding, and Investment records.

12. On completion, click the Save button in the form app bar above the list.

Figure 34 - Save button

Add the Current Stock Holding Menu

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

Figure 35 - Menus menu item

2. Click the Add Item button and type Current Stock Holding in the Caption field.

3. Type 2 in the Menu Order field, select CurrentStockHolding (Data View) in the Action field, and select Portfolios (Portfolios) in the Parent Menu field.

4. Optional: Click the Edit button in the Icon field and open an image file.

Figure 36 - Add Current Stock Holding menu

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

Figure 37 - Save button

Edit Menu Records

The Buys and Sells menus were created by the Form Wizard, these need to be reordered.

Edit the Buys Menu

1. Select the Buys record in the list and click the Edit button in the form app bar.

2. Click the Cancel button in the Menu Order field and type 3.

3. Optional: Click the Edit button in the Icon field and open an image file.

Figure 38 - Edit Buys menu

4. 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 and click the Edit button in the form app bar.

2. Click the Cancel button in the Menu Order field and type 4.

3. Optional: Click the Edit button in the Icon field and open an image file.

Figure 40 - Edit Sells menu

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

Figure 41 - Save button

tip

This is a good time to deploy/run the Portfolio application to see how the CurrentStockHolding query works in the data view and on the Sells form!

Test the Current Stock Holding Data View

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

Figure 42 - Down button

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 Current Stock Holding data view is showing us that we currently own 15200 AAPL (Apple Inc) shares.

Figure 43 - Current Stock Holding data view

Test the Sells Form

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

2. Click the lookup icon in the Stock field.

info

Only the stocks we own are in the Stock lookup list!

Figure 44 - Stock list

3. Select AAPL in the Stock field.

4. Type 16000 in the Quantity field, press tab.

Problem

The Current Stock Holding view showed us that we own 15200 shares in the AAPL stock, yet we are able to enter 16000 in the Quantity field. In Five, we can make a validation and attach it to the Quantity field to prevent trying to sell more than we own.

Figure 45 - Quantity field

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

Figure 46 - Cancel button

6. Close the browser tab and return to Five.