Function

You can use functions to combine other fields to calculate a result. Function fields are not actually stored, and results are dynamically calculated at runtime.

Description

Item	Content
Category	Control Class
Sortable	Yes
Filterable	Yes
Supported Filter Types	Equal, Not Equal, Empty, Not Empty, Less Than, Greater Than, Less Than or Equal, Greater Than or Equal, In List, Not In List

Field Settings

Setting	Description
Placeholder in form when not filled	Text information displayed in the input box when no information is entered in the field
Function	Set the content of the function
Use expression as function value on create page	Use expression to calculate function value on create page
Display format	Set the display format of date fields Options: Text, Decimal, Currency, Email, URL, Phone Number, QR Code, Barcode, Progress Bar, Image, Markdown, HTML, Trend Chart
Format prefix	Display prefix before function value
Format suffix	Display suffix after function value

Database Storage Format

Not stored in database

Usage Instructions

Get Object Value

If the referenced field is sub-object, attachment, signature, user selection, etc., you can use the ->> expression to get the value in the object. For the storage methods of these types of objects, please refer to Storage Method

user->>'name' //Assuming the identifier of the user selection field is user, get the name of the stored user selection
attachment->>'path' //Assuming the identifier of the attachment field is attachment, get the stored path

Addition

Field1 + Field2, add two numeric fields to sum, direct addition of two strings is not supported.

Notes

• Both items to be added must be numeric type
• Functions cannot reference functions of related record fields

Field1=1.02
Field2=2

Field1 + Field2 = 3.02

Subtraction

Field1 - Field2, subtract two numeric fields to get the difference

Notes

• Both items to be subtracted must be numeric type
• Functions cannot reference functions of related record fields

Field1=1.02
Field2=2

Field1 - Field2 = -0.98

Multiplication

Field1 * Field2, multiply two numeric fields to get the product

Notes

• Both items to be multiplied must be numeric type
• Functions cannot reference functions of related record fields

Field1=1.02
Field2=2

Field1 * Field2 = 2.04

Division

Field1 / Field2, divide two numeric fields to get the value

Notes

• Both items to be divided must be numeric type
• The divisor cannot be 0. If you are not sure whether the divisor is 0, you can use DIVISION.
• If two integers are divided, an integer is returned. If you want to return a decimal, you need to multiply the divisor or dividend by 1.0. If you are not sure about the type of the two numbers, you can use DIVISION.

Field1=1
Field2=2
Field3=1.02

Field1 / Field2 = 0
Field1 * 1.0 / Field2 = 0.51
Field3 / Field2 = 0.51

UPPER

UPPER(input), convert all letters in the input string input to uppercase and output

Notes

Only supports operations on string type fields

Field1='Abcdefg012MNOQ'

UPPER(Field1)='ABCDEFG012MNOQ'

LOWER Convert to Lowercase

LOWER(input), convert all letters in the input string input to lowercase and output

Notes

Only supports operations on string type fields

Field1='Abcdefg012MNOQ'

LOWER(Field1)='abcdefg012mnoq'

CHAR_LENGTH

CHAR_LENGTH(input), calculate the length of the character input

Notes

• Only supports operations on string type fields
• Only counts characters (one Chinese character is also counted as one character)

Field1='Abcdefg012MNOQ'

CHAR_LENGTH(Field1)=14

CONCAT

CONCAT(s1,s2...sn), merge multiple input strings into one string

Notes

Only supports operations on string type fields

Field1='abc'
Field2='ABC'
Field3='123'

CONCAT(Field1,Field2,Field3)='abcABC123'

CONCAT_WS

CONCAT_WS(s,s1,s2...sn), merge multiple input strings into one string according to the separator s

Notes

Only supports operations on string type fields

Field1='abc'
Field2='ABC'
Field3='123'

CONCAT('-',Field1,Field2,Field3) -- 'abc-ABC-123'
CONCAT('/',Field1,Field2,Field3) -- 'abc/ABC/123'

LPAD

LPAD(s1,len,s2), pad the left side of string s1 with string s2 to make the string length reach len

Notes

• Only supports operations on string type fields
• When the padding length is less than the string length, it is truncated from the left to meet the length

Field1='abc'

LPAD(Field1,5,'0')='00abc'
LPAD(Field1,6,'@#')='@#@abc'
LPAD(Field1,2,'0')='ab'

RPAD

RPAD(s1,len,s2), pad the right side of string s1 with string s2 to make the string length reach len

Notes

• Only supports operations on string type fields
• When the padding length is less than the string length, it is truncated from the left to meet the length

Field1='abc'

