All Custom Functions List

This article lists down all Custom Supported Functions implemented for DronaHQ Studio.

LOOKUP

LOOKUP is used to retrieve data from any sheet.

Definition

LOOKUP([Sheetname.columnname],filter,sorting,order)

LOOKUP([Sheetname.columnname AS “”],filter,sorting,order)

LOOKUP([Sheetname.columnname], filter, limit, offset)

Where,

Sheetname is the sheet’s name from where you want to fetch the data; columnname is the column whose data you want to fetch.

filter is used to filter results in the sheet. Filter has three components, i.e. Column to filter, filter operator & filter criteria. It has the following format - Sheetname.columnname1=“Studio”

sorting is used for sorting the result set on the basis of the column specified in either ascending (“ASC”) or descending (“DESC”) order;

order is the order in which you want to sort the data fetched from the sheet. It can have value either ASC or DESC which needs to be put within double quotes “”.

AS is used to provide a custom/user-friendly name to refer to this column. This is also useful to bind data from Multiple LOOKUP and assign it to a Control.

limit is typically the value from the PROPERTIES.LIMIT assigned for Page Limit property in the table grid properties. For more information refer this article here.

offset is typically value from the PROPERTIES.OFFSET assigned for the OFFSET property in the table grid properties. For more information refer this article here.

Example 1

LOOKUP([BaseSheet.name.first_name],BaseSheet.email=“olag.gentil@house.gov”)

Example 2

LOOKUP([BaseSheet.company AS “ORG”, BaseSheet.department AS “DEP”])

For More Info - Refer here

DLOOKUP

DLOOKUP is much more advanced than LOOKUP which is used to retrieve data from any sheet. You can use All supported formula’s in DLOOKUP’s filter which is not possible in LOOKUP function which supports only AND and OR function. For Multiple conditions in Filter you can use DAND and DOR functions which are same as AND and OR function which is supported by LOOKUP

Definition

DLOOKUP([Sheetname.columnname],filter,sorting,order)

DLOOKUP([Sheetname.columnname AS “”],filter,sorting,order)

Example 1

DLOOKUP([BaseSheet.name.first_name],BaseSheet.email=“olag.gentil@house.gov”)

Example 2

DLOOKUP([BaseSheet.company AS “ORG”, BaseSheet.department AS “DEP”])

Example 3

DLOOKUP([BaseSheet.company AS “ORG”, BaseSheet.department AS “DEP”],DAND(BaseSheet.name = “OLAD”, BaseSheet.emal $cn “.gov”))

DATESTR

This is a custom function that converts date (Unix format) to readable date format, say, 29/Apr/2019…

Definition

DATESTR(date,format,isLocalTime)
isLocalTime is an optional parameter.

Example 1

DATESTR(TODAY(),“MM/DD/YYYY”)

Output

12/12/2019

Example 2

DATESTR(datepicker,“DD YYYY”)

For More Info - Refer here

STRTODATE

This function converts String date to Date object similar to TODAY() function.

Definition

STRTODATE(strDate,format)

Where,
strDate - Date in string format
format - Given String date’s format

Example 1

STRTODATE(“03-12-2019 17:30”,“DD-MM-YYYY HH:mm”)

Output

2019-12-02T18:30:00.000Z

For More Info on Format - Refer here

STRTOUNIX

This function converts string date to Unix timestamp.

Definition

STRTOUNIX(strDate,format)

Where,
strDate - Date in string format
format - Given String date’s format

Example 1

STRTOUNIX(“03-12-2019 17:30”,“DD-MM-YYYY HH:mm”)

Output

1575394200000

Example 2

STRTOUNIX(“03-12-2019”,“DD-MM-YYYY”)

Output

1575331200000

For More Info on Format - Refer here

TIMEOFFSET

This function provides Time zone offset in milliseconds. This can either positive or negative based on the device’s timezone

Definition

TIMEOFFSET()

Example 1

TIMEOFFSET()

Output for GMT -5:30

19800000

Output for GMT +5:30

-19800000

DATEDIF

Calculates the number of days, months, or years between two dates.

