4 - Use Metadata in a Query

Last updated 3/04/2023

This documentation is to demonstrate how you can create a query with metadata and use this query on a field with the display type _Lookup Query to filter the results you require. This same query can also have a menu item created and be used to show a report of the results.

When you ran the Portfolio application you saw that we were able to select a stock that we do not own to sell. 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
• A value field to display in the lookup list

Navigate to Queries

1. Select Visual in the menu.

2. Select Queries in the sub-menu.

Figure 1 - Queries menu item

Add the Current Stock Holding Query

1. Click the Add Item button.

2. Type Current Stock Holding in the Title field.

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

Figure 2 - Add the Current Stock Holding query

4. Click the SQL tab.

Figure 3 - SQL tab

5. Click the Copy button for the syntax below.

Current Stock Holding

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 HAVING clause is used to see if we have more bought than sold for each stock to know whether to display the StockCode in the lookup list.

7. Click the Save button in the SQL Editor app bar.

Figure 4 - Add the Current Stock Holding syntax

8. Click the Fields tab.

Figure 5 - Fields tab

9. Select the StockKey record.

Figure 6 - StockKey record

10. Click in the Show If field and type false.

info

The StockKey field is a key field and holds a GUID so we do not want to show this value in our report.

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

Figure 7 - Edit the StockKey record

12. Select the Holding record.

Figure 8 - Holding record

13. Click the lookup icon in the Display Type field and select _Integer.

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

Figure 9 - Edit the Holding record

15. Select the Investment record.

Figure 10 - Investment record

16. Click the lookup icon in the Display Type field and select _Currency.

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

Figure 11 - Edit the Investment record

18. Click the Parameters tab.

Figure 12 - Parameters tab

19. Click the Add Parameters button.

Figure 13 - Add Parameters button

20. Type PortfolioKey in the Parameter ID field.

21. Type {{five.stack.Portfolio.PortfolioKey}} in the Parameter field.

Figure 14 - Add the PortfolioKey parameter

info

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

five.stack

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

Figure 15 - Save the PortfolioKey parameter

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

Figure 16 - Save the Current Stock Holding query

Working with Parameters

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

Attach the Query to the Stock Field on the Sells Form

Path: Sells form > General page > Stock field

1. Select Visual in the menu.

2. Select Forms in the sub-menu.

Figure 17 - Forms menu item

3. Select the Sells record in the list.

4. Click the Pages tab.

Figure 18 - Pages tab

5. Select the General record.

Figure 19 - General record

6. Click the Fields tab.

Figure 20 - Fields tab

7. Select the Stock record.

Figure 21 - Stock record

8.Click the lookup icon in the Display Type field and select _LookupQuery.

9. Click the Cancel button in the Reference Form field.

10. Click the lookup icon in the Lookup Query field and select CurrentStockHolding.

Figure 22 - Attach the CurrentStockHolding query

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

Figure 23 - Save the Stock field

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

Figure 24 - Save the Sells form

Create a Menu Item for the Current Stock Holding Query

info

The Current Stock Holding query can also have a menu item created so a user can see the results in a report style.

1. Select Visual in the menu.

2. Select Menus in the sub-menu.

Figure 25 - Menus menu item

3. Click the Add Item button.

4. Type Current Stock Holding in the Caption field.

5. Type 2 in the Menu Order field.

6. Click the lookup icon in the Action field and select CurrentStockHolding (Query).

7. Click the lookup icon in the Parent Menu field and select Portfolios (Portfolios).

Optional

You can add an icon for the Current Stock Holding menu item by clicking the Edit button in the Icon field and selecting your image file.

Figure 26 - Add the Current Stock Holding menu item

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

Figure 27 - Save the Current Stock Holding menu item

Edit the Buys Menu Item

info

The Buys and Sells menu items were created by the Form Wizard, these need to be edited to re-order them into the positions we want.

1. Select the Buys record in the list.

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

Figure 28 - Edit the Buys menu item

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

Figure 29 - Save the Buys menu item

Edit the Sells Menu Item

1. Select the Sells record in the list.

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

Figure 30 - Edit the Sells menu item

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

Figure 31 - Save the Sells menu item

Run the Portfolio Application

This documentation is to demonstrate how the Current Stock Holding query works in the Portfolio application.

1. Click the Run button in Five's toolbar.

Figure 32 - Run button

2. Select the Growth Portfolio record.

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

Figure 33 - Down button

Test the Current Stock Holding Query

The first thing we now see in the child menu is the Current Stock Holding report and it shows us the stock we own with our share holding and what we have invested. We can see we own shares in the stocks BOQ, AMP, AEI, and BHP. The report shows us that we own 300 BOQ shares.

Figure 34 - Current Stock Holding Report

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.

Figure 35 - Filtered Stock field

info

We now only have the stocks we own in the Stock lookup list!

4. Select BOQ in the Stock lookup list.

5. Click the calendar icon in the Transaction Date field and select the current date.

6. Type 400 in the Quantity field, press tab.

Problem

The Current Stock Holding report showed us that we only own 300 shares in the BOQ stock, yet we are able to enter 400 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 36 - No quantity validation

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

Figure 37 - Cancel button

8. Close the browser tab and return to Five.