RPAD(Field1,5,'0')='abc00'
RPAD(Field1,6,'@#')='abc@#@'
RPAD(Field1,2,'0')='ab'

TRIM

TRIM(s), remove all spaces on the left and right of string s

Notes

Only supports operations on string type fields

Field1='abc'
Field2=' abc  '
Field3='abc  '
Field4=' abc'

TRIM(Field1)='abc'
TRIM(Field2)='abc'
TRIM(Field3)='abc'
TRIM({Field4})='abc'

LTRIM

LTRIM(s), remove all spaces on the left of string s

Notes

Only supports operations on string type fields

Field1='abc'
Field2=' abc  '
Field3='abc  '
Field4=' abc'

LTRIM(Field1)='abc'
LTRIM(Field2)='abc  '
LTRIM(Field3)='abc  '
LTRIM({Field4})='abc'

RTRIM

RTRIM(s), remove all spaces on the right of string s

Notes

Only supports operations on string type fields

Field1='abc'
Field2=' abc  '
Field3='abc  '
Field4=' abc'

RTRIM(Field1)='abc'
RTRIM(Field2)=' abc'
RTRIM(Field3)='abc'
RTRIM({Field4})=' abc'

REPEAT

REPEAT(s,n), repeat the string s n times

Notes

Only supports operations on string type fields

Field1='abc'
Field2='efg'

REPEAT(Field1,2)='abcabc'
REPEAT(Field2,5)='efgefgefgefgefg'

REPLACE

REPLACE(s,s1,s2), replace string s1 with string s2 in string s

Notes

• Only supports operations on string type fields
• Characters are case-sensitive, only replace exact matches

Field1='abcefgeacEa'

REPLACE(Field1,'e','F')='abcFfgFacEa'

REVERSE

REVERSE(s), return the reverse of string s

Notes

Only supports operations on string type fields

Field1='abcdefg1'

REVERSE(Field1)='1gfedcba'

SUBSTR

SUBSTR(s, start, length), extract a substring of length from string s starting at position start

Notes

• Only supports operations on string type fields
• Position values start from 1

Field1='abcdefg1'

SUBSTR(Field1,1,3)='abc'

STRPOS

STRPOS(s,s1), return the position where string s1 first appears in s

Notes

• Only supports operations on string type fields
• Position values start from 1

Field1='abcdefg1'

STRPOS(Field1,'a')=1
STRPOS(Field1,'bc')=2

LEFT

LEFT(s,n), return the leftmost n characters of string s

Notes

• Only supports operations on string type fields
• If the specified length exceeds the string length, the entire string is returned

Field1='abcdeafg1'

LEFT(Field1,3)='abc'
LEFT(Field1,20)='abcdeafg1'

RIGHT

RIGHT(s,n), return the rightmost n characters of string s

Notes

• Only supports operations on string type fields
• If the specified length exceeds the string length, the entire string is returned

Field1='abcdeafg1'

RIGHT(Field1,3)='fg1'
RIGHT(Field1,20)='abcdeafg1'

MD5

MD5(s), return the hexadecimal result of the MD5 hash of string s

Notes

Only supports operations on string type fields

Field1='abcdeafg1'

MD5(Field1)='61013906f38b75046a9022263230311c'

CAST

CAST ('s' AS targetType), convert the numeric string s to the target type

Notes

• Only supports operations on string type fields
• Available targetType values: INTEGER (integer type), FLOAT (floating point type), NUMERIC (numeric type), TEXT (text type), BOOLEAN (boolean type)

Field1='12345'
Field2=12345

CAST (Field1 AS NUMERIC)=12345
CAST (Field2 AS TEXT)='12345'

ABS

ABS(x), return the absolute value of x

Notes

Only supports operations on numeric type fields

Field1=10
Field2=-10
Field3=-0

ABS(Field1)=10
ABS(Field2)=10
ABS(Field3)=0

FLOOR

FLOOR(x), return the largest integer less than or equal to x

Notes

Only supports operations on numeric type fields

Field1=10.1
Field2=-10.5
Field3=10.8

FLOOR(Field1)=10
FLOOR(Field2)=-11
FLOOR(Field3)=10

CEIL

CEIL(x), return the smallest integer greater than or equal to x

Notes

Only supports operations on numeric type fields

Field1=10.1
Field2=-10.5
Field3=10.8

CEIL(Field1)=11
CEIL(Field2)=-10
CEIL(Field3)=11

GREATEST

GREATEST(expr1, expr2, expr3, ...), return the maximum value in the list

Notes

Only supports operations on numeric type fields

