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 on), “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'

Did this answer your question?