This article is a documentation for all the formulas supported in Fyle’s advanced export settings. To know more about what else is possible with Advanced Export Settings, please refer to this article.
Each column in your export data:
can be mapped to one of the available fields, or
can be assigned with a static/constant value, or
can be transformed by applying a valid formula which takes constants or other columns as parameters.
If you’re applying a formula to any column, these are the general rules you have to keep in mind:
Each formula will look like
formula_name(parameter_1, parameter 2, … parameter n)
where formula_name is the name of the formula and contains one or more parameters enclosed within brackets.
Parameters can be constant values (Eg: 97, “apple”, etc) or they can also be values of other columns.
If a parameter is referring to another column, the formula field() has to be used. Eg: If you want to use the field amount in a formula called sum(), it has to be written as sum(97, field(amount)).
Only the default columns shown in the Column source dropdown can be used in a formula, i.e, user-defined columns cannot be used.
The formulas and parameters are case insensitive, i.e., sum(97, field(amount)), Sum(97, Field(Amount)) and SUM(97, FieLD(TOtal)) are all the same. The only thing to keep in mind - if the field name has spaces, maintain the same inside the formula. Eg: If you’re using the column created on in a formula, regardless of the case, space has to be maintained, i.e. Concat(Field(created_at), ' by John').
If you’re using a column that contains special characters in its name, enclose the name within quotes when using it in the formula. Eg: field(“Name(# of expenses)”) where Name(# of expenses) is the name of the column.
Now let’s see all the formulas available at our disposal:
1. field( )
Description: Used for referring a column when it is being used in another formula
Number of parameters: 1
Parameter: Name of the column. (Note: when the column name consists of special characters, enclose the name within quotes)
Return type: Value of the referenced column
Example:
sum(97, field(amount)) returns the sum of 97 and the value in the Amount column.
2. concat( )
Description: Used for concatenating two strings or numbers into a single string
Number of parameters: 2
Parameter types: Both the parameters can be strings, numbers, or references to default columns
Return type: String
Example:
concat(76, 93) returns "7693".
concat("Total amount is", field(amount)) returns "Total amount is 538", given that the value of amount for that row is 538.
3. round( )
Description: Used for rounding off a number to its nearest integer
Number of parameters: 1
Parameter type: round(number)
Return type: Number
Example:
round(1.0) returns 1
round(1.23) returns 1
round(1.78) returns 2
round(2) returns 2
round(field(amount)) returns the value of amount rounded off to its nearest integer, given that, amount is of the type Number.
4. sum( )
Description: Used for adding two numbers
Number of parameters: 2
Parameter type: sum(number, number)
Return type: Number
Example:
sum(9, 10) returns 19
sum(9.1, 10.9) returns 20
sum(10, field(amount)) returns the sum of 10 and the value of amount, given that, amount is of the type Number.
sum(field(total), field(amount)) returns the sum of columns total and amount, given that both of them are of the type Number.
5. subtract( )
Description: Used for subtracting the right number from the left
Number of parameters: 2
Parameter types: subtract(number, number)
Return type: Number
Example:
subtract(19, 10) returns 9
subtract(19.1, 10.9) returns 8.2
subtract(100, field(amount)) returns (100 - amount), given that, amount is of the type Number.
subtract(field(total), field(amount)) returns (total - amount), given that both of them are of the type Number.
6. multiply( )
Description: Used for multiplying two numbers
Number of parameters: 2
Parameter types: multiply(number, number)
Return type: Number
Example:
multiply(19, 10) returns 190
multiply(19.1, 10.9) returns 208.19
multiply(100, field(amount)) returns the product of 100 and the value in amount column, given that, amount is of the type Number.
multiply(field(total), field(amount)) returns the product of total and amount, given that both of them are of the type Number.
7. divide( )
Description: Used for dividing the left number by the right
Number of parameters: 2
Parameter types: divide(number, number)
Return type: Numbers
Example:
divide(290, 10) returns 29
divide(19.1, 10.9) returns 1.752
divide(100, field(amount)) returns (100 / amount), given that, amount is of the type Number.
divide(field(total), field(amount)) returns (total / amount), given that both of them are of the type Number.
8. abs( )
Description: Used for returning the absolute value of a number.
Number of parameters: 1
Parameter type: abs(number)
Return type: Number
Example:
abs(-90) returns 90
abs(-9.1) returns 9.1
abs(field(amount)) returns the absolute value of amount, given that the amount column is of the type Number.
9. avg( )
Description: Used for returning the average of two numbers
Number of parameters: 2
Parameter types: avg(number, number)
Return type: Number
Example:
avg(10, 26) returns 13
avg(-10, 26) returns 8
avg(10, field(amount)) returns the average of 10 and amount, given that the amount column is of the type Number.
10. max( )
Description: Used for returning the maximum of two numbers
Number of parameters: 2
Parameter types: max(number, number)
Return type: Number
Example:
max(10, 16) returns 16
max(10, field(amount)) returns the maximum value among 10 and amount, given that the amount column is of the type Number.
10. min( )
Description: Used for returning the minimum of two numbers
Number of parameters: 2
Parameter types: min(number, number)
Return type: Number
Example:
max(10, 16) returns 10
max(10, field(amount)) returns the minimum value among 10 and amount, given that the amount column is of the type Number.
11. substring( )
Description: Used for returning a part of a string
Number of parameters: Minimum-2, Maximum-3
Parameters: substring(string, starting_index [, no_of_characters])
In a given string, extract x number of characters specified by no_of_characters, beginning at the index specified by starting_index. (The first index of the string starts at 0.)
The 3rd parameter is optional. If it's not given, it returns the string starting at the starting_index till the end end of the string,
Parameter types: substring(string, number, number)
Return type: String
Example:
substring(helloworld, 4, 3) returns "owo"
substring(helloworld, 3) returns "loworld"
12. replace( )
Description: Used for replacing a part of the string with another string
Number of parameters: 3
Parameters: replace(string, target_string, new_string)
In a given string, replace target_string with new_string
Parameter types: replace(string, string, string)
Return type: String
Example:
replace(helloworld, world, universe) returns "hellouniverse"
replace(helloworld, world, field(currency)) returns "helloUSD", given that currency is of type string and has value the USD.
13. split()
Description: Used for splitting a string based on a delimiter and returning an array containing the split values
Number of parameters: 2
Parameters: split(string, delimiter)
Split the given string using the delimiter specified and return an array containing the splits.
Parameter types: split(string, string)
Return type: an array of strings
Example:
split(hello-world, - ) returns [hello, world]
14. upper( )
Description: Used for converting a string to its upper case version
Number of parameters: 1
Parameter type: upper(string)
Return type: String
Example:
upper(helloworld) returns HELLOWORLD
14. lower( )
Description: Used for converting a string to its lower case version
Number of parameters: 1
Parameter type: lower(string)
Return type: String
Example:
lower(HELLOWORLD) returns helloworld
15. trim( )
Description: Used for removing extra whitespace at the start and end of the string.
Number of parameters: 1
Parameter type: trim(string)
Return type: String
Example: trim(" helloworld ") returns "helloworld"
16. power( )
Description: Used for finding the power of a number
Number of parameters: 2
Parameters: power(base, exponent)
Returns the power of the base raised to the exponent.
Parameter types: power(number, number)
Return type: number
Example:
power(2, 3) returns 8
power(field(amount), 2) returns the value of amount raised to 2, given that amount is of the type number.
17. sqroot( )
Description: Used for calculating the square root of a number
Number of parameters: 1
Parameter type: sqroot(number)
Return type: Number
Example:
sqroot(9) returns 3
sqroot(16) returns 4
18. isString( )
Description: Used for checking if the parameter is a string or not
Number of parameters: 1
Parameter types: isString(Any)
Return type: Boolean (True or False)
Example:
isString(43) returns False
isString("Helloworld") returns True
isString(field(amount)) returns False if amount is not of the type String.
19. range( )
Description: Used for getting a range of numbers as a list
Number of parameters: Minimum-1, Maximum-2
Parameter types: range(number [, number] )
Return type: List of numbers
Example:
range(3) returns [1, 2, 3]
range(4, 10) returns 4, 5, 6, 7, 8, 9, 10
20. length( )
Description: Used for finding the length of an array, string, or an object
Number of parameters: 1
Parameter types: length(array/string/object)
Return type: Number
Example:
length("helloworld") returns 10
length( [1, 5, 8, 10] ) returns 4
21. FormatTime( )
Description: Used for converting a DateTime to a specified format
Number of parameters: Minimum-3, Maximum-5
Parameters: FormatTime(Datetime, input_format, output_format , [fallback, timezone] )
Changes the Datetime from the input_format to the output_format.
fallback and timezone are optional parameters.
fallback is the default value returned if Datetime is null.
timezone is given if the output time is required for a specific timezone. By default it is UTC.
Parameter types: FormatTime(string, string, string, string, string)
Return type: string
Example:
FormatTime('2018-09-01', 'YYYY-MM-DD', 'DD-MMM-YYYY') returns 01-Sep-2018
22. coalesce( )
Description: Used for returning the first non-null parameter
Number of parameters: Minimum-2, Maximum-10
Parameter types: Each parameter can be of any type
Return type: Any
Example:
coalesce( Null, { }, [ ], 'george', 'lloyd') returns 'george'