Field1=10.1
Field2=-10.5
Field3=10.8

GREATEST(Field1,Field2,Field3)=10.8

LEAST

LEAST(expr1, expr2, expr3, ...), return the minimum value in the list

Notes

Only supports operations on numeric type fields

Field1=10.1
Field2=-10.5
Field3=10.8

LEAST(Field1,Field2,Field3)=-10.5

POWER

POWER(x,y), return x raised to the power of y

Notes

Only supports operations on numeric type fields

Field1=2
Field2=-2
Field3=3

POWER(Field1,2)=4
POWER(Field1,3)=8
POWER(Field2,2)=4
POWER(Field2,3)=-8
POWER(Field3,2)=9
POWER(Field3,3)=27

RANDOM

RANDOM(), return a random number between 0 (inclusive) and 1 (exclusive)

RANDOM()=0.3323342
RANDOM()=0.5690213

SQRT

SQRT(x), return the square root of x

Notes

Only supports operations on numeric type fields

Field1=4
Field2=16
Field3=8

SQRT(Field1) = 2
SQRT(Field2) = 4
SQRT(Field3) = 2.82842712474619029

CBRT

CBRT(x), return the cube root of x

Notes

Only supports operations on numeric type fields

Field1=8
Field2=27
Field3=9

CBRT(Field1) = 2
CBRT(Field2) = 3
CBRT(Field3) = 2.08008382305190409

PI

PI(), return the constant value of pi

PI() = 3.14159265358979312

MOD

MOD(x,y), return the remainder when x is divided by y

Notes

Only supports operations on numeric type fields

Field1= 8
Field2= 27
Field3= 9

MOD(Field1,2) = 0
MOD(Field1,3) = 2
MOD(Field2,2) = 1
MOD(Field2,3) = 0
MOD(Field3,2) = 1
MOD(Field3,3) = 0

DIVISION

DIVISION(x,y), return the quotient of x divided by y

Notes

• Only supports operations on numeric type fields
• This function returns a decimal value. Even when both x and y are integers, it returns a decimal
• If the divisor is 0 or null, this function returns 0

Field1= 8
Field2= 27
Field3= 9

DIVISION(Field1,2) = 4.00000
DIVISION(Field1,3) = 2.66666
DIVISION(Field1,16) = 0.5
DIVISION(Field2,2) = 13.500000
DIVISION(Field2,3) = 9.000000
DIVISION(Field3,2) = 4.500000
DIVISION(Field3,3) = 3.000000
DIVISION(Field3,0) = 0

ROUND

ROUND(x,y), return x rounded to y decimal places

Notes

Only supports operations on numeric type fields

Field1=10.125432
Field2=-10.125432
Field3=10.8456

ROUND(Field1,2)=10.13
ROUND(Field1,3)=10.125
ROUND(Field2,2)=-10.13
ROUND(Field2,3)=-10.125
ROUND(Field3,2)=10.85
ROUND(Field3,3)=10.846

SIGN

SIGN(x), return the sign of x (-1, 0, 1)

Notes

Only supports operations on numeric type fields

Field1=10
Field2=-0
Field3=-10

SIGN(Field1)=1
SIGN(Field2)=0
SIGN(Field3)=-1

DATE

DATE(d) + n, calculate the date n days after the start date d

Notes

• Only supports operations on date type fields
• n must be an integer

Field1='2020-11-09'

DATE(Field1) + 2 = '2020-11-11'
DATE(Field1) + 90 = '2021-02-07'

DATE

DATE(d) - n, calculate the date n days before the start date d

Notes

• Only supports operations on date type fields
• n must be an integer

Field1='2020-11-09'

DATE(Field1) - 2 = '2020-11-07'
DATE(Field1) - 90 = '2020-08-11'

CURRENT_DATE

CURRENT_DATE, get the current date

CURRENT_DATE = '2020-11-09'

LOCALTIME

LOCALTIME(0), return the current time, 0 indicates time precision, can pass other values (between 0-6)

LOCALTIME(0) = '16:27:34'
LOCALTIME(3) = '16:27:34.354'

LOCALTIMESTAMP

LOCALTIMESTAMP(0), return the current date and time, 0 indicates time precision, can pass other values (between 0-6)

LOCALTIMESTAMP(0) = '2020-11-09 16:27:34'
LOCALTIMESTAMP(3) = '2020-11-09 16:27:34.354'

DATE(d1) - DATE(d2)

DATE(d1) - DATE(d2), calculate the number of days between dates d1 and d2

Notes

Only supports operations on date type fields

