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.
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:
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.
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 https://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) :
https://www.ragic.com/wfdemo/workflow-demo/2/9
Here is its HTML printer friendly URL:
https://www.ragic.com/wfdemo/workflow-demo/2/9.xhtml
Here is its Excel version URL:
https://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:
https://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:
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
| Method | Description |
|---|
| 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
| Method | Description |
|---|
| 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 |
ScriptUser
| Method | Description |
|---|
| getEmail() | Get user's email address |
| getUserName() | Get user name |
which will show you a demo on how the custom e-mail looks like. If you decide to use our standard template instead, click on 
will discard all the changes you've made.
to apply the new view.
