Ragic Logo
<< HTTP API integration Table of Contents

Javascript workflow engine

All forms can trigger Ragic's server-side Javascript workflow engine to execute complex business logic, such as calculating costs and posting inventory balance. Basically, any complex business logic that you would like to execute but cannot be covered by Ragic's existing functions can be covered by server-side scripting.

What does Javascript Workflow do?

Ragic's spreadsheet design interface can handle most of your data management work, such as creating, editing, and querying records without much problem. On the other hand, manual data maintenance can be a bit time consuming and routine after a while. This is the time when Ragic users will start thinking of ways to automate these processes.

Inside Ragic, there is a pretty powerful scripting engine where you can write Javascript that runs on the server-side, to retrieve data you have on your spreadsheet, make modifications, or even create many records with one click. Typical usage includes updating inventory, creating a new record based on another (creating a sales order from a quote, creating contact from a sales lead), or doing record validation based on database data.

Types of Javascript Workflow

There are several ways to run your Javascript workflow:

Action Button

This is the most common and cleanest way to run Javascript workflow, and generally our first recommendataion. You can write your script in the installed sheet scope of your sheet, and configure an action button to execute the script when the user clicks on the button that will be displayed in the "Actions" panel in the lower right side.

To add an installed sheet scope script, just right click on a sheet, and choose Javascript Workflow:

And choose installed sheet scope from the top dropdown:

You can then go to the form page of your sheet design, and add an Action Button of the type JS Workflow, and refer to the Javascript function that you have written.

Note that you can pass the record id of the current record by using {id} in the argument for the function call like:

setStatus({id})

Of course, we will talk more about how to write these functions in the following sections.

Post-workflow

Post-workflows are executed immediately after a record is saved. With post-workflow, it's very convenient to automate changes that you would like to make on the record that you just saved that cannot be done with formulas. Or you can make modifications to records on other related sheets, like updating inventory balance.

To add a post-workflow, just right click on a sheet, and choose Javascript Workflow:

And choose Post-workflow from the top dropdown.

Pre-workflow

Pre-workflows are executed before a record is saved, so it can be used as a way of validation to check the data entered against data in the database. Generally most validation can be done with our front-end regular expression checks, or the unique checkbox for free text fields. But for more complex backend checks, sometimes pre-workflow will be needed.

To add a pre-workflow, just right click on a sheet, and choose Javascript Workflow:

And choose Pre-workflow from the top dropdown.

Daily Workflow

Daily workflow runs on a daily basis. It's useful for doing modifications that needs to be refreshed every day. Like updating the results of the formulas based on the current date.

To add a daily workflow, just right click on a tab, and choose Global Javascript Workflow:

And choose Daily Workflow at the first top dropdown.

By default, daily workflow runs at 19:00 UTC. You can change the execution time and time zone in Company Setting:

1. Go to Account Setup

2. Click Company Setting

3. Select Company Local Time Zone and Daily Workflow Execution Time and save.

If your Company Setting does not have Company Local Time Zone and Daily Workflow Execution Time, please contact us to update it for you.

Global Workflow

Global workflow is a place where you can write Javascript workflow modules that other workflow functions can reference. It will not be exeucted by itself, but can be referenced in any types of the workflow listed above. It's a great place to put scripts that you might need to duplicate across multiple sheets otherwise.

To add a global workflow, just right click on a tab, and choose Global Javascript Workflow:

Get Record

There are two different ways to get the current record:

Action Button

If you added the action button in form page, you can pass a parameter {id} which is the id of record where you click action button to functions in workflow. You can see the example at Types of Javascript Workflow -> Action Button.

Pre-workflow and Post-workflow

You can get record id by:

var recordId = param.getNewNodeId(key_field_id_of_form);

param is a predefined variable, you can use it in pre-workflow and post-workflow.


After you get the record id, this is how you get the record (entry):

var query = db.getAPIQuery(path_of_form);

var entry = query.getAPIEntry(id);

There is more introduction about how to update record in following section.

Record Update

Link to example

Let's start with a simple example which retrieves the current record as an object, update its value with a button, and then saves it back to the database. Here is what the demo form looks like:

We would like to design some buttons that will change the value of our status field with the click of a button executing simple server-side Javascript workflow. Here's the code behind the button:

/**

* AP_Name:wfdemo

* Key Field: 1000013

* Name ID

* - - - - - - - - - - - --------

* No. : 1000011

* Status : 1000012

*/

