Associating a Person With a To-Do Item
In the To-Do application a person can be associated with a to-do item. The people table and todo_items table have a many-to-many relationship through the people_todo table. Let's look at how easy it is to connect these records using SQL Yoga.
What We Are Going To Do
To associate a task with a person we want to drag the task from the list onto the person's name in the left column. The handlers dealing with drag and drop have already been defined so we just need to add the RevTalk that performs the actual link.
Note: the dimmed image of the to-do item that is being dragged may not appear in the Revolution IDE. It relies on a relatively new Data Grid feature that will be included with Revolution 4.0.
Update the LinkPersonWithToDo Command
Replace the LinkPersonWithToDo command in the card script with the following RevTalk code. After you insert the code we will go through the relevant parts.
Copy & Paste The Following Code
command LinkPersonWithToDo pPersonID, pToDoID
## Create SQL Record objects for person and to-do
put sqlrecord_createObject("people") into thePersonA
put sqlrecord_createObject("todo_items") into theToDoA
## Just set the fields that are used to link records together
sqlrecord_set thePersonA, "id", pPersonID
sqlrecord_set theToDoA, "id", pToDoID
## Let SQL Yoga fill in the people_todo table for you
sqlrecord_link thePersonA, theToDoA
put the result into theError
## If person is already linked to to-do item then
## database will complain and error will be thrown.
## Just catch it and move along...
if theError is empty then
## Refresh list
if theError is not empty then
answer "Error linking person to to-do item:" && theError & "."
Linking Two Related Database Records Together
Open the card script and find the empty LinkPersonWithToDo handler. To link two records together you can use SQL Record objects and the sqlrecord_link command. Here is how it works:
1) Create SQL Record objects for the two tables containing the records that you want to link. In this example we are going to use sqlrecord_createObject and then manually assign the 'id' properties for each object. We could also use sqlrecord_find which would grab the entire record from the database.
2) Fill in the 'id' properties (only necessary if not using sqlrecord_find). The 'id' properties for projects and todo_items are the unique fields used in the relationship. That is the minimum amount of information that SQL Yoga needs to link the records together.
3) Call sqlrecord_link. SQL Yoga inserts the necessary values into the people_todo table.
Note that the call to sqlrecord_link is wrapped in a try/catch block. SQL Yoga throws an error if a SQL Query fails. The error is prefixed with sqlyoga_executesql_err, and includes the error message returned from the database after the comma. The To-Do application database does not allow a person to be linked to the same to-do item twice so the database will return an error if you try to do so. The try/catch block allows you to silently ignore this error and is adequate for the purposes of this sample application.
With New Project selected in the left column drag New Task onto New Person in the left column.
Clicking on New Person in the left column will now show the person associated with the task.
Verify That Database Record Was Created
Look at the records in the people_todo table and you should see a new entry linking the task and the person.