11.1 - Do Before Insert Event

Last updated 20/05/2025

Five has several table events. All table events execute on the backend as they are directly interacting with your database.

In the Book Club application, the Do Before Update event will be used on the Book table which will execute before the updated record is committed into the database. The context contains two objects

old

and

new

which contain the old record currently existing in the database, and the new updated record being updated in the database. The function will be called when updating the Book table.

When the Book table is updated to add a borrowing member, a record will be inserted in the BorrowHistory table with the borrow date and a notification will be sent using Five's

sendNotification()

function. The notification will send a Google Maps link to the member requesting to borrow the book to know the collection point.

When the Book table is updated to remove a borrowing member, the record will be updated in the BorrowHistory table with the returned date.

Navigate to Functions

1. Click Logic in the menu.

2. Click Functions in the sub-menu.

Figure 1 - Functions menu item

Add the SaveBookBeforeUpdate Function

The

SaveBookBeforeUpdate()

function manages book borrowing and will perform two types of updates to the BorrowHistory table. It handles the creation of borrow history records and book returns, plus sends notifications to members about the book availablity. Five's

executeQuery()

function is used to interact with the database.

This operation is calculated by inspecting the

context.new.BorrowMemberKey

value , and also the

context.old.BorrowMemberKey

. If the

context.old.BorrowMemberKey

is empty, we create the BorrowHistory record to perform a borrowing action of the book, otherwise if the

context.new.BorrowMemberKey

is empty, the function then updates the BorrowHistory record for this book as being returned by removing the BorrowMemberKey from the existing BorrowHistory record.

Book Borrow (New Loan)

When a book is borrowed for the first time

context.old.BorrowMemberKey === ''

will be empty, and therefore a borrow entry is created with the following values:

Unique borrow HistoryKey (UUID)
MemberKey of the borrower
BookKey
Current timestamp as the borrow date

The current user's address is retrieved from the iUser table using Five's

currentUserKey()

function and a notification is sent to the borrowing member with a Google Maps link to the collection point.

Book Return

When a book is returned

context.new.BorrowMemberKey === ''

will be empty, and therefore updates the corresponding BorrowHistory record by setting the Date Returned field to the current timestamp and uses the MemberKey and BookKey to identify the correct history record.

1. Click the Add Item button.

2. Type SaveBookBeforeUpdate in the Function ID field.

3. Click in the Code field to open the Code Editor.

Figure 2 - Add SaveBookBeforeUpdate function

4. Copy the code block below and paste it over the template in the Code Editor.

Manages Book Borrowing
function SaveBookBeforeUpdate(five, context, result)  {
    if (context.old.BorrowMemberKey === context.new.BorrowMemberKey){
        return five.success();
    }

    if (context.old.BorrowMemberKey === '') {
        // create new History record
        let userResults = five.executeQuery("INSERT INTO BorrowHistory (BorrowHistoryKey, MemberKey, BookKey, Date) Values(?, ?, ?, ?)", 0, five.uuid(), context.new.BorrowMemberKey, context.new.BookKey, five.now());
        if (userResults.isOk() === false) {
            return five.createError(userResults);
        }

        const currentUserKey = five.currentUserKey();
        userResults = five.executeQuery("SELECT AddressLine1, AddressLine2, Suburb, State, PostCode FROM iUser WHERE iUserKey = ?", 1, currentUserKey);
        if (userResults.isOk() === false) {
            return five.createError(userResults);
        }

        const AddressLine1 = userResults.records[0].AddressLine1;
        const AddressLine2 = userResults.records[0].AddressLine2;
        const Suburb = userResults.records[0].Suburb;
        const State = userResults.records[0].State;
        const PostCode = userResults.records[0].PostCode;

        let address = AddressLine1;
        if (AddressLine2) {
            address += "," + AddressLine2;
        }

        address += `,${Suburb},${PostCode},${State}`;

        const message = `Book is ready at collection point : https://maps.google.com?q=${address}`;
        five.sendNotification(context.new.BorrowMemberKey, message);

    } else if (context.new.BorrowMemberKey === '') {
        // update history record that the book has been returned
        const userResults = five.executeQuery("UPDATE BorrowHistory SET DateReturned = ? WHERE MemberKey = ? AND BookKey = ?", 0, five.now(), context.old.BorrowMemberKey, context.new.BookKey);
        if (userResults.isOk() === false) {
            return five.createError(userResults);
        }

    } else {
        return five.createError(result, 'Book must be returned before being borrowed again.')
    }
    
    return five.success(result);
}