function setStatus(recordId, status) {

var STATUS_FIELD = 1000012; //field id of the status field

var query = db.getAPIQuery("/workflow-demo/2"); //get the query object for a sheet with path to sheet

var entry = query.getAPIEntry(recordId); //get the record object for the current record

//set the status value for the current record to the object

if (status) {

entry.setFieldValue(STATUS_FIELD, status);

}

else {//for switching

var newStatus = entry.getFieldValue(STATUS_FIELD) == 'On' ? 'Off' : 'On'; //get the current value of a field

entry.setFieldValue(STATUS_FIELD, newStatus);

}

//save the record back to the database

entry.save();

}

When writing Javascript workflow, the variable db is predefined. You can reference to it anywhere. Generally we call the method getAPIQuery(pathName) to retrieve the query object for a sheet.

Then you can retrieve a record with its record id on the sheet object with getAPIEntry(recordId), and call setFieldValue(fieldId,value) to set value to a field, or getFieldValue(fieldId) to retrieve value from a record.

Please note that when adding date values, it must be in one of the following formats:

yyyy/MM/dd

yyyy/MM/dd HH:mm:ss

HH:mm:ss

If you're retrieving a value from a multiple selection field, where there may be multiple values, use getFieldValues(fieldId) to retrieve all the values in an array. You can also call setFieldValue(fieldId,value,true) with an extra true argument at the end to specify that you're "adding" an option to the current list of values, not overwriting the existing ones. Note that these operations is only suitable for multiple selection fields.

After you're done, just call save() on the record object to save it back to the database.

Subtables

Link to example

If you have subtables in a sheet, you can also use our API to retrieve data from it, or make edits like the following example. The form looks like this:

The workflow will walk through each row in the subtable, and find the total amount for this year (according to date field in the subtable), the total for the year with the most amount, and identify which year has the highest total. This is designed as a post-workflow, so the three read-only fields will be filled by workflow after the record is saved.

/**

* AP_Name:wfdemo

* Key Field: 1000006

* Date subtable key: 1000007

* Field Name Field Id

* - - - - - - - - - - - --------

* No. : 1000001

* Name : 1000002

* Date : 1000003

* Amout : 1000004

* Total of This Year : 1000010

* Maximal Total of Year : 1000009

* Year of Maximal Total : 1000008

*/

var KEY_FIELD = 1000006;

var AMOUNT_SUBTABLE_ID = 1000007;

var DATE_FIELD = 1000003;

var AMOUNT_FIELD = 1000004;

var MAX_YEAR_FIELD = 1000008;

var MAX_TOTAL_FIELD = 1000009;

var THIS_YEAR_TOTAL_FIELD = 1000010;

var query = db.getAPIQuery("/workflow-demo/1");

var entry = query.getAPIEntry(param.getNewNodeId(KEY_FIELD));

var subtableSize = entry.getSubtableSize(AMOUNT_SUBTABLE_ID);

var yearTotal = {}

for (var i = 0; i < subtableSize; i++) {

var year = parseInt(entry.getSubtableFieldValue(AMOUNT_SUBTABLE_ID, i, DATE_FIELD).substr(0, 4));

var amount = parseInt(entry.getSubtableFieldValue(AMOUNT_SUBTABLE_ID, i, AMOUNT_FIELD));

if (year in yearTotal) {

yearTotal[year] += amount;

} else {

yearTotal[year] = amount;

}

}

var maxYear;

for (var year in yearTotal) {

if (!maxYear || yearTotal[maxYear] < yearTotal[year]) {

maxYear = year;

}

}

entry.setFieldValue(MAX_YEAR_FIELD, maxYear);

entry.setFieldValue(MAX_TOTAL_FIELD, yearTotal[maxYear]);

entry.setFieldValue(THIS_YEAR_TOTAL_FIELD, yearTotal[new Date().getFullYear()]);

entry.save();

The basic idea is to use getSubtableSize(subtableId) to get the number of rows for a record, and use getSubtableFieldValue(subtableId,subtableRowIndex,subtableFieldId) to retrieve their values. You should be able to find subtable id, field id information in the auto generated comments when you start editing workflow scripts.

You can also use setSubtableFieldValue(subtableFieldId,subtableRootNodeId,value) to set values to a subtable. The subtableRootNodeId is used to specify which subtable row that you're referring to. To find a subtableRootNodeId for an existing subtable row, you can use the following call getSubtableRootNodeId(subtableId,subtableRowIndex) which will return an integer containing the subtableRootNodeId.

If you need to add a row to the subtable, you can use a negative subtableRootNodeId like -100, this way all values set to the same negative subtableRootNodeId will be applied to the same new subtable row, and values set to a different negative subtableRootNodeId like -101 will create different row in the subtable with this different set of values.