Definition

DATEDIF(start_date,end_date,unit)

Example

DATEDIF( datepicker , datepicker1 , “D”)

For More Info - Refer here

TIMESTR

This is a custom function that converts date (Unix format) to readable time format, say, 09:58 PM.

Definition

TIMESTR(date,format,isLocalTime)
isLocalTime is an optional parameter. If isLocalTime is enabled you would get the time as the Local time. By default it is UTC.

Example

With isTimeLocal disabled

TIMESTR(NOW()," HH:mm", 0)

Output

07:58

With isTimeLocal enabled

TIMESTR(NOW()," HH:mm", 1)

Output

13:29 for IST

STRING

It is used to convert NUMBER TO TEXT. This function takes a Number OR Array of Numbers as Input and gives a String OR Array of Strings as Output. Basically this is useful when you have a NUMBER and you want to apply some function on it which is applicable to TEXT type but not applicable to NUMBER type. For example. - CONCATENATE() function is available for TEXT type but not available for NUMBER.

Definition

STRING(number)
STRING([number1, number2, number 3, …])

Example 1

STRING(1)

Output

“1”

Example 2

STRING([1,2])

Output

[“1”,“2”]

NUMBER

It is used to convert TEXT TO NUMBER. This function takes a Text OR Array of Texts as Input and gives a Number OR Array of Numbers as Output. Basically this is useful when you have a TEXT and you want to apply some function on it which is applicable to NUMBER type but not applicable to TEXT type. For example. - SUM() function is available for NUMBER type but not available for TEXT.

Definition

NUMBER(text)
NUMBER([text1, text2, text 3, …])

Example 1

NUMBER(“1”)

Output

1

Example 2

NUMBER([“1”,“2”])

Output

[1,2]

SELECT

Based on the Given Index, return a value from provided list of values as an array as the second parameter or from a list of parameters. Index values start from 1

Definition

SELECT(val1,val2,…,valn, index)
SELECT([val1,val2,…,valn], index)

Example 1

SELECT(“A”,“B”,“C”, 2)

Output

B

Example 2

SELECT([“A”,“B”,“C”], 2)

Output

B

SELECTANY

This function gives one random value between the input entered. This function accepts an integer, string, array, and values separated by a comma.

Definition

SELECTANY(val1,val2,…,valn)
SELECTANY([val1,val2,…,valn])

Example 1

SELECTANY(“A”,“B”,“C”)

Output

B

Example 2

SELECTANY([“A”,“B”,“C”])

Output

A


INDEX

It is used to get Index of an Item in an Array. Useful to search an item in an Array and get its index. Index starts from 1.

Definition

INDEX([text1, text2, text 3, …], textToFind)

Example 1

INDEX([“A”,“B”,“C”,“D”],“B”)

Output

2


INDEXVALUE

It is used to get Item in an Array for a given Index. Index starts from 1.

Definition

INDEXVALUE([text1, text2, text 3, …], indexNumber)

Example 1

INDEXVALUE([“A”,“B”,“C”,“D”,“E”],2)

Output

B


UNIQUEOBJECTS

It is used to get Unique values based on the given Column Index. In the case of Duplicate Column values for the given index, the first value will be returned, and rest all duplicates will be filtered out. ColumnIndexNumber starts at 1.

Definition

UNIQUEOBJECTS([column1, column2, column3, …], ColumnIndexNumber)

Example 1

UNIQUEOBJECTS(LOOKUP([ProfileViews.unique_id,ProfileViews.Name]),2)

Output

[{“unique_id”:1,“Name”:“Fenil”},{“unique_id”:2,“Name”:“Ramanuj”},{“unique_id”:3,“Name”:“Divyesh D”},{“unique_id”:4,“Name”:“Rishabh”},{“unique_id”:5,“Name”:“Rahul G”},{“unique_id”:6,“Name”:“Brijesh”},{“unique_id”:7,“Name”:“Ronak”},{“unique_id”:8,“Name”:“Luv”}]


CONCATENATE

Is used to join two or more strings or values from controls.

Definition

CONCATENATE( text1, [text2], …)

