Overview
This article contains links to other App Script articles containing examples covering specific topics, as well as additional examples of App Script use cases and examples. Click on the links in the Table of Contents to jump to that example for more information.
Table of Contents
Updating and Fetching Record Values
- Updating a value on the record that triggered the App Script to run
- Updating a value on the Parent Record
- Updating a value on the Grandparent Record
- Fetching a value from a Parent Record
- Adding a Child Record Linked to a Parent Record
- Creating Multiple Child Records
- Updating a value in a Child Record
- Updating A Field in Every Child Record Linked to the Parent Record
- Print a value on each record in a List
Working with Dates
- Outputting Date Values based on the User's Time Zone
- Adding X Days to the Current Date
- Subtracting X Days from the Current Date
- Setting a Date field value to the first day of the month
Working with Checkboxes
Working with the Manage Users Table
How to Use Loggers
- Logging User Actions
- How to Check for null values before logging
- How to Use Loggers to Check Conditional Logic
- How to Use Loggers to View Results Returned
Working with Strings
- How to Combine Strings using Concatenation
- How to Combine Strings using Interpolation
- How to Return the Length (# of characters and spaces) of a String
- How to Reverse the Output of a String
- How to Split the Output of a String
- How to Convert the Output of a String to Upper Case
- How to Convert the Output of a String to Lower Case
Workings with Lists
- How to Combine Lists
- How to Access Items in a List
- How to Access Specific Portions of a List
- How to Add Items to a List
- How to Remove Items from a List in Bulk
- How to Remove Specific Items from a List
- How to Check if a Value is Contained in a List
- How to Check if a List is Empty
- How to Reverse the Order of Items in a List
- How to Return the Size (# of Items) of a List
Looping through Lists
- Loop Through a List to Log Items In the List
- How to Specify How Long a List Should Run
- How to Name Elements Before Iterating Through Them
- How to Loop Through Records Until Reaching a Specific Value
Using Runtime Exceptions
Simplifying Script Syntax
-
Use a Custom Function to Simplify Checking for Difference in a Value
-
Using the Safe
?
Operator To Check for Null Values and Avoid Errors
Updating and Fetching Record Values
π§ Use Case - Updating a value on the record that triggered the App Script to run
currentValues["Customer"] = "John Smith"
currentValues["Age"] = 123
π§ Use Case - Updating a value on the Parent Record
currentValues["Link to Parent"]["Parent Status"] = "Active"
currentValues["Link to Parent"]["Parent Amount"] = 123
π§ Use Case - Updating a value on the Grandparent Record
currentValues["Link to Parent"]["Link to Grandparent"]["Grandparent Address"] = "123 Main Street"
currentValues["Link to Parent"]["Link to Grandparent"]["Grandparent Zipcode"] = "12345"
π§ Use Case - Fetching a value from a Parent Record
String parentStatus = currentValues["Link to Parent"]["Status"]
Int masterZip = currentValues["Link to Parent"]["Zipcode"]
π§ Use Case - Adding a Child Record Linked to a Parent Record
addChild("Child Table", "Relationship Name", ["name":"John Smith", "Age":42])
π§ Use Case - Creating Multiple Child Records
//Create 5 child records
(1..5).each { index ->
addChild("Child Table", "Relationship Name", ["Child Number": index])
}
π§ Use Case - Updating a value in a Child Record
List<Map> children = getChildren("Child Table", "Relationship Name")
children.each { child ->
if(child["Name"] == "John Smith Jr.") {
child["Age"] = 12
}
}
π§ Use Case - Updating A Field in Every Child Record Linked to the Parent Record
List<Map> children = getChildren("Child Table", "Relationship Name")
children.each { child ->
child["Parent Name"] = "John Smith"
}
π§ Use Case - Print a value on each record in a List
List<Map> children = getChildren("Child Table", "Relationship Name")
children.each { child ->
logger.info(child["Field"].toString())
}
Working with Dates
Refer to the Script Writing - Working with Calendars article for more information.
π§ Use Case - Outputting Date Values based on the User's Time Zone
πNOTE: App Scripts pull Date values in UTC, NOT in the time zone of the user who triggers the script to run. App Scripts run on database-level events and DO NOT have any context of the user who triggered them to run.
For a script to output the date based on the current user's time zone you will need to subtract hours from the date object before stamping the value into the field.
In order to determine how many hours to subtract, refer to the "updated by user" record in the users table, grab the user's time zone, and then subtract the number of hours from UTC.
See the syntax example below:
Date todayUtc = new Date() as Date
String userTimeZone = currentValues["Last User"]["Time Zone"] as String
def dateString = todayUtc.format("yyyy-MM-dd", TimeZone.getTimeZone(userTimeZone))
Date today = new SimpleDateFormat("yyyy-MM-dd").parse(dateString)
Manipulating Date Values
When working with Date values, it is recommended to use a Calendar object.
π§ Use Case - Adding X Days to the Current Date
//Set a Task Due Date field's Value to be 3 Days After The Current Date
//get the current date
def cal = Calendar. getInstance()
//Add 3 days to the current date
cal.add(Calendar.DAY_OF_MONTH, 3)
//get the new Date object
def dueDate = cal.getTime()
//assign the dueDate to the taskEndDate field
currentValues["taskEndDate"] = dueDate
π§ Use Case - Subtracting X Days from the Current Date
SUBTRACT 3 Days from the Current Date instead of ADDING 3 Days
//get the current date
def cal = Calendar. getInstance()
//Add 3 days to the current date
cal.add(Calendar.DAY_OF_MONTH, -3)
//get the new Date object
def dueDate = cal.getTime()
//assign the dueDate to the taskEndDate field
currentValues["taskEndDate"] = dueDate
π§ Use Case - Setting a Date field value to the first day of the month
//Create date variable
Date theDate = currentValues["taskEndDate"] as Date
//Create calendar variable for date manipulation
Calendar calendar = Calendar.getInstance()
//Set calendar date to date variable
calendar.setTime(theDate)
//Add one month to date
calendar.add(Calendar.MONTH, 1)
//Set calendar date to the first day of the month
calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMinimum(Calendar.DAY_OF_MONTH))
//Set date variable equal to the calendar variable
theDate = calendar.getTime()
//Set date field to the new date
currentValues["taskEndDate"] = theDate
Working with Checkboxes
Checkboxes are best handled as a "List" data type.
π§ Use Case - Checking or Unchecking Checkbox Options
There are several other ways to achieve this save thing using different List operators. For more information on Lists and List operators, refer to the Script Writing - Working with Lists article.
Here's an example of removing and adding a selection from a checkbox field:
//Use a list for the checkbox field
List checkbox = currentValues["Checkbox Field"] as List
//Check South as a checkbox option
checkbox.add("South")
//Uncheck East as a checkbox option
checkbox.remove("East")
//Set the new value of the checkbox field
currentValues["Checkbox Field"] = checkbox
Working with the Manage Users Table
π§ Use Case - Set an Application User field on a record
Map user = loadRecord("Account Users", "Email", "johnsmith@email.com");
currentValues["Assigned User"] = user;
π§ Use Case - Set an Application User Group field on a record
πNOTE: User Group fields are Maps
πNOTE: When using the loadRecord()
function with User Groups, the first two inputs of the function will always be "XVIA_USER_GROUP"
, and "group_name"
, and the third input is where you enter the name of whichever User Group you want to assign.
Map group = loadRecord("XVIA_USER_GROUP", "group_name", "Enter Name of User Group Here");
currentValues["Assigned Group"] = group
How to Use Loggers
Refer to the Script Writing - Using Loggers article for more information
π§ Use Case - Logging User Actions
//EXAMPLE - INFO logger used to identify the user who nulled out the "Inactive Date" field's value, and the timestamp of when it happened.
logger.info(currentValues[lastUser]['First Name'].toString() + " " +
currentValues[lastUser]['Last Name'].toString() +
" caused Inactive Date to null out on " + currentValues[updated].toString())
π§ Use Case - How to Check for null values before logging
if (currentValues["Due Date"] != null) {
logger.info("Due Date: " + currentValues["Due Date"])
} else {
logger.info("Due Date is null")
}
π§ Use Case - How to Use Loggers to Check Conditional Logic
if (priority == "High") {
logger.info("High priority condition met")
} else {
logger.info("Condition not met, priority: " + priority)
}
π§ Use Case - How to Use Loggers to View Results Returned
def results = recordSearch.findAllMatchingRecordsByViewId(12345)
logger.info("Found " + results.size() + " records")
for (record in results) {
logger.info("Record Name: " + record["Name"])
}
Working with Strings
Refer to the Script Writing - Working with Strings article for more information.
π§ Use Case - How to Combine Strings using Concatenation
//Combining variables only
String abc = 'abc' as String
String 123 = '123' as String
String newString = abc+123 as String
//Output
//'abc123'
//Combining variables and static text
String firstName = 'Scott' as String
String lastName = 'Carpenter' as String
String greeting = 'Hello my name is ' + firstName + ' ' + lastName as String
//Output
// 'Hello my name is Scott Carpenter'
π§ Use Case - How to Combine Strings using Interpolation
//Combining variables and static
String firstName = 'Scott' as String
String lastName = 'Carpenter' as String
String greeting = "Hello my name is ${firstName} ${lastName}" as String
//Output
//'Hello my name is Scott Carpenter'
π§ Use Case - How to Return the Length (# of characters and spaces) of a String
String helloWorld = 'Hello World' as String
helloWorld.length()
//Output
//11
π§ Use Case - How to Reverse the Output of a String
String helloWorld = 'Hello World' as String
String reversed = helloWorld.reverse() as String
//Output
//dlroW olleH
π§ Use Case - How to Split the Output of a String
String helloWorld = 'Hello World' as String
//Splitting on spaces
List spaceSplit = helloWorld.split(' ') as List
//Output
//[Hello, World]
//Splitting on the letter L
List lSplit = helloWorld.split('l') as List
//Output
//[He, , o Wor, d]
π§ Use Case - How to Convert the Output of a String to Upper Case
String helloWorld = 'Hello World' as String
helloWorld.toUpperCase()
//Output
//HELLO WORLD
π§ Use Case - How to Convert the Output of a String to Lower Case
String helloWorld = 'Hello World' as String
helloWorld.toLowerCase()
//Output
//hello world
Working with Lists
Refer to the Script Writing - Working with Lists article for more information.
π§ Use Case - How to Combine Lists
//Example 1
List sampleList1 = [1, 2, 3] as List
List sampleList2 = [4, 5, 6] as List
List sampleList3 = sampleList1 + sampleList2 as List
//sampleList3 = [1, 2, 3, 4, 5, 6]
//Example 2
List sampleList1 = [1, 2, 3] as List
List sampleList2 = [4, 5, 6] as List
List sampleList4 = sampleList2 + sampleList1 as List
//sampleList4 = [4, 5, 6, 1, 2, 3]
π§ Use Case - How to Access Items in a List
//Usinglist[0]
will return the first value in the List.
//Usinglist[-1]
will yield the last object in the List. For example,
List sampleList = [1, 2, 3]
sampleList[0]
//Returns 1
sampleList[-1]
//Returns 3
π§ Use Case - How to Access Specific Portions of a List
//You can access specific portions of the List using thesubList()
method.
//The start and ending index are provided as parameters in this method.
List sampleList1 = ["Orange", "Green", "Blue", "Purple", "Red", "Yellow"] as List
//Example 1
List sublist1 = sampleList1.sublist(3,4) as List
//subList1 = [Purple]
//Example 2
List sublist2 = sampleList1.sublist(2,3) as List
//sublist2 = [Blue]
//Example 3
List sublist3 = sampleList1.sublist(0,3) as List
//sublist3 = [Orange, Green, Blue]
π§ Use Case - How to Add Items to a List
//Example 1
List sampleList1 = [1, 2, 3] as List
sampleList1 << 4
//sampleList1 = [1, 2, 3, 4]
//Example 2
List sampleList1 = [1, 2, 3] as List
sampleList1.add(4)
//sampleList1 = [1, 2, 3, 4]
// Example 3
List sampleList1 = [1, 2, 3] as List
sampleList1.add(0, 4)
//sampleList1 = [4, 1, 2, 3]
π§ Use Case - How to Remove Items from a List in Bulk
//Example 1
List sampleList1 = [1, 2, 3, 4, 5, 6, 6, 7, 9] as List
List sampleList2 = [4, 5, 6] as List
List sampleList3 = sampleList1 - sampleList2 as List
//sampleList3 = [1, 2, 3, 7, 8, 9]
//Example 2
List sampleList1 = [1, 2, 3, 4, 5, 6, 7, 8, 9] as List
List sampleList2 = [4, 5, 6] as List
List sampleList3 = sampleList1 - sampleList2 as List
//sampleList3 = [1, 2, 3, 7, 8, 9]
π§ Use Case - How to Remove Specific Items from a List
//Example 1
List sampleList1 = [1, 2, 3, 4, 5, 6, 7, 8, 9] as List
List sampleList2 = sampleList1 - 3
//sampleList2 = [1, 2, 4, 5, 6, 7, 8, 9]
//Example 2
List sampleList1 = [1, 2, 3, 4, 5, 6, 7, 8, 9] as List
sampleList1.pop()
//sampleList1 = [1, 2, 3, 4, 5, 6, 7, 8]
//Example 3
List sampleList1 = [1, 2, 3, 4, 5, 6, 7, 8, 9] as List
sampleList1.remove(0)
//sampleList1 = [2, 3, 4, 5, 6, 7, 8, 9]
π§ Use Case - How to Check if a Value is Contained in a List
List sampleList = ["Orange", "Green", "Blue"] as List
sampleList.contains("Orange")
//Returns true
sampleList.contains("Purple")
//Returns false
π§ Use Case - How to Check if a List is Empty
List sampleList1 = ["Orange", "Green", "Blue"] as List
List sampleList2 = []
sampleList1.isEmpty()
//Returns false
sampleList2.isEmpty()
//Returns true
π§ Use Case - How to Reverse the Order of Items in a List
List sampleList1 = ["Orange", "Green", "Blue"] as List
List sampleList2 = sampleList1.reverse() as List
//sampleList2 = [Blue, Green, Orange]
π§ Use Case - How to Return the Size (# of Items) of a List
List sampleList1 = ["Orange", "Green", "Blue"] as List
List sampleList2 = []
sampleList1.size()
//Returns 3
sampleList2.size()
//Returns 0
Looping Through Lists
Refer to the Script Writing - Using Loops with Lists article for more information.
π§ Use Case - Loop Through a List to Log Items In the List
//Example 1 using .each
List colors = ['red', 'green', 'blue'] as List
colors.each { color ->
logger.info("Color: ${color}")
}
//Example 2 using it
List colors = ['red', 'green', 'blue'] as List
colors.each {
logger.info("Color: ${it}")
}
π§ Use Case - How to Specify How Long a List Should Run
for(int i = 0 as int;i<5;i++){
logger.info(i.toString())
}
π§ Use Case - How to Name Elements Before Iterating Through Them
List numbers = [1, 2, 3, 4, 5] as List
for(num in numbers){
logger.info("Number: ${num}")
}
π§ Use Case - How to Loop Through Records Until Reaching a Specific Value
//EXAMPLE - After each loop, thecount
variable increases by 1.
//The last number displayed in the log will be 4 because oncecount
is 5,
//we no longer meet the condition, so the loop stops.
int count = 0 as int
while(count<5){
logger.info(count.toString())
count++
}
Using Runtime Exceptions
You can use Runtime Exceptions in your scripts to enforce extra data validation by throwing a customized error message and not allowing a record to be saved if specified conditions are not met.
π§ Use Case - How to Throw a Runtime Exception if the Link to Parent is Not Set
//EXAMPLE - When creating a new Project Task record, throw a Runtime exception
// if it has not been linked to a Project parent record.
// Script is on a BEFORE_INSERT Event Type
if(currentValues["Project Task"] && !currentValues["Project"]){
throw new RuntimeException("Task must be assigned to a Project before saving")
}
In the example above, if a user attempts to save a new Project Task record but they have not selected the associated Project in the "Project" relationship field, they will receive a Runtime exception error with the customized message instructing them that "Task must be assigned to a Project before saving" (see below).
Simplifying Script Syntax
π§ Use Case - Use a Custom Function to Simplify Checking for Difference in a Value
When your script is repeatedly checking whether a field's value has changed, you can create a custom function using def (see the example below):
def isDifferent(String fieldName) {
return currentValues[fieldName] != previousValues[fieldName]
}
And then whenever you need to check whether the field's value has changed, instead of repeated writing out:
if(currentValues[fieldName] != previousValues[fieldName]){
//Perform some action
}
you can use the shortened syntax shown in the example below:
if(isDifferent(fieldName)){
//Perform some action
}
π§ Use Case - Using the Safe ?
Operator To Check for Null Values and Avoid Errors
- The Safe
?
Operator is used to help avoid receiving a null pointer exception when attempting to access a value (or values) that could be null. Instead of throwing an error, it returnsnull
. - It ensures your scripts continue running even when some data isnβt populated.
In this example, we have a chain of Table relationships linking four tables (see screenshot below):
GOAL: update a record on the "Bottom Child Table" and have the script take the name of the related record on the "Tippy Top Table", reverse it, and stamp that value into the "This Record's TT Name" field.
We can use the safe operator ?
to check for values at each stage of the chain automatically and return null if a link is missing without throwing an error (e.g. after updating the record on the "Bottom Child Table", you can successfully save your changes without receiving an error, but no value is stamped into the "This Record's TT Name" field).
The example script below does NOT use the Safe Operator:
//NO SAFE OPERATOR EXAMPLE - Take the name of the linked record on the "Tippy Top Table",
//reverse it, and stamp it into "Bottom Child" record
currentValues["thisRecordsTippyTopName"] =
(currentValues["linkToMiddleTable"]["linkToTopLevelParentTable"]["linkToTippyTopTable"]["ttName"] as String).reverse()
Using the syntax in the example above, if there is no value in a relationship field linking to a parent record from the Table above it in the chain of relationships, you will receive an error message when attempting to save your changes.
However, if you add the Safe Operator to the script (see example below), the script will not throw an error message:
//SAME SCRIPT, BUT INCLUDING THE SAFE ? OPERATOR
currentValues[thisRecordsTippyTopName] =
(currentValues[linkToMiddleTable][linkToTopLevelParentTable][linkToTippyTopTable][ttName] as String)?.reverse()
Related Articles:
Comments
0 comments
Please sign in to leave a comment.