How to automate INSERT INTO

How do I?
1
  • can it be done in queries?
  • how would the variables to be inserted in the table passed to the INSERT?
  • is there api to do it in javascript?
  • what is the actionkey for doing it in javascript?
2

Hey Toshi,

could you provide a bit more detail on what you want to accomplish, please?

I’ll give you an example of something I’ve built. Let me know if this is what you’re looking for.

I have this form inside my end-user application:

image
image3000×2000 323 KB

When someone clicks submit in the top right corner of this form, the data gets inserted into multiple tables.

To accomplish this,

  1. Go to Tasks > Processes > Yellow Plus Icon > Write a Name

  2. Next, click on Fields and add all required fields that you want to show to your users. Here’s what this looks like for me (note I’m using a few show-if conditions, so not every field that you see here is shown on the screen above):

    image
    image3000×2000 365 KB

  3. Now click on General, and on Code - Click to Add

  4. Here’s the code I’m using:

function BookingRequest(five, context, result) {
        // Database Connection Check
        const db = five.getDatabaseConnectionByID('PropertyFinderdb');
        if (db.isOk() === false) {
                return five.createError(db, 'Failed to get database connection');
        }
        // Transaction Check
        const tx = five.startTransaction(db);
        if (tx.isOk() === false) {
                return five.createError(tx, 'Failed to start transaction');
        }
        const selectProperty = `SELECT ID, Address
                           From Property WHERE PropertyKey = ?`;
        const selectResult = five.executeQuery(tx, selectProperty, 1, context.PropertyKey);
        if (selectResult.isOk() === false) {
                return five.createError(selectResult, 'Failed query database');
        }
        // SQL Insertion 
        const sql = `INSERT INTO Customer (CustomerKey, CustomerName, Email, PropertyKey, BookingTime)
                VALUES (?, ?, ?, ?, ?)`;

        const CustomerKey = five.uuid();
        const CustomerName = context.CustomerName;
        const PropertyKey = context.PropertyKey;
        const Email = context.Email;
        const BookingTime = context.BookingTime;

        const execResult = five.executeQuery(tx, sql, 0, CustomerKey, CustomerName, Email, PropertyKey, BookingTime);
        if (execResult.isOk() === false) {
                five.rollback(tx);
                return five.createError(execResult, 'Failed to insert information');
        }
        const commitResult = five.commit(tx);
        if (commitResult.isOk() === false) {
                return five.createError(commitResult, 'Failed to commit');
        }
        const notification = 'New Booking Request Received.'

        //////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        // The key '10000000-0000-0000-0000-000000000001' is the admin user account
        //////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        five.sendNotification('10000000-0000-0000-0000-000000000001', notification);

        return five.success(result);
}
1 Like