Example

CONCATENATE(“DRONA”, “HQ”, " Welcomes", " You!!")

Output:

DRONAHQ Welcomes You!!

MERGE

It is used to Merge Multiple Arrays into a single array. It can also be used to merge individuals items into a single array.

Definition

MERGE([item1, item2, item3, …], [item4, item5, item6, …])
MERGE(item1, item2, item3, …)
MERGE([item1, item2, item3, …], item4, item5, …)

Example 1

MERGE( [2,3, 5],[2,4,5])

Output

[2,3,5,2,4,5]

Example 2

MERGE(2,3,5)

Output

[2,3,5]

Example 3

MERGE([2,3,5],7,8,9)

Output

[2,3,5,7,8,9]


FLATTEN

It is used to Flatten Array of Multiple Arrays into a single array. It works with LOOKUP of Multiselect Column of the sheet.

Definition

FLATTEN( [ array1, array2, … ] )
FLATTEN(LOOKUP([AllColumnTypes.Multiselect]))

Example 1

FLATTEN([[2,3,5],[2,4,5]])

Output

[2,3,5,2,4,5]

Example 2

FLATTEN( LOOKUP([Candidate_details_doc.Grade])) assuming that the Grade column in the sheet has values [A,B,C]

Output

[A,B,C]


UNIQUEIDGENERATOR

It is used to generate a UNIQUE ID. You can also optionally pass USERID as a parameter to this function, in both cases, a unique id will be generated.
You can also give an Array as an Input and the output will be an Array of Unique IDs.
UserID can be Number or Alphabets.

Definition

UNIQUEIDGENERATOR()
UNIQUEIDGENERATOR(userID)
UNIQUEIDGENERATOR( [userID1, userID2, … ] )

Example 1

UNIQUEIDGENERATOR()

Output

507-829-228-1ds9u81ea

Example 2

UNIQUEIDGENERATOR(“ABC”)

Output

ABC-903-506-1ds9ua9o0

Example 3

UNIQUEIDGENERATOR([“AB”, “CD”])

Output

[“AB-549-438-1ds9uc0e7”,“CD-948-641-1ds9uc0e7”]


RANGE

Given two numbers this function will return the integers between these two values entered

Definition

RANGE(number1,number2)

Example

RANGE(5,10)

Output

[5,6,7,8,9,10]


UNIXTIME

EXCEL TIME TO UNIX TIME CONVERTER. This function will convert Given Excel time and give Unix time as a result.
It also accepts optional Default value in case of incorrect ExcelTime is given as Input. This Function can also Accept Array of Excel time and give an array of Unix time as output. You can also choose the Input and Output Format in an optional parameter.

** Unix time is calculated from 1 Jan 1970. Excel time is calculated from 1, Jan 1900.

Definition

UNIXTIME(ExcelTime)
UNIXTIME(ExcelTime,defaultInputTime)
UNIXTIME(ExcelTime,defaultInputTime,InputFormat, OutputFormat)

UNIXTIME( [ ExcelTime1, ExcelTime2, …] )
UNIXTIME( [ ExcelTime1, ExcelTime2, …] , defaultInputTime)
UNIXTIME( [ ExcelTime1, ExcelTime2,…],defaultInputTime, InputFormat, OutputFormat)

Where,

defaultInputTime must be greater than or equal to 25569 and will be used in case of invalid Exceltime. defaultInputTime format will be same as InputFormat

Input Format & OutputFormat can be any one of the below -
“m” => milliseconds
“s” => seconds
“d” => days
If no Input/Output format is given it will consider being default Input/Output format.
Default Input => “d” (days)
Default Output => “m” (milliseconds)*

Example 1

UNIXTIME(25571)

Output

172800000

Example 2

UNIXTIME(255, 25569)

Output

0

Example 3

UNIXTIME(25571, 25569, “d”,“d”)

Output

2

Example 4

UNIXTIME([25571,25575, 255], 25569, “d”,“d”)

Output

[2,6,0]


EXCELTIME

