1.1 - Add the Do Before Update Event

Last updated 13/06/2023

In Five, you can attach functions to a table to execute in the following situations:

• Do Before Insert
• Do After Insert
• Do Before Update
• Do After Update
• Do Before Delete
• Do After Delete

All of these events execute on the backend as they are directly interacting with your database.

tip

In Five, all Do events execute on the backend and all On events execute in the frontend!

We created a table called BorrowHistory when we added the tables for the Book Club application. We are going to create the SaveBookBeforeUpdate function and attach it to the Do Before Update event on the Book table. 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 link to Google Maps via a https request.

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

Navigate to the Code Editor

1. Select Logic in the menu.

2. Select Code Editor in the sub-menu.

Figure 1 - Code Editor menu item

Add the SaveBookBeforeUpdate Function

1. Click the Add New Code button.

Figure 2 - Add New Code button

2. Type SaveBookBeforeUpdate in the Function ID field.

3. Click the lookup in the Language field and select JavaScript.

4. Click the OKAY button.

Figure 3 - Add the SaveBookBeforeUpdate function

5. Highlight and delete the template in the Code Editor.

Figure 4 - Clean the Code Editor

6. Click the Copy button on the code block below.

SaveBookBeforeUpdate()

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);
}

7. Paste the code into the Code Editor.

8. Click the Save Current Tab button.

Figure 5 - Save the SaveBookBeforeUpdate function

Navigate to Tables

1. Click Data in the menu.

2. Click Tables in the sub-menu.

Figure 6 - Tables menu item

Attach the SaveBookBeforeUpdate Function

1. Select the Book record in the list.

2. Click the Events tab.

Figure 7 - Events tab

3. Either click the Edit button in the form app bar, or click directly in the Do Before Update field.

4. Click the lookup icon in the Do Before Update field and select SaveBookBeforeUpdate.

Figure 8 - Attach the SaveBookBeforeUpdate function

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

Figure 9 - Save the Do Before Update event