Field1 = '2002-11-09'
Field2 = '2020-11-09'

DATE(Field2) - DATE(Field1) = 6575

TO_CHAR

TO_CHAR(d, f), display date d according to the format specified in expression f.

SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS:MS')

Notes

Only supports operations on date type fields

Format Value	Description
HH	Hour of day (01-12)
HH12	Hour of day (01-12)
HH24	Hour of day (00-23)
MI	Minute (00-59)
SS	Second (00-59)
MS	Millisecond (000-999)
US	Microsecond (000000-999999)
SSSS	Seconds since midnight (0-86399)
AM, am, PM or pm	Meridian indicator (without periods)
A.M., a.m., P.M. or p.m.	Meridian indicator (with periods)
Y,YYY	Year with comma (4 or more digits)
YYYY	Year (4 or more digits)
YYY	Last three digits of year
YY	Last two digits of year
Y	Last digit of year
IYYY	ISO 8601 week-numbering year (4 or more digits)
IYY	Last three digits of ISO 8601 week-numbering year
IY	Last two digits of ISO 8601 week-numbering year
I	Last digit of ISO 8601 week-numbering year
BC, bc, AD or ad	Era indicator (without periods)
B.C., b.c., A.D. or a.d.	Era indicator (with periods)
MONTH	Full uppercase month name (padded with blanks to 9 characters)
Month	Full capitalized month name (padded with blanks to 9 characters)
month	Full lowercase month name (padded with blanks to 9 characters)
MON	Abbreviated uppercase month name (3 characters in English, variable length in localized)
Mon	Abbreviated capitalized month name (3 characters in English, variable length in localized)
mon	Abbreviated lowercase month name (3 characters in English, variable length in localized)
MM	Month number (01-12)
DAY	Full uppercase day name (padded with blanks to 9 characters)
Day	Full capitalized day name (padded with blanks to 9 characters)
day	Full lowercase day name (padded with blanks to 9 characters)
DY	Abbreviated uppercase day name (3 characters in English, variable length in localized)
Dy	Abbreviated capitalized day name (3 characters in English, variable length in localized)
dy	Abbreviated lowercase day name (3 characters in English, variable length in localized)
DDD	Day of year (001-366)
IDDD	Day of ISO 8601 week-numbering year (001-371; day 1 of the year is Monday of the first ISO week)
DD	Day of month (01-31)
D	Day of week, Sunday (1) to Saturday (7)
ID	ISO 8601 day of week, Monday (1) to Sunday (7)
W	Week of month (1-5) (first week starts on the first day of the month)
WW	Week number of year (1-53) (first week starts on the first day of the year)
IW	ISO 8601 week number of year (01 - 53; the first Thursday of the new year is in week 1)
CC	Century (2 digits) (21st century starts on 2001-01-01)
J	Julian day (integer days since noon UTC on November 24, 4714 BC)
Q	Quarter
RM	Uppercase Roman numeral month (I-XII; I is January)
rm	Lowercase Roman numeral month (i-xii; i is January)
TZ	Uppercase timezone abbreviation (only supported in to_char)
tz	Lowercase timezone abbreviation (only supported in to_char)
OF	Timezone offset from UTC (only supported in to_char)

Field1 = '2020-11-09 16:33:30'

TO_CHAR(Field1::timestamp,'YYYY-MM-DD HH24:MI:SS') = '2020-11-09 16:33:30'

DATE_PART

DATE_PART(type, d1), return the value of the specified date part type from date d1

Notes

• Only supports operations on date type fields
• type definition values

  Type Value	Description
  DOW	Day of week, from Sunday (0) to Saturday (6)
  DOY	Day of year (1 - 365/366)
  QUARTER	Quarter of the year (1 - 4)
  YEAR	Year component of the date
  WEEK	Week number of the year
  DAY	Day component of the date
  HOUR	Hour component of the date
  MONTH	Month component of the date (1 - 12)
  MINUTE	Minute component of the date
  SECOND	Second component of the date
  EPOCH	Number of seconds since 1970-01-01 00:00:00 (result may be negative)

Field1 = '2020-11-09 16:33:30'

DATE_PART('DOW',Field1::TIMESTAMP) = 1
DATE_PART('DOY',Field1::TIMESTAMP) = 314
DATE_PART('QUARTER',Field1::TIMESTAMP) = 4
DATE_PART('YEAR',Field1::TIMESTAMP) = 2020
DATE_PART('MONTH',Field1::TIMESTAMP) = 11
DATE_PART('WEEK',Field1::TIMESTAMP) = 46
DATE_PART('HOUR',Field1::TIMESTAMP) = 16
DATE_PART('MINUTE',Field1::TIMESTAMP) = 33
DATE_PART('SECOND',Field1::TIMESTAMP) = 30
DATE_PART('EPOCH',Field1::TIMESTAMP) = 1604939610