UNIX TIME TO EXCEL TIME CONVERTER. This will convert Given Unix time in milliseconds and give Excel time in days as a result.
It also accepts optional Default value in case of incorrect UnixTime is given as Input. This Function can also Accept Array of Unix time and give an array of Excel time as output. You can also choose the Input and Output Format in an optional parameter.

** Unix time is calculated from 1 Jan 1970. Excel time is calculated from 1, Jan 1900.

Definition

EXCELTIME(UnixTime)
EXCELTIME(UnixTime,defaultInputTime)
EXCELTIME(UnixTime,defaultInputTime,InputFormat, OutputFormat)

EXCELTIME( [ UnixTime1, UnixTime2, …] )
EXCELTIME( [ UnixTime1, UnixTime2, …] , defaultInputTime)
EXCELTIME( [ UnixTime1, UnixTime2,…],defaultInputTime, InputFormat, OutputFormat)

Where,

defaultInputTime will be used in case of invalid Unixtime. defaultInputTime format will be same as InputFormat

Input Format & OutputFormat can be any one of the below -
“m” => milliseconds
“s” => seconds
“d” => days
If no Input/Output format is given it will consider being default Input/Output format.
Default Input => “m” (milliseconds)*
Default Output => “d” (days)

Example 1

EXCELTIME(172800000)

Output

25571

Example 2

EXCELTIME(-1,1)

Output

25569.000000011572

Example 3

EXCELTIME(2, 0, “d”,“d”)

Output

25571

Example 4

EXCELTIME([2,6,0], 0, “d”,“d”)

Output

[25571,25575,25569]


DISTANCE

This function is useful to find the distance(in Metre) between two given points i.e, destination location and source location with their ( Latitude, Longitude Values) in a CSV format i,e, (lat, long). It also accepts optional default distance parameter, which is returned in case there is an error is occurred for given source & destination Location.

Definition

DISTANCE(destinationLocation,sourceLocation)
DISTANCE(destinationLocation, sourceLocation, defaultDistance)
DISTANCE([destinationLocation1,destinationLocation2,…],sourceLocation)
DISTANCE([destinationLocation1,…],sourceLocation, defaultDistance)

Example 1

DISTANCE( “45.7458,74.9864” , “40.7486,-73.9864”)

Output

9915192.888361111

Example 2

DISTANCE( [“45.7458,74.9864”,“45.64,74.88”], “40.7486,-73.9864”)

Output

[9915192.888361111,9922787.743762657]

Example 3

DISTANCE( [“45.7458”,“45.64,74.88”], “40.7486,-73.9864”,100)

Output

[100,9922787.743762657]


ISPLACENEARBY

It is used to check whether the distance between the source and destination location is within the given distance. It also accepts multiple destinations to check whether the given source in within a given distance or not. This function has an optional input unit which basically denotes the distance format. If no input unit is specified, it will by default consider the given distance is in meters.

Definition

ISPLACENEARBY(destinationLocation,sourceLocation, distance)
ISPLACENEARBY(destinationLocation,sourceLocation, distance, inputUnit)
ISPLACENEARBY([destinationLocation1, …],sourceLocation, distance)
ISPLACENEARBY([destinationLocation1, …],sourceLocation, distance, inputUnit)

Where inputUnit can be any one of the below -
“m” => meters
“km” => Kilimeters
“miles” => miles

Example 1

ISPLACENEARBY(“70.5,80.5”,“10.5,20.5”,10000000)

Output

true

Example 2

ISPLACENEARBY(“70.5,80.5”,“10.5,20.5”,10000,“km”)

Output

true

Example 3

ISPLACENEARBY([“70.5,80.5”,“10.5,25.5”,“11.5,21.5”],“10.5,20.5”,1000, “km”)

Output

[false,true,true]


PLACESNEARBY

This function returns those destinations location pairs that are within the distance from the source location. This function has an optional input unit which basically denotes the distance format. If no input unit is specified, it will by default consider the given distance is in meters.

Definition

PLACESNEARBY(destinationLocation,sourceLocation, distance)
PLACESNEARBY(destinationLocation,sourceLocation, distance, inputUnit)
PLACESNEARBY([destinationLocation1, …],sourceLocation, distance)
PLACESNEARBY([destinationLocation1, …],sourceLocation, distance, inputUnit)

