Create Lookup Queries

Last updated 2/02/2022

This chapter will explain how to create lookup queries to filter data using Five's property:

five.stack

Using the stack in Five gives you access to any tables stacked above your current location by specifing the Table ID and Primary Key.

Create the Billing Address Lookup Query

info

The Billing Address query when attached to the Orders form will filter the data from the Address table to only show the addresses with a type of Billing. We can use the stack to know:

which addresses belong to each restaurant, and

for dispatch to see the billing addresses.

1. Select Queries in the menu.

2. Click the Add Item button in the list bar.

3. Type Billing Address in the Title Field.

4. Click in the Query field to open Five's Table and Fields Builder.

Figure 1 - Add the Billing Address query

5. Click the SQL tab.

Figure 2 - SQL tab

6. Click the Copy button on the syntax below.

Billing Address

SELECT
    AddressKey,
    CONCAT(AddressLine1, " ", AddressLine2) AS Address
FROM
    Address
WHERE Type = ? AND (RestaurantKey = ? OR RestaurantKey in (Select Orders.RestaurantKey from Orders WHERE OrdersKey = ?))

7. Paste into Five's SQL Editor.

8. Click the Save button in the SQL Editor.

Figure 3 - Billing Address syntax

9. Click the Fields tab.

Figure 4 - Fields tab

10. Click the Parameters tab.

Figure 5 - Parameters tab

11. Click the Add Parameters button.

Figure 6 - Add Parameters button

12. Type BillingAddressType in the Parameter ID field.

13. Click the lookup icon in the Parameter Type field and select Constant.

14. Type B in the Parameter field.

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

Figure 7 - Add the BillingAddressType parameter

16. Click the Add Parameters button.

Figure 8 - Add Parameters button

17. Type RestaurantKey in the Parameter ID field.

18. Type {{five.stack.Restaurant.RestaurantKey}} in the Parameter field.

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

Figure 9 - Add the RestaurantKey parameter

20. Click the Add Parameters button.

Figure 10 - Add Parameters button

21. Type OrdersKey in the Parameter ID field.

22. Type {{five.stack.Orders.OrdersKey}} in the Parameter field.

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

Figure 11 - Add the OrdersKey parameter

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

Figure 12 - Save the Billing Address query

Create the Shipping Address Lookup Query

info

The Shipping Address query when attached to the Orders form will filter the data from the Address table to only show the addresses with a type of Shipping. We can use the stack to know:

which addresses belong to each restaurant, and

for dispatch to see the shipping addresses.

1. Click the Add Item button in the list bar.

2. Type Shipping Address in the Title Field.

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

Figure 13 - Add the Shipping Address query

4. Click the SQL tab.

Figure 14 - SQL tab

5. Click the Copy button on the syntax below.

Shipping Address

SELECT
    AddressKey,
    CONCAT(AddressLine1, " ", AddressLine2) AS Address
FROM
    Address

WHERE Type = ? AND (RestaurantKey = ? OR RestaurantKey in (Select Orders.RestaurantKey from Orders WHERE OrdersKey = ?))

6. Paste into Five's SQL Editor.

7. Click the Save button in the SQL Editor.

Figure 15 - Shipping Address syntax

8. Click the Fields tab.

Figure 16 - Fields tab

9. Click the Parameters tab.

Figure 17 - Parameters tab

10. Click the Add Parameters button.

Figure 18 - Add Parameters button

11. Type ShippingAddressType in the Parameter ID field.

12. Click the lookup icon in the Parameter Type field and select Constant.

13. Type S in the Parameter field.

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

Figure 19 - Add the ShippingAddressType parameter

15. Click the Add Parameters button.

Figure 20 - Add Parameters button

16. Type RestaurantKey in the Parameter ID field.

17. Type {{five.stack.Restaurant.RestaurantKey}} in the Parameter field.

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

Figure 21 - Add the RestaurantKey parameter

19. Click the Add Parameters button.

Figure 22 - Add Parameters button

20. Type OrdersKey in the Parameter ID field.

21. Type {{five.stack.Orders.OrdersKey}} in the Parameter field.

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

Figure 23 - Add the OrdersKey parameter

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

Figure 24 - Save the Shipping Address query