Date String Conversion

Operator	Description	Example	Output
DATE(dateString)	Convert date, return date type value of dateString (without time part)	DATE('2020-11-09 16:33:30')	2020-11-09
dateString::DATE	Convert date, return date type value of dateString (without time part)	'2020-11-09 16:33:30'::DATE	2020-11-09
dateString::TIMESTAMP	Convert date and time, return date type value of dateString (with time part)	'2020-11-09 16:33:30'::TIMESTAMP	2020-11-09 16:33:30
dateString::TIME	Convert time, return time type value of dateString (only time part)	'2020-11-09 16:33:30'::TIME	16:33:30
timeString::TIME	Convert time, return time type value of dateString (only time part)	'16:33:30'::TIME	16:33:30

Display Format

Usage Examples

Calculate Birth Date from ID Card

Existing ID card field, type: single-line text, identifier: idCard. Add a birthday field, type: function, identifier: birthday

concat(substr(idCard,7,4),'-',substr(idCard,11,2),'-',substr(idCard,13,2))

ID Card: 110101199003070978
Birthday: 1990-03-07

Calculate Constellation from String Birthday Field

Existing birthday field, type: string, identifier: birthday; add a constellation field, type: function, identifier: constellation

case
    when (birthday is null or char_length(birthday)<10) then ''
    when to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric >= 1222 or to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric <=119
    then 'Capricorn'
    when to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric >= 120 and to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric <=218
    then 'Aquarius'
    when to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric >= 219 and to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric <=320
    then 'Pisces'
    when to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric >= 321 and to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric <=420
    then 'Aries'
    when to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric >= 421 and to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric <=520
    then 'Taurus'
    when to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric >= 521 and to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric <=621
    then 'Gemini'
    when to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric >= 622 and to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric <=722
    then 'Cancer'
    when to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric >= 723 and to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric <=822
    then 'Leo'
    when to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric >= 823 and to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric <=922
    then 'Virgo'
    when to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric >= 923 and to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric <=1022
    then 'Libra'
    when to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric >= 1023 and to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric <=1121
    then 'Scorpio'
    when to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric >= 1121 and to_char(TO_TIMESTAMP(birthday,'yyyy-MM-dd'),'MMDD')::numeric <=1221
    then 'Sagittarius'
end

  Birthday: 1990-03-07
  Constellation: Pisces

Calculate Constellation from Date Birthday Field

Existing birthday field, type: date, identifier: birthday; add a constellation field, type: function, identifier: constellation

case
when to_char(birthday,'MMDD')::numeric >= 1222 or to_char(birthday,'MMDD')::numeric <=119
then 'Capricorn'
when to_char(birthday,'MMDD')::numeric >= 120 and to_char(birthday,'MMDD')::numeric <=218
then 'Aquarius'
when to_char(birthday,'MMDD')::numeric >= 219 and to_char(birthday,'MMDD')::numeric <=320
then 'Pisces'
when to_char(birthday,'MMDD')::numeric >= 321 and to_char(birthday,'MMDD')::numeric <=420
then 'Aries'
when to_char(birthday,'MMDD')::numeric >= 421 and to_char(birthday,'MMDD')::numeric <=520
then 'Taurus'
when to_char(birthday,'MMDD')::numeric >= 521 and to_char(birthday,'MMDD')::numeric <=621
then 'Gemini'
when to_char(birthday,'MMDD')::numeric >= 622 and to_char(birthday,'MMDD')::numeric <=722
then 'Cancer'
when to_char(birthday,'MMDD')::numeric >= 723 and to_char(birthday,'MMDD')::numeric <=822
then 'Leo'
when to_char(birthday,'MMDD')::numeric >= 823 and to_char(birthday,'MMDD')::numeric <=922
then 'Virgo'
when to_char(birthday,'MMDD')::numeric >= 923 and to_char(birthday,'MMDD')::numeric <=1022
then 'Libra'
when to_char(birthday,'MMDD')::numeric >= 1023 and to_char(birthday,'MMDD')::numeric <=1121
then 'Scorpio'
when to_char(birthday,'MMDD')::numeric >= 1121 and to_char(birthday,'MMDD')::numeric <=1221
then 'Sagittarius'
end

  Birthday: 1990-03-07
  Constellation: Pisces