Where inputUnit can be any one of the below -
“m” => meters
“km” => Kilimeters
“miles” => miles

Example 1

PLACESNEARBY(“70.5,80.5”,“10.5,20.5”,10000000)

Output

[“70.5,80.5”]

Example 2

PLACESNEARBY(“70.5,80.5”,“10.5,20.5”,10000,“km”)

Output

[“70.5,80.5”]

Example 3

PLACESNEARBY([“70.5,80.5”,“10.5,25.5”,“11.5,21.5”],“10.5,20.5”,1000, “km”)

Output

[“10.5,25.5”,“11.5,21.5”]


LEFTJOIN

Perform Left Join on given two tables (Sheets) with LOOKUP/DLOOKUP formula. This works similar to the left join in SQL. This function has the first 4 required parameters, which are -

  1. LOOKUP/DLOOKUP for sheet 1,

  2. LOOKUP/DLOOKUP for sheet 2

  3. Key Name for comparison from sheet 1

  4. Key Name for comparison from sheet 2.

It also has 3 optional parameters after above first 4, which are:

  1. MODE: This is a number type and it applies only to the key1 & key2 used for comparison

  2. ORDERBY: This is a string type that accepts the column name according to which ordering/sorting should be done

  3. ORDERBYTYPE: This is a string that accepts “ASC”/“DESC”, means ORDERBY can be done in ascending/descending order.

Definition

LEFTJOIN( LOOKUP(sheet1), LOOKUP(sheet2), “key1”, “key2”)
LEFTJOIN( LOOKUP(sheet1), LOOKUP(sheet2), “key1”, “key2”, MODE)
LEFTJOIN( LOOKUP(sheet1),LOOKUP(sheet2),“key1”,“key2”, MODE,ORDERBY)
LEFTJOIN( LOOKUP(sheet1), LOOKUP(sheet2), “key1”, “key2”, MODE, ORDERBY, ORDERBYTYPE)

Where Mode can be 1 of the below 4 values,
1 => Include key1 of sheet1 from the comparison and do not include key2
2 => Include key2 of sheet2 from the comparison and do not include key1
3 => Include both key1 & key2 of sheet1 and sheet2 from the comparison
4 => Do not include oth key1 & key2. This is useful if you do not want the internal keys which you used in comparison in your response/output

Note: If no MODE is present, by default 3 is selected

Example 1

LEFTJOIN(LOOKUP( [employee.emp_name,employee.dep_id AS “DEP1”]) ,LOOKUP( [department.dep_id AS “DEP2”,department.dep_name]) , “DEP1” , “DEP2”)

Output

[{“emp_name”:“Alice”,“DEP1”:12,“DEP2”:12,“dep_name”:“Sales”},{“emp_name”:“Dan”,“DEP1”:14,“DEP2”:14,“dep_name”:“Engineering”}]

Example 2

LEFTJOIN(LOOKUP( [employee.emp_name,employee.dep_id AS “DEP1”]) ,LOOKUP( [department.dep_id AS “DEP2”,department.dep_name]) , “DEP1” , “DEP2”),1,“DEP1”,“DESC”)

Output

[{“emp_name”:“Dan”,“DEP1”:14,“dep_name”:“Engineering”},
{“emp_name”:“Alice”,“DEP1”:12,“dep_name”:“Sales”}]


RIGHTJOIN

Perform Right Join on given two tables (Sheets) with LOOKUP/DLOOKUP formula. This works similar to the right join in SQL.

Definition

RIGHTJOIN( LOOKUP(sheet1), LOOKUP(sheet2), “key1”, “key2”)
RIGHTJOIN( LOOKUP(sheet1), LOOKUP(sheet2), “key1”, “key2”, MODE)
RIGHTJOIN( LOOKUP(sheet1),LOOKUP(sheet2),“key1”,“key2”, MODE,ORDERBY)
RIGHTJOIN( LOOKUP(sheet1), LOOKUP(sheet2), “key1”, “key2”, MODE, ORDERBY, ORDERBYTYPE)