Copying records

Link to example: Copy From and Copy To

Copying records is one of the most common workflow program we encounter. We have written a pretty simple function to simplify this type of operation. Let's say we would like to see a record on this sheet:

With the click of the button, generate a record on this sheet:

Here is the code for this action button:

/**

* AP_Name:wfdemo

* Key Field: 1000022

* S1 subtable key: 1000023

* T1 subtable key: 1000029

* Field name Field ID

* - - - - - - - - - - - --------

* A : 1000014

* C : 1000015

* B : 1000016

* D : 1000017

* S1 : 1000018

* S2 : 1000019

* S3 : 1000020

* S4 : 1000021

* T1 : 1000024

* T2 : 1000025

* T3 : 1000026

*/

function copyEntry(nodeId) {

db.entryCopier(JSON.stringify({

THIS_PATH:"/workflow-demo/3",

THIS_NODEID:nodeId,

NEW_PATH:"/workflow-demo/4",

COPY:{

1000030:1000014, // A

1000031:1000015, // C

1000032:1000018, // S1

1000033:1000020 // S3

}

}),response);

}

Here you can see we can do the copy with one simple function call to entryCopier. entryCopier takes a JSON string as its parameter. Just put down the source sheet, target sheet, the record that we're copying, and most importantly, which field should be mapped to which field. When the mapping is complete, you can create action buttons to copy records from one sheet to another very easily.

Sending e-mail notifications

Sometimes you would like to send e-mail notifications based on a set of conditions, or you would like to really customize your notification message content. You can write server-side Javascript workflow for this purpose. Here is the script that you will use to send out e-mail, it's really simple:

//omitted...retrieve record object first

var name=entry.getFieldValue(1001426);

var email=entry.getFieldValue(1001428);

var title=entry.getFieldValue(1001386);

mailer.compose(

email, //to

null, //cc

'support@example.com', //reply to

'Acme, Inc.', //displayed from

title,

'Hi '+name+',

we have received your sales order '+

'and will be processing your order very soon.

'+

'You can see your order details at http://www.ragic.com/example/1

'+

'Thanks.


Best Regards,

Sophia, Sales Manager

Acme, Inc.'

);

//mailer.attach(myURL); //you can use .attach to attach content from a URL

mailer.send();

For attachments, you can use mailer.attach(myURL); to attach a record on Ragic by using the record's URL. For example, here is a record URL on Ragic (always ignore URL after the hash) :

http://www.ragic.com/wfdemo/workflow-demo/2/9

Here is its HTML printer friendly URL:

http://www.ragic.com/wfdemo/workflow-demo/2/9.xhtml

Here is its Excel version URL:

http://www.ragic.com/wfdemo/workflow-demo/2/9.xlsx

You can also use a Mail Merge URL like this, the cid being the id of the Mail Merge, you can get the cid in the URL when trying to download the Mail Merge document:

http://www.ragic.com/wfdemo/workflow-demo/2/9.custom?rn=9&cid=1

We do enforce some limitations on how many e-mails you can send. So send reasonably! If you have some questions on e-mail sending quota, send us an e-mail at support@ragic.com.

Approver and other information on a record

You can first issue the following command to the query object you get from db.getAPIQuery to include full record info:

query.setIfIncludeInfo(true);

and then you can get the approval information for a record like this:

entry.getFieldValue('_approve_status');//getting status of current approval

entry.getFieldValue('_approve_next');//getting the next person who should sign this record

entry.getFieldValue('_create_date');//getting the create date of the record

entry.getFieldValue('_create_user');//getting the create user e-mail of the record

The approval status will be F is approved, REJ for rejected, P for processing.

Filtering records with Javascript

If you want to get more than one records by filtering:

var query = db.getAPIQuery("/workflow-demo/1");

query.addFilter(1000002, '=', 'Green');

query.addFilter(1000008, '=', '2017');

var results = query.getAPIResultList();

for (var i = 0; i < results.length; i++) {

var entry = results[i];

// ...

}

You can add filters to query by addFilter(fieldId, operator, value), and call getAPIResultList() to get the list of records.

Show message

You can show message in a pop-up window like this. Please note that the message will only show when the response status is "WARN". By default the message inside the response object will not be displayed since it's usually not helpful to end users.

response.setStatus('WARN');

response.setMessage(message);

Send HTTP request

You can send an HTTP GET/POST request to an URL and get returned result:

util.getURL(String urlstring)

util.postURL(String urlstring,String postBody)

The variable util is predefined.

API references

ScriptAPIQuery (db.getAPIQuery)

