Expression Builder in SnapApp
on 07-19-2024 12:00 AM by SnapApp by BlueVector AI
404
An expression builder is a user interface tool that allows users to create and manage expressions, often in the form of formulas, queries, or logical statements, without needing to write code manually.
It typically provides a graphical interface with a set of predefined functions, operators, enabling users to construct complex expressions through a series of selections and inputs.
In SnapApp expression builder can be accessed through the Expression Builder link.
An expression is a combination of symbols, variables, operators, and functions that represents a value or a computation in programming, mathematics, or logic.
Function Signature
Function Signature for every SnapApp functions are provided which is basically the general information about a function like the name, scope and parameters.
Types of Expressions
- Any
| Name | Description | Examples |
|---|---|---|
ANY(["<Value 1>", "<value 2>", "<value n>"]) |
Returns a random value from the list. | ANY([“a”, “b”, “c”]) Returns: a random value - a, b, or c |
GET([<index>) |
Returns a value from a dictionary or a list | GET([1,7,8],2) Returns: 7 |
IF(<Condition>, “Yes”, “No”) |
Returns “Yes” value if a condition is true, and another value if it is false. | IF(True, “Yes”, “No”) Returns: Yes |
IFS(<Condition>, “Yes”, “No”) |
Returns one value if a condition is true, and another value if it is false for multiple conditions | IFS(True, “Yes”, False, “No”) Returns: Yes |
LOOKUP(<Value>, object, field, return-column) |
Returns a single value from a table if found. | LOOKUP(“admin@example.com”, “users”, “email”, “name”) Returns: List |
- Date
| Name | Description | Examples |
|---|---|---|
DATE(<value>) |
Extract and return the date from the <value> in %Y-%m-%d format |
DATE(“2020-01-01”) Returns: 2020-01-01 DATE(“2020-01-01 00:00:00”) Returns: 2020-01-01 |
DATEADD(date, <value>) |
Add number of days to the specified date specified |
DATEADD(“2020-01-01”, 1) Returns: 2020-01-02 DATEADD(“2020-01-01”, -1) Returns: 2019-12-31 |
EOMONTH(<value>) |
Returns the date of the last day of the month | EOMONTH(“2020-01-01”) Returns: 2020-01-31 |
EOWEEK(<value>) |
Returns the date of the last day of the week | EOWEEK(“2020-01-01”) Returns: 2020-01-05 |
| TODAY() | Returns the current date in %Y-%m-%d format. The timezone is optional with a default of UTC. | TODAY() Returns: 2020-01-01 |
- DateTime
| Name | Description | Examples |
|---|---|---|
| NOW() | Return the date and time in %Y-%m-%d %H:%M:%S format. The timezone is optional with a default of UTC. | NOW() Returns: 2020-12-31 12:34:56 |
| UTCNOW() | Return the date and time for UTC time zone in %Y-%m-%d %H:%M:%S format | UTCNOW() Returns: 2020-01-01 12:34:56 |
| Name | Description | Examples |
|---|---|---|
| USEREMAIL() | Returns the current user’s email | USEREMAIL() Returns: email |
- File
| Name | Description | Examples |
|---|---|---|
| STATIC_MAP (latitude, longitude, zoom, width, height, map_type) | Returns a static map image from Google Maps. Map_type can be one of the following: roadmap, satellite, terrain, hybrid. | STATIC_MAP(40.714224, -73.961452, 12, 400, 400) Returns: signed URL STATIC_MAP(ADDRESS_TO_LATLNG(“1616 Federal Blvd, Denver, CO 80204, USA”), None, 12, 400, 400) Returns: File |
| STREET_VIEW (latitude, longitude, heading, pitch, fov, width, height) | Returns a street view image from Google Maps | STREET_VIEW(40.714224, -73.961452, 90, 0, 90, 400, 400) Returns: signed URL |
| TEXT_TO_SPEECH(“text”, “voice gender”) | Returns the voice. | TEXT_TO_SPEECH(“Hello World”, “FEMALE”) Returns: signed URL |
| URL_TO_PDF(url) | Returns a PDF file from a URL. | URL_TO_PDF(“https://example.com”) Returns: URL |
- Image
| Name | Description | Examples |
|---|---|---|
GENERATEIMAGE(<prompt>, <size>, <number-of-images>) |
Returns a generated images based on the <prompt> |
GENERATE_IMAGE(“Cute Dog playing football “,”512x512”,1) Returns: Image |
- JSON
| Name | Description | Examples |
|---|---|---|
KG_SEARCH(<value>, [limit=10]) |
Returns a list of entities from Google’s Knowledge Graph | KG_SEARCH(“Barack Obama”) Returns: [{“name”: “Barack Obama”, “type”: “Person”, “description”: “44th U.S. President”, “url”: “https://en.wikipedia.org/wiki/Barack_Obama”}] |
- List
| Name | Description | Examples |
|---|---|---|
| EXTRACTDATES(‘date 1’, ‘date 2’) | Returns a list of dates from a string | EXTRACTDATES(“2020-01-01, 2020-01-03”) Returns: [‘2020-01-01’, ‘2020-01-03’] |
| EXTRACTEMAILS( ‘mail 1’, ‘mail 2’) | Returns a list of emails from a string | EXTRACTEMAILS(“admin@example.com hello@example.com”) Returns: [‘admin.example.com’, ‘hello@example.com’] |
| EXTRACTNUMBERS(“number1 number2 number3”) | Returns a list of numbers from a string | EXTRACTNUMBERS(“1 2 3”) Returns: [1, 2, 3] |
| EXTRACTPHONES(Phone number) | Returns a list of phone numbers from a string | EXTRACTPHONES(“123-456-7890”) Returns: [‘123-456-7890’] |
| EXTRACTURLS(url1, url2) | Returns a list of URLs from a string | EXTRACTURLS(“https://www.google.com https://www.example.com”) Returns: [‘https://www.google.com’, ‘https://www.example.com’] |
| LIST(value1, value2, value3) | Returns a list of values | LIST(“a”, “b”, “c”) Returns: [‘a’, ‘b’, ‘c’] |
| RE_FINDALL(“([range-of-values]+)”, string) | Returns a list of all matches of a regular expression | RE_FINDALL(“([a-z]+)”, “abc123def456”) Returns: [‘abc’, ‘def’] |
| SELECT() | Returns a list of objects from DB table. Optional second parameter to specify fields. Optional third and foruth parameter to specify filter column and filter value. Optional fifth parameter to specify whether to flatten the list.” filters:- Optional parameter to apply multiple filters. It should be a list of tuples. Each tuple should have 2 elements. First element should be the column name and second element should be the value. | SELECT(“table”, “*”, filters=[(“column1”, 10), (“column2”, 20)]) Returns: [(record1), (record2), …] |
| SORT([list-of-values]) | Returns a list of values sorted in ascending order. Optional second boolean parameter to sort in descending order. | SORT([3, 2, 1]) Returns: [1, 2, 3] |
| SPLIT(“string”, ” “) | Returns a list of substrings from a text string, separated by a delimiter. | SPLIT(“Hello World”, ” “) Returns: [‘Hello’, ‘World’] |
| TOP([list-of-values], count) | Returns the first n items in a list | TOP([1, 2, 3], 2) Returns: [1, 2] |
| UNIQUE([list-of-values]) | Returns a list of unique values from a list | UNIQUE([1, 2, 3, 1, 2, 3]) Returns: [1, 2, 3] |
- Number
| Name | Description | Examples |
|---|---|---|
| ABS(- number) | Arithmetic absolute value. If the value is a string, it will be converted to a number. | ABS(“-3.14”) Returns: 3.14 |
| AVERAGE([number 1, number 2, number 3]) | Returns mean of a list | AVERAGE([1, 2, 3]) Returns: 2 |
| CEILING(floating number) | Returns the smallest integer greater than or equal to a number. | CEILING(3.14) Returns: 4 CEILING(-3.14) Returns: -3 |
| COUNT([value 1, value 2, value 3]) | Returns the number of items in an iterable. If the argument is a table name, it will return the number of rows. | COUNT([1, 2, 3]) Returns: 3 |
| DATEDIFF() | Returns the number of days between two dates. | DATEDIFF(“2020-01-01”, “2020-12-31”) Returns: 365 |
| DAY(date) | Returns the day of the month | DAY(“2020-01-01”) Returns: 1 |
| FIND(“word”, “string”) | Returns the position of a substring. Returns -1 if not found. | FIND(“needle”, “needle in haystack”) Returns: 0 |
| FLOAT(number) | Converts a value to float | FLOAT(3) Returns: 3.0 |
| FLOOR(float number) | Returns the largest integer less than or equal to a number | FLOOR(1.1) Returns: 1 |
| HOUR(date,time) | Returns the hour | HOUR(“2020-01-01 12:00:00”) Returns: 12 |
| INDEX([list of numbers, 1]) | Returns the value at a specific position in a list. Indexes start at 1. | INDEX([“a”, “b”, “c”], 2) Returns: b |
| INT(number) | Converts a value to an integer. | INT(2.718) Returns: 2 |
| LEN(string) | Return the string length | LEN(“abc”) Returns: 3 |
| MAX([list of numbers]) | Returns the largest value in a list of numbers | MAX([3.14, 2.71]) Returns: 3.14 |
| MAXROW() | Returns the largest row number in a table | MAXROW(“table”, “field”) Returns: 3 |
| MIN([list of numbers]) | Returns the smallest value in a list of numbers | MIN([3.14, 2.71]) Returns: 2.71 |
| MINROW() | Returns the smallest row number in a table | MINROW(“table”, “field”) Returns: 1 |
| MINUTE(date, time) | Returns the minute from the time. | MINUTE(“2020-01-01 12:34:56”) Returns: 34 |
| MONTH(date, time) | Returns the month from the date | MONTH(“2020-01-01 12:34:56”) Returns: 1 |
| NLP_SENTIMENT(text) | Returns a sentiment score from -1 to 1 on the basis of text | NLP_SENTIMENT(“I love this product”) Returns: 0.9 NLP_SENTIMENT(“I hate this product”) Returns: -0.9 |
| POWER(number, power) | Returns the result of a number raised to a power | POWER(2, 3) Returns: 8 |
| RANDBETWEEN(range) | Returns a random number between the two numbers | RANDBETWEEN(1, 10) Returns: 5 |
| ROUND(float number, decimal-places) | Returns a number rounded to a specified number of decimal places | ROUND(3.14159, 3) Returns: 3.142 |
| SECOND(date, time) | Returns the second from the time | SECOND(“2020-01-01 12:34:56”) Returns: 56 |
| SQRT(number) | Returns the square root of a number | SQRT(4) Returns: 2 |
| SUM([number1, number2, number3]) | Returns the sum of a list of numbers | SUM([1.1, 2.2, 3.3]) Returns: 6.6 |
| TOTALHOURS(time) | Returns the total number of hours in a time | TOTALHOURS(“12:34:56”) Returns: 12.582222222222223 |
| TOTALMINUTES(time) | Returns the total number of minutes in a time | TOTALMINUTES(“12:34:56”) Returns: 754.9333333333333 |
| TOTALSECONDS(time) | Returns the total number of seconds in a time | TOTALSECONDS(“12:34:56”) Returns: 45296.0 |
| WEEKDAY(date) | Returns the day of the week from a date | WEEKDAY(“2020-01-01”) Returns: 1 |
| YEAR(date) | Returns the year from the date | YEAR(“2020-01-01”) Returns: 2020 |
- Point
| Name | Description | Examples |
|---|---|---|
ADDRESS_TO_LATLNG(<address>) |
Converts an <address> to latitude and longitude. A point is a tuple with 2 values: latitude and longitude. |
ADDRESS_TO_LATLNG(“1600 Amphitheatre Parkway, Mountain View, CA 94043”) Returns: (37.4223878,-122.0841877) |
- Text
| Name | Description | Examples |
|---|---|---|
| CONCAT(“String 1” + “String 2”) | Concatenates two or more text strings | CONCAT(“Hello”, ” “, “World”, “!”) Returns: Hello World! |
| CONCAT_WS(“String 1” + “String 2”) | Concatenates two or more text strings with a separator | CONCAT_WS(” “, “Hello”, “World”, “!”) Returns: Hello World ! |
| CONCATENATE(“String 1” + “String 2”) | Concatenates two or more text strings | CONCATENATE(“Hello”, ” “, “World”, “!”) Returns: Hello World! |
| CONTEXT(application) | Returns current context | CONTEXT(‘view’) Returns: my-view |
| DLP_SSN(SSN number) | Returns a de-identified SSN | DLP_SSN(“123-45-6789”) Returns: 123-XX-XXXX |
| FORMAT_NUMBER(“number”, “{:format}”) | Returns a formatted number | FORMAT_NUMBER(1234567.89, “{:10.2f}”) Returns: 1,234,567.89 |
| ENCODEURL(URL) | Returns a URL encoded string | ENCODEURL(“https://www.google.com/search?q=hello world”) Returns: https%3A%2F%2Fwww.google.com%2Fsearch%3Fq%3Dhello+world |
| GENERATE_SIGNED_URL(url, [expiration]) | Returns a signed URL from GCS. | GENERATE_SIGNED_URL(“bucket-name/example.png”) Returns: signed URL |
| GENERATE_TEXT(prompt, context) | Returns a generated text based on the input prompt | GENERATE_TEXT(“Give me a name for a blog website”) Returns: Blogger’s Home |
| GENERATE_TEXT_WITH_IMAGE(image, query) | Returns a generated text with an image prompt and text prompt | GENERATE_TEXT_WITH_IMAGE(“image.jpg”,”Describe the image”) Returns: Text |
| IMAGE(URL) | Returns a signed URL from GCS. | IMAGE(“bucket-name/example.png”) Returns: signed URL |
| FORMAT_TIME(Value,Military Time, Ignore Seconds, Default timezone(‘optional)) | Returns Time based on the input | FORMAT_TIME(“05:44:18 AM”,False,True,”Pacific/Saipan”)”, “returns”: “03:44 PM” Returns: 1. AppSheet 2. Bubble |
| FORMAT_DATETIME(value, format,default_timezone[optional],ignore_timezone[optional]) | Returns Datetime based on the input | FORMAT_DATETIME(Sat, 15 Jun 2024 00:00:00 GMT,”%Y-%m-%d %H:%M:%S”) Returns: 2024-05-20 05:07:51 |
| LATLNG_TO_ADDRESS(latitude, longitude) | Returns an address from a latitude and longitude. | LATLNG_TO_ADDRESS(40.714224, -73.961452) Returns: 277 Bedford Ave, Brooklyn, NY 11211, USA |
| LEFT(“string”, index) | Returns the leftmost characters in a text string | LEFT(“abc”, 2) Returns: ab |
| LOWER(“string with uppercase”) | Lowercase a string | LOWER(“aBc”) Returns: abc |
| MARKDOWN_TO_HTML(“markdown code”) | Converts Markdown to HTML | MARKDOWN_TO_HTML(“# Hello”) Returns: Hello |
| MID(arg, start, length) | Returns a specific number of characters from a text string starting at the position you specify. | MID(“abc”, 1, 2) Returns: ab |
| PREDICT(model, input) | Returns a prediction from a model. | PREDICT(“my-model”, {“sepal_length”: 5.1, “sepal_width”: 3.5, “petal_length”: 1.4, “petal_width”: 0.2}) Returns: setosa |
| RE_FIND(“([range]+)”, “string”) | Returns the first match of a regular expression | RE_FIND(“([0-9]+)”, “abc123def456”) Returns: 123 |
| RE_MATCH(“([range]+)”, “string”) | Returns the first match of a regular expression at the beginning of a string | RE_MATCH(“([0-9]+)”, “123def456”) Returns: 123 |
| RE_SEARCH(“([range]+)”, “string”) | Returns the first match of a regular expression anywhere in a string | RE_SEARCH(“([a-z]+)”, “abc123def456”) Returns: abc |
| RE_SUB(“([range]+)”,”replaced_string”, “string”) | Returns a string with all matches of a regular expression replaced | RE_SUB(“([a-z]+)”, “X”, “abc123def456”) Returns: X123X456 |
| REF_LABEL() | Get Ref Label of record | REF_LABEL(“permits”, “1745c746-aaa5-4ecf-96e7-49392458d42c”) Returns: ON-0017 |
| RIGHT(“string”, index) | Returns the rightmost characters from a string | RIGHT(“abc”, 1) Returns: c |
| SPEECH_TO_TEXT(object) | Returns a text string from a GCS object. | SPEECH_TO_TEXT(“example.mp3”) Returns: Hello World |
| SUBSTITUTE(“string”, “substring_to_be_replaced”, “substring_used_for_replace”) | Returns a text string with all occurrences of a substring replaced with another substring | SUBSTITUTE(“Hello World”, “World”, “Universe”) Returns: Hello Universe |
| SUMMARIZE_DOCUMENT(url) | Returns a summarized text of the input document. Supports PDF files. | SUMMARIZE_DOCUMENT(url = pdf_url) Returns: Summarized text of the pdf |
| TEXT(number) | Returns a text string representation of a value | TEXT(123) Returns: 123 |
| TITLE(“String”) | Returns a text string in title case | TITLE(“hello world”) Returns: Hello World |
| TRANSLATE_DOCUMENT(“URL”, “source_language”, “target_language”) | Returns a translated document from a GCS URL. | TRANSLATE_DOCUMENT(“gs://bucket-name/file-name”, “en”, “es”) Returns: GCS URL |
| TRANSLATE_TEXT(text, [source_language=’auto’, target_language=’en’]) | Returns a translated string. Similar to the GOOGLETRANSLATE() function in Google Sheets. | TRANSLATE_TEXT(“Hola Amigo”) Returns: Hi friend |
| TRIM(“String”) | Returns a text string with whitespace removed from the start and end | TRIM(” Hello World “) Returns: Hello World |
| UPPER(“String”) | Uppercase a string | UPPER(“Hello World”) Returns: HELLO WORLD |
| USERLOCALE() | The active locale e.g. en, de, es, zh | USERLOCALE() Returns: en, de, es, fr, or zh |
| USERNAME() | Returns the current user’s fullname | USERNAME() Returns: Full Name |
- Time
| Name | Description | Examples |
|---|---|---|
TIME(<datetime>) |
Returns the time portion of a <datetime> |
TIME(“2020-01-01 12:34:56”) Returns: 12:34:56 |
| TIMENOW() | Returns the current time | TIMENOW() Returns: 12:34:56 |
- Tuple
| Name | Description | Examples |
|---|---|---|
| REF_ROWS() | Define related table and column | REF_ROWS(“users”, “claim_id”) Returns: (‘users’, ‘claim_id’) |
- UUID
| Name | Description | Examples |
|---|---|---|
| ACCOUNTID() | Returns current account ID | ACCOUNTID() Returns: UUID |
| CURRENTAPP() | Returns ID of the active application | CURRENTAPP() Returns: UUID |
| USERID() | Returns the current user’s ID | USERID() Returns: UUID |
| USERROLE() | Returns the current user’s role ID | USERROLE() Returns: UUID |
- YesNo
| Name | Description | Examples |
|---|---|---|
| AND(YesNo value, YesNo value) | Returns a YesNo expression | AND(TRUE, FALSE) Returns: No |
| BOOLEAN(value) or BOOL(value) | Converts a value to a boolean | BOOLEAN(“false”) Returns: No BOOLEAN(1) Returns: Yes |
| CONTAINS(“String”, “Any SubString”) | Returns a YesNo expression | CONTAINS(“Hello World”, “World”) Returns: Yes |
| ENDSWITH(“String”, “End substring”) | Returns a YesNo expression | ENDSWITH(“Hello World”, “World”) Returns: Yes |
| EXISTS() | Returns a YesNo expression on the basis of existence | EXISTS(“users”, “id”, 1) Returns: Yes |
| IN(“value”, [list of values]) | Returns a YesNo expression | IN(“a”, [“a”, “b”, “c”]) Returns: Yes |
| ISBLANK(“String or Null”) | Returns a YesNo expression | ISBLANK(“”) Returns: Yes |
| ISDATE(“Date or null”) | Returns a YesNo expression | ISDATE(“2020-01-01”) Returns: Yes |
| ISNOTBLANK(“String or Null”) | Returns a YesNo expression, just the opposite of ISBLANK() | ISNOTBLANK(“”) Returns: No |
| ISNULL() | Returns a YesNo expression, if the value is null or None | ISNULL(1) Returns: No |
| ISNUMBER(number) | Returns a YesNo expression | ISNUMBER(1) Returns: Yes |
| NOT(value) | Returns a YesNo expression | NOT(True) Returns: No |
| OR(value1, value2) | Returns a YesNo expression | OR(True, False) Returns: Yes |
| STARTSWITH(“String”, “Start substring”) | Returns a YesNo expression | STARTSWITH(“Hello World”, “Hello”) Returns: Yes |
Thank you for following these steps to configure your SnapApp components effectively If you have any questions or need further assistance, please don’t hesitate to reach out to our support team. We’re here to help you make the most out of your SnapApp experience.
For support, email us at snapapp@bluevector.ai
Generate Text