Note: All parameters of this function is the same as LEFTJOIN function described above. Please refer to LEFTJOIN Definition & Examples section for more details.


FULLJOIN

Perform Full Join on given two tables (Sheets) with LOOKUP/DLOOKUP formula. This works similar to the full join in SQL.

Definition

FULLJOIN( LOOKUP(sheet1), LOOKUP(sheet2), “key1”, “key2”)
FULLJOIN( LOOKUP(sheet1), LOOKUP(sheet2), “key1”, “key2”, MODE)
FULLJOIN( LOOKUP(sheet1),LOOKUP(sheet2),“key1”,“key2”, MODE,ORDERBY)
FULLJOIN( LOOKUP(sheet1), LOOKUP(sheet2), “key1”, “key2”, MODE, ORDERBY, ORDERBYTYPE)

Note: All parameters of this function is the same as LEFTJOIN function described above. Please refer LEFTJOIN Defintion & Examples section for more details.


INNERJOIN

Perform Inner Join on given two tables (Sheets) with LOOKUP/DLOOKUP formula. This works similar to the inner join in SQL.

Definition

INNERJOIN( LOOKUP(sheet1), LOOKUP(sheet2), “key1”, “key2”)
INNERJOIN( LOOKUP(sheet1), LOOKUP(sheet2), “key1”, “key2”, MODE)
INNERJOIN( LOOKUP(sheet1),LOOKUP(sheet2),“key1”,“key2”, MODE,ORDERBY)
FULLJOIN( LOOKUP(sheet1), LOOKUP(sheet2), “key1”, “key2”, MODE, ORDERBY, ORDERBYTYPE)

Note - All parameters of this function is the same as LEFTJOIN function described above. Please refer LEFTJOIN Defintion & Examples section for more details.


SETTOKV

Using this function data can be set to KV storage (key Value storage) which is a persistent data store.

Definition

SETTOKV(key,value)
SETTOKV(key,value, isGlobal, isWritable)

Where -
key - Specific string with which you can store or access data
value - represents your data
global - Either 0 OR 1 , Set to 1 if you want this key to be accessible by other MicroApps
isWritable - Either 0 OR 1 , Set to 1 if you want this key to be editable by other MicroApps

Note - This function doesn’t work as expected in Preview but will work on Client Apps after it is published

Example 1

SETTOKV(“UserOpen”,“Value to Save”)

Output

“”

Example 2

SETTOKV(“SharedKey”,“Value to Save”, 1, 1)

Output

“”


GETFROMKV

Using this function data can be get from KV storage (key Value storage) which is a persistent data store. This will get data from KV storage using the key which was used during SETTOKV function. This function doesn’t work in Preview but will work on Client Apps after it is published

Definition

GETFROMKV(key)
GETFROMKV(key, isGlobal)

Where -
key - Specific string with which you have stored the data using SETTOKV
isGlobal - Either 0 OR 1 , Set to 1 if you want the key’s value which was SETTOKV using isGlobal as 1

Note - This function doesn’t work as expected in Preview but will work on Client Apps after it is published

Example 1

GETFROMKV(“UserOpen”)

Output

“Value to Save”

Example 2

GETFROMKV(“SharedKey”,1)

Output

“Value to Save”


DELETEFROMKV

Using this function you can delete data stored in KV storage (key Value storage) with a specific key which was used to store data using SETTOKV function.

Definition

DELETEFROMKV(key)
DELETEFROMKV(key, isGlobal)

Where -
key - Specific string with which you have stored the data using SETTOKV
isGlobal - Either 0 OR 1 , Set to 1 if you want the key’s value which was SETTOKV using isGlobal as 1

Note - This function doesn’t work as expected in Preview but will work on Client Apps after it is published

Example 1

DELETEFROMKV(“UserOpen”)

Output

“”

Example 2

DELETEFROMKV(“SharedKey”,1)

Output

“”


CLEARKV

Using this function you can clear all data from KV storage (key Value storage).

Definition

