Utility functions¶

formatDate

Format date to 'YYYY-MM-DD' to be used in a date-type column.

base.utils.formatDate(date: Date Object)

Output String

Example

let date = new Date();
let formatDate = base.utils.formatDate(date);
output.text(formatDate);

formatDateWithMinutes

Format date to 'YYYY-MM-DD HH:mm' to be used in a date-type column.

base.utils.formatDateWithMinutes(date: date object)

Output String

Example

let date = new Date();
let formatDate = base.utils.formatDateWithMinutes(date);
output.text(formatDate);

lookupAndCopy

Similar to the Microsoft Excel VLOOKUP function. Find a matching row in the source table for each row of the target table, and then copy the data of the specified cell of the matching row to the specified cell of the target row. Every arguments are String.

base.utils.lookupAndCopy(targetTable, targetColumn, targetColumnToCompare, sourceTableName, 
                         sourceColumnName, sourceColumnToCompare = null /* (1)! */);

1. targetTable: the name of the target table - i.e. the table you want to copy data into

   targetColumn: the column of targetTable you want to copy data into

   targetColumnToCompare: the column of targetTable you want to compare to a column of table sourceTableName

   sourceTableName: the source table - i.e. the table you want to copy data from

   sourceColumnName: the column of sourceTableName you want to copy data from

   sourceColumnToCompare: If specified, the column of sourceTableName you want to compare with targetColumnToCompare to find matching rows. If not specified, the system will look for a column with the name targetColumn in the table sourceTableName

Output Nothing (throws an error if some tables or columns do not exist)

Principle example

Here are two tables, the source table containing both names and emails for few Avengers whereas the target table only has the user names.

Source table

Target table

To copy the email addresses from the source table to the target table, this function can be used with the following syntax:

base.utils.lookupAndCopy('Target table', 'TargetEmail', 'Name', 'Source table', 'SourceEmail');

Example

// Match the rows with the same content in the Name column of Table1 and Table2, 
// copy the contents of the Email column of the row in Table1 to the Email column 
// of the corresponding row in Table2
base.utils.lookupAndCopy('Table2', 'Email', 'Name', 'Table1', 'Email');

// Match the rows with the same content in the Name column in Table1 and the Name1 column
//  in Table2, and copy the contents of the Email column of the row in Table1 to the 
// Email1 column of the corresponding row in Table2
base.utils.lookupAndCopy('Table2', 'Email1', 'Name1', 'Table1', 'Email', 'Name');

query

Filter and summarize the table tableName data of the view viewName by SQL like query statements.

base.utils.query(tableName: String, viewName: String, query: String);

Example

// Filter out the rows where the sum of the three columns 'number', 'number1', 
// and 'number2' is greater than 5 then sum the number and number2 columns in these rows, 
// return {number: 12, number2: 23}
base.utils.query('Table1', 'Default View', 'select sum(number), sum(number2) where number + number1 + number2 > 5');