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

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.

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

stack

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

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

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

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 field

1. 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 the 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.