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

try

sqlrecord_link thePersonA, theToDoA

put the result into theError

catch e

## If person is already linked to to-do item then

## database will complain and error will be thrown.

## Just catch it and move along...

end try

if theError is empty then

## Refresh list

RefreshToDoList

end if

if theError is not empty then

answer "Error linking person to to-do item:" && theError & "."

end if

end LinkPersonWithToDo

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.

Test

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.

0 Comments

Add your comment

E-Mail me when someone replies to this comment