CLEARKV()

Note - This function doesn’t work as expected in Preview but will work on Client Apps after it is published

Example

CLEARKV()

Output

“”


MAPKEYS

Is used for mapping the keys to the new key names and displaying them likewise. It is further useful to change the order of the fetched columns.

Definition

MAPKEYS([ArrayOfJsonobjects.Key1,ArrayOfJsonobjects.Key2,…],“newkeyname1,newkeyname2,…”)

OR

MAPKEYS([ArrayOfJsonobjects.Key1,ArrayOfJsonobjects.Key2,…],“newkeyname1:Key1,newkeyname2:Key2,…”)

OR

MAPKEYS([ArrayOfJsonobjects.Key1,ArrayOfJsonobjects.Key2,…],“Key1 AS newkeyname1, Key2 AS newkeyname2, …”)

Where,

  • Newkeyname<1>: is the new label assigned to the respective key
  • Key<1>: is the default key name or label of the respective header of the populated columns from any source

The columns would be displayed in the order specified when defining the keynames.

Example 1

MAPKEYS( LOOKUP([Prod_depend_Doc.Prod_ID,Prod_depend_Doc.Prod_name,Prod_depend_Doc.Quantity,Prod_depend_Doc.Price]) , “Prod_name AS ProductName , Quantity AS Qty, Price AS CostPrice, Prod_ID AS ProductID”)

where
AS is used to define the new keyname and the order of columns has been changed as well.

For More Info on using the funtion - Refer the article here.


FILTER

This is used to fetch data from a data store and provide filtered data from that data store. The FILTER function is ideally used with the list controls like TableGrid and Detail view.

Definition

FILTER([datastore.columnname1, datastore.columnname2,…], Filter or Condition, Sort column,Order)

Where,

  • datastore : is the name of the datastore created earlier to fetch data from a sheet or API.
  • Columnname : is the column whose data is to be fetched.
    • In case you need to fetch data from more than one column, then add the list of columns within the square [ ] brackets separated by comma as [datastore.columnname1,datastore.columnname2]
  • Filter: is the filter format to fetch data from the column.

Example

FILTER ([prod_datastore.result.rows.name, prod_datastore.result.rows.author_id] , prod_datastore.result.rows.author_id > 6,prod_datastore.result.rows.name, “DESC”)

For More Info on using the funtion - Refer the article here.


BINDAPI

The BINDAPI() function is used to fetch data from a connector data source and bind it to a control. Also you can optionally filter data and sort fetched data in ascending or descending order.

Definition

BINDAPI([connectorname.result.rows.columnname1, connectorname.result.rows.columnname2,…], Filter Condition, Sort column,Order)

Where,

  • Connectorname.result.rows.columnname: is the column whose data is to be fetched.
    • In case you need to fetch data from more than one column, then add the list of columns within the square [ ] brackets separated by comma as [Connectorname.result.rows.columnname1,Connectorname.result.rows.columnname2]
  • Filter: is the filter format to fetch data from the column. There are three components as Columns to be filtered, filter operator & filter criteria. In case you need to state multiple filters you can use AND (for all filter criteria match) or OR (for any one of the filter criteria match).
    • The condition will be taken as datastore.columnname1=""

The parameters mentioned below are optional:

  • Sorting: is used to sort the result set on the basis of the column specified.
  • Order: is the order in which you want to sort the data fetched from the sheet. It can have value either ASC or DESC which needs to be put within double quotes “”.

Important Note: Filter and Sorting happen at the client-side once we get the result from API.

Example

BINDAPI( [AuthorMongoDB.result.rows.author_id,AuthorMongoDB.result.rows.name,AuthorMongoDB.result.rows.last_name], AuthorMongoDB.result.rows.name $cn “A”, AuthorMongoDB.result.rows.last_name, “DESC”)

For More Info on using the funtion - Refer the article here.


STRINGTOJSON

When you have a string JSON, for example in scenarios where the server returns JSON as strings, you can make use of this function to convert the string to a JSON.

Definition

STRINGTOJSON(string JSON)

Where,

  • String JSON : is a valid JSON of type String

