¶ Macros
What is a Macro?
A macro is a built-in formula attached to a field that automatically calculates or sets a value based on other fields in the same or related forms.
- Macros reduce manual entry.
- They help ensure data accuracy and consistency.
- The result is shown instantly after entering relevant data.
Macros are programmable expressions used to dynamically evaluate values based on field data, conditions, and functions. This guide is divided into two sections:
- Admin Users : For configuring and managing macros.
- End Users: For understanding macro behavior and usage during record operations.
¶ Section A: For Admin Users
¶ Macro Configuration:
User Navigate to Collection field layout Configuration > Edit Macro.
Define a macro expression using supported functions and field references.
Example:
=IF(F1.AGE > 18, “Adult”, “Minor”)
User can either save as concept or publish the macro.
Note: Macros saved as concept will not be executed during record operations. Only published macros are evaluated during runtime operations.
Macro Syntax:
Field Reference: F1.FieldName refers to the field in the current context.
Functions: Examples include IF, SUM, DSUM, DLOOKUP, AND, OR, NOT.
Basic Syntax:
Example: ADD(10, 5) = 15
Example: IF(MARK != null, “Yes”, “No”)
String Literals and Quotes:
Use double quotes " for string values:
“Hello”, “Admin”, “Test”
Valid: IF(NAME == “Admin”, “Yes”, “No”)
Invalid: IF(NAME == ‘Admin’, …) Use of single quotes ’ is not supported
Boolean Logic & IF Macro Behavior:
IF macro supports:
Explicit comparison: IF(Condition1 = true, “Yes”, “No”)
Implicit evaluation: IF(Condition1, “Yes”, “No”)
Nested expressions:
IF(AND(Var1 > 10, OR(Var2 < 5, Var3 == 20)), “OK”, “NO”)
Both IF(A, x, y) and IF(A == true, x, y) work when A is a Boolean.
Null Handling:
Use null keyword
IF(MARK != null, MARK, “Fallback”)
Nested usage: IF(MARK != null, MARK, “D/” + FORMAT(YEAR(IF(ITEM_CREATED != null, ITEM_CREATED, DATE())) - 2000, “00”))
Deep nesting of logic or macros is fully supported:
IF(AND(Var1 > 10, OR(Var2 < 5, Var3 == 20)), IF(NOT(Var4), (Var1 + Var2) * Var3, (Var1 - Var2) / Var3), “not match”)
With relation macros like DSUM: DSUM(“NUM1”, IF(MARK != null, “MARK = ‘INKOOP’”, “”))
Supported operators in inline expressions:
Arithmetic: +, -, *, /, %
Logical: ==, !=, >, <, >=, <=
Boolean macros: AND, OR, NOT
Conditions: IS NULL, IS NOT NULL, IN, LIKE, NOTLIKE.
Date Arithmetic:
DATE1 - DATE2
Which returns a Time Span. Example: DATE(2023, 10, 10) - DATE(2023, 09, 01) = “39.00:00:00”
Macro Execution Flow:
-
Macro Triggered :
When a macro-enabled record field is created or updated, the macro engine is invoked. -
Placeholder Substitution :
Field references like F1.MARK or F2.AMOUNT are replaced using values from:
The same collection (current record), or A linked collection (uses the first linked record ordered by CreatedOn). -
Macro Execution Order
If multiple fields are macro-enabled:
Dependencies between fields are resolved using topological sorting.
Execution proceeds from independent to dependent fields.
Cyclic dependencies result in an error.
Example:
F1 = 10
F2 = F1 + 5 = 15
F3 = F2 * 2 = 30 -
Formula Evaluation :
Each macro expression is evaluated using Macro Evaluator. This may invoke built-in macros (e.g., IF, DSUM, FORMAT) or external functions. -
Result Assignment :
The computed value is written to the target field of the record. -
Macro Propagation :
If the updated field is used in another macro-enabled field (even in other collections)
Those dependent macros are re-evaluated automatically. This ensures cascading consistency across the system.
Example: Updating F1 may trigger re-evaluation of F2,which may further trigger recalculation in a related collection’s F3.
¶ DSUM macro
¶ DAVG macro
¶ DCOUNT macro
¶ DLOOKUP macro
¶ ADD macro
¶ SUM macro
¶ DIVIDE macro
¶ CONCAT macro
¶ LIKE macro
¶ JOIN macro