MethodDescription
getAPIResult()Get first entry when iterate over the query
getAPIResultList()Get array of entries of the query
getAPIEntry(int rootNodeId)Get entry by node ID
insertAPIEntry()Insert a new entry to the query, the method returns the new entry
addFilter(int domainId, String operand, String value)Filtering entries by specified condition
setOrder(int orderDomain, int orderDir)Sort entries of the query by specified field domain ID and order direction, parameter orderDir is set to 1 if sort ascending, and set to 2 if sort descending.
deleteEntry(int nodeId)Delete entry by node ID

ScriptAPIEntry (From ScriptAPIQuery above)

MethodDescription
save()Save a entry
setCreateHistory(boolean createHistory)Set if the entry need to create history
isCreateHistory()Whether the entry is set to create history
setIfExecuteWorkflow(boolean executeWorkflow)Set if executing workflow (pre-workflow and post-workflow) of the entry is needed
setIgnoreEmptyCheck(boolean ignoreEmptyCheck)Set if checking not empty fields would be ignored
setRecalParentFormula(boolean recalParentFormula)If this sheet is created by subtable of other sheet, or is referenced by other sheet, which means, this sheet has the parent sheet, then you can call this method to set if you want to recalculate the parent sheet or not.
setFieldValue(int domainId, String value)Set value to specified single field
setFieldValue(int domainId, String value, boolean appendValue)Set value to single field which is a multiple select field, parameter appendValue need to be true
setSubtableFieldValue(int domainId, int subtableRootNodeId,String value)Set value to subtable field, you can get parameter subtableRootNodeId by method getSubtableRootNodeId.
setSubtableFieldValue(int domainId, int subtableRootNodeId, String value, boolean appendValue)Set value to subtable field which is a multiple select field, parameter appendValue need to be true
getJSON()Get JSON of the entry
getFieldValue(int domainId)Get value of the field by domain ID
getRootNodeId()Get root node ID of the entry
getRootDomainId()Get root domain ID of the entry
getSubtableSize(int subtableRootDomainId)Get size of subtable, specified by root domain ID of subtable.
getSubtableRootNodeId(int subtableRootDomainId, int rowNumber)Get root node ID of subtable, specified by its root domain ID and row number in subtable.
deleteSubtableRowByRowNumber(int subtableRootDomainId, int rowNumber)Delete subtable row by its root domain ID and row number in subtable.
deleteSubtableRowAll(int subtableRootDomainId)Delete every row in specified subtable
deleteSubtableRow(int subtableRootDomainId, int subtableRootNodeId)Delete subtable row by root domain ID and root node ID of subtable
loadAllListenFields()Load value of all loaded fields in the entry.
recalculateAllFormulas()Recalculate every field that contains formula in the entry.
recalculateFormula(int domainId)Recalculate formula of specified field.
loadAllDefaultValues(ScriptUser user)Load value of every field that is set with default value, parameter user is predefined.
loadDefaultValue(int domainId, ScriptUser user)Load default value of specified field, parameter user is predefined.
lock()Lock the entry
unlock()Unlock the entry

ScriptResponse (response)

MethodDescription
getStatus()Gets the status of the response. Either SUCCESS, WARN, CONFIRM, INVALID, ERROR
setStatus(String status)Sets the status of the response. Either SUCCESS, WARN, CONFIRM, INVALID, ERROR
setMessage(String plainMessage)Sets a message for display when script executed. This function can be called several times and all messages have been set will show at the same time.
numOfMessages()Returns the number of messages that has been set.

ScriptUser (user)

MethodDescription
getEmail()Get user's email address
getUserName()Get user's full name

ScriptMailer (mailer)

MethodDescription
compose(String to,String cc,String from,String fromPersonal,String subject,String content)Compose an e-mail message for sending out.
send()Send out the message that was just composed.
sendAync()Send out the message that was just composed asynchronously. Note that sendAsync can only be called once per script execution.
attach(String url)Attach a file to the message. The URL should be a full URL with http://

ScriptUser (util)

MethodDescription
getURL(String urlstring)Calls an URL with GET method
postURL(String urlstring,String postBody)Calls an URL with POST method. The postBody should be in the format of name1=value1&name2=value2

ScriptAccount (account)

MethodDescription
getUserName(String email)Get the full name of a user specified by the e-mail address.
reset()Purge all account related cache when the script and reloads the page finishes executing.

Top of Page
<< HTTP API integrationTable of Contents
Learn more about Ragic: Why we created Ragic | Webinars | User Guide.
Keep in touch: Blog | Email | Facebook | Twitter.
Ragic, Inc. | +1 888-666-8037 | Terms | Privacy