The function will parse the JSON column data of a single row only.

Example

STRINGTOJSON(LOOKUP([sheet.jsonColumn],sheet.unique_id=12))

This will result into the LOOKUP fetching the String column from the Sheet where the Unique Id is 12 and returning it as a JSON.

For More Info on using the funtion - Refer the article here.


MERGEJSON

The MERGEJSON function allows you to parse the data from multiple rows of a JSON column. The JSON column would contain an array of objects.

Definition

MERGEJSON(LOOKUP([sheet.jsonColumn]))

Where,

  • sheet: is the sheet that contains a JSON column
  • jsonColumn: is the column that contains an array of objects.

Example

MERGEJSON( LOOKUP([Prod_depend_Doc.json_array]))

returnS an array of JSON objects.

For More Info on using the funtion - Refer the article here.

GETKEYVALUE

When you need to get only the value from a specific key typically where key-value pairs are returned, you can use the GETKEYVALUE function.

Definition

GETKEYVALUE(object, “keyname”)

Where,

  • object: is the JSON object from where the value is to be fetched.
  • Keyname: is the default key name or label of the JSON object.

Example

GETKEYVALUE( STRINGTOJSON( LOOKUP([Prod_depend_Doc.json_object] , Prod_depend_Doc.unique_id = 1)) , “name”)

returns the value in the “name” key i.e. John in this case.

For More Info on using the funtion - Refer the article here.

ARRAYTOCSV

The ARRAYTOCSV function converts your array data into comma-separated values .

Definition

ARRAYTOCSV(ArrayValue)

Where,

  • ArrayValue is an array input.

Example

ARRAYTOCSV(array[“a”,“b”,“c”] )

Will result into: a,b,c

For More Info on using the funtion - Refer the article here.

SHOWDETAILS

This is used to show the details from the selected row of the TableGrid control. It displays the selected data from TableGrid in Detail View control. The value returned by the function is a JSON array with the dynamic keys provided in the function.

Definition

SHOWDETAILS(“newkey1”, tablegrid.columnname1,”newkey2”, tablegrid.columnname2)

Where,

  • Newkey<1>: is the new label assigned to the respective key
  • tablegrid.columnname<1>: is the specific column from the tablegrid control.

Here you can also change the order in which the keys appear. So the records will be displayed in the order in which you added the new keys.

Example

SHOWDETAILS(“name”, tableGrid.name, “id”, tableGrid.id)

For More Info on using the funtion - Refer the article here.


ESCAPEDOUBLEQUOTE

The ESCAPEDOUBLEQUOTE function helps you to ignore the double quotes that are typically present in JSON type data returned by the server or by advanced controls like Form Repeat control, Summary control, and so on.

Definition

ESCAPEDOUBLEQUOTE(string/text/json value)

Where,

  • <string/text/json value>: is the JSON value returned by APIs or control selections

Example

ESCAPEDOUBLEQUOTE( [{“productname2”:“Laptop”,“quantity1”:101}])

returns

[{“productname2”:“Laptop”,“quantity1”:101}]

For More Info on using the funtion - Refer the article here.


ESCAPESINGLEQUOTE

The ESCAPESINGLEQUOTE function helps you to ignore the single quotes that are typically present in data that you want to insert in SQL or any relational database.

Definition

ESCAPESINGLEQUOTE(string / json field)

Where,

  • <string/json>: is the column or field value that contains the single quote symbol

Example

ESCAPESINGLEQUOTE ([{“productname2”:“Welch’s Fruit Snacks”,“price”:20}] )

returns

[{“productname2”:“Welch\’s Fruit Snacks”,“price”:20}

For More Info on using the funtion - Refer the article here.


STRINGENCODE

The STRINGENCODE enables you to accept a String as input and returns an Encoded string value for the same.

Definition

STRINGENCODE(StringInput)

Where,

  • StringInput: Is the String that is to be encoded

Example

STRINGENCODE(“Welcome to DronaHQ!%”)

returns

Welcome%20to%20DronaHQ!%25

For More Info on using the funtion - Refer the article here.