3.4 - Find Book Query

Last updated 12/06/2023

The Find Book query is going to return the title, the classification, the name of the owner of the book, their email, and their postcode. When the SEARCH button is clicked, that we created in the Find Book process, the title and the ISBN values will be set as variables globally so the query can be filtered via the parameters on the query. The BorrowMemberKey is also returned, this will be explained in further documentation as to why we need this field.

Navigate to Queries

1. Select Visual in the menu.

2. Select Queries in the sub-menu.

Figure 1 - Queries menu item

Add the Find Book Query

1. Click the Add Item button.

2. Type Find Book in the Title field.

3. Type Query after FindBook in the Action ID field.

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

Figure 2 - Add the Find Book query

5. Click the SQL tab.

Figure 3 - SQL tab

6. Click the Copy button on the syntax below.

FindBookQuery

SELECT
    iUser.iUserKey AS UserKey,
    iUser.FullName AS Name,
    iUser.Email AS Email,
    iUser.PostCode AS Postcode,
    Classification.Name AS Classification,
    Book.Title AS Title,
    Book.BorrowMemberKey AS Borrower
FROM
    iUser
INNER JOIN Book ON Book.MemberKey = iUser.iUserKey
INNER JOIN Classification ON Classification.ClassificationKey = Book.ClassificationKey
WHERE ((? = '' AND Book.Title LIKE ?) OR (Book.ISBN = ?)) AND iUser.iUserKey <> ?

7. Paste the syntax in the SQL Editor.

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

Figure 4 - Save the Find Book syntax

9. Type false in the Show Title If field.

info

The If fields throughout Five take a JavaScript statement and you can apply a condition in these fields. By typing false in the Show Title If field here, the title for the query will not be displayed on our dashboard.

10. Click the Live switch

info

The Live switch is turned on because when we set the variables in the SetSearchISBN function we call the refreshTable() in that function which causes this query to re-evaluate.

Figure 5 - Find Book fields

11. Click the Fields tab.

Figure 6 - Fields tab

info

All the fields that are in the Find Book query will be listed here on the Fields page, these fields can be edited if required to change things like the caption and the display type etc.

12. Select the UserKey record.

Figure 7 - UserKey field record

13. Either click the Edit button in the form app bar or click directly in the Show If field.

14. Click the Cancel button in the Show If field and type false.

Figure 8 - Edit the UserKey field

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

Figure 9 - Save the UserKey field

16. Select the Borrower record.

Figure 10 - Borrower field record

17. Either click the Edit button in the form app bar or click directly in the Show If field.

18. Click the Cancel button in the Show If field and type false.

Figure 11 - Edit the Borrower field

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

Figure 12 - Save the Borrower field

20. Click the Parameters tab.

Figure 13 - Parameters tab

info

All the fields that are in the WHERE clause in the Find Book query need to be added on the Parameters page. In the query they have a placeholder of ?, we do this for security reasons to prevent SQL injection.

21. Click the Add Parameters button.

Figure 14 - Add Parameters button

22. Type ISBN in the Parameter ID field.

23. Type {{five.variable.ISBN}} in the Parameter field.

info

The ISBN value on the five variable object which was set in the SetSearchISBN function will now be used as a parameter in the Find Book query.

Figure 15 - Add the ISBN parameter

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

Figure 16 - Save the ISBN parameter

25. Click the Add Parameters button.

Figure 17 - Add Parameters button

26. Type Title in the Parameter ID field.

27.Type {{five.variable.Title}} in the Parameter field.

Figure 18 - Add the Title parameter

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

Figure 19 - Save the Title parameter

29. Click the Add Parameters button.

Figure 20 - Add Parameters button

30. Type ISBNSearch in the Parameter ID field.

31. Type {{five.variable.ISBN}} in the Parameter field.

Figure 21 - Add the ISBNSerach parameter

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

Figure 22 - Save the ISBNSearch parameter

33. Click the Add Parameters button.

Figure 23 - Add Parameters button

34. Type UserKey in the Parameter ID field.

35. Type {{five.variable.UserKey}} in the Parameter field.

Figure 24 - Add the UserKey parameter

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

Figure 25 - Save the UserKey parameter

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

Figure 26 - Save the Find Book query