Search for To-Dos Using Scopes
Now that we have defined Scope objects we are going to revisit the uiPopulateToDos command in the card script. We will use Scope objects to rework the existing functionality as well as add support for the search field and the 'Hide Completed Items" checkbox in the UI.
Update the uiPopulateToDos Command
Replace the existing uiPopulateToDos handler in the card script with the following RevTalk code.
Copy & Paste The Following Code
## Create Query object
put sqlquery_createObject("todo_items") into theQueryA
## Filter by project or person?
switch the uSelectedType of group "ProjectsPeople"
sqlquery_addScope theQueryA, "of project", \
the uSelectedProjectID of group "ProjectsPeople"
sqlquery_set theQueryA, "order by", "todo_items.sequence"
sqlquery_addScope theQueryA, "of person", \
the uSelectedPersonID of group "ProjectsPeople"
## User supplied search string?
if the text of field "Search" is not empty then
sqlquery_addScope theQueryA, "name contains", \
sqlyoga_splitUserSearchString(the text of field "Search")
## Filter out completed?
if the hilite of button "HideCompleted" then
sqlquery_addScope theQueryA, "not completed"
## Query database
sqlquery_retrieveAsRecords theQueryA, theRecordsA
put the result into theError
if theError is empty then
set the dgData of group "ToDo" to theRecordsA
if theError is not empty then
answer "Error populating to-do items:" && theError & "."
uiPopulateToDos Reworked To Use Scopes
Here is the meat of the uiPopulateToDos handler reworked to use the Scopes we just created. We add Scopes in order to provide the search filters that have been specified by the UI.
1) Are the to-do items linked to a project or person?
2) Is the user searching for a particular string in a to-do name?
3) Should completed to-do items be displayed?
When you use sqlquery_addScope to add a Scope you pass in the name of the Scope object and then any values that will replace the binding variable in the Scope objects 'conditions' string.
Tip: If you want to see the SQL query that the SQL Query object you just created will generate you can check the 'query' property. Try adding the following line right before sqlquery_retrieveAsRecords and you will see the query appear in the Message Box whenever the to-do list is populated.
put sqlquery_get(theQueryA, "query")
Test the UI
After updating the uiPopulateToDos handler you can test the UI. Make sure you have at least one project and one person create and that you have linked a to-do item to the person. Select a project or person.
Check the 'Hide Completed Items' checkbox.
Enter a search term for a string that appears in a to-do item name and press the return key.
And then enter a search string that doesn't appear in a to-do item name and press the return key.
You can even enter search strings that use AND or OR operators. sqlyoga_splitUserSearchString will break the string up so that the records with the string 'broken' or the string 'busted' are returned.