Advanced Mass Update with an Expression

Use an Advanced mass update permanently change one set of values to another set of values for the same field when you need to permanently update a field with an expression. Doing so can reduce your workload. For example, one Advanced mass update can produce the same results as two queries and two simple mass updates. Expressions also permit updates that just are not available any other way.

An expression is a string that can contain a mix of variable and literal tokens. Variable tokens are enclosed in braces ('{' and '}') and contain a period-delimited string of accessors (accessors are referenced by the Java name, which can be found in the Data Dictionary organizes the tables and fields that house all of your district's administrative information). A variable token might also contain a function and parameters for that function. Literal tokens are any portions of the expression that are not variable tokens. The following is an example of an expression:

{person.lastName}, {person.firstName}

This expression could be parsed into the following list of tokens:

1. VariableToken: "person.lastName"
2. LiteralToken:    ", "
3. VariableToken: "person.firstName"

The final result might be a value like "Doe, John".

Note: Before you perform an advanced mass update, you must select the records you want to work with. If you neglect to do this, you might accidentally mass update all of the records in your database.

Important: It is not possible to reverse a mass update. Proceed with caution.

To use the Advanced Mass Update option:

1. Go to any list page, like the Student List.
2. Select the records you want to mass update by doing one of the following:
   • Select their checkboxes and then from the Options menu, select Show Selected.
   • Use a query.
   • Use a snapshot.

   Note: Be sure to check the record counter at the top of the page to verify that it shows the correct number. Regardless of what records are selected, performing a mass update will always update the total number of records. If the record counter shows "100 of 750 selected," you will update 750 records if you forget to do Options > Show Selected.

3. From the Options menu, click Mass Update. The Mass Update pop-up appears.

4. 

4. Click Advanced. The Mass Update pop-up expands.

5. 

5. Use the Field drop-down to select the field whose value you want to replace.
6. Use the Source drop-down to select An expression.
7. In the Value field, enter an expression of literal and/or variable tokens.
8. Select the Force save checkbox to override the validation of required or restricted fields during the mass update process. If you do not select this checkbox and a record has validation errors, then it will not be saved.
9. Click Update. The system confirms the field and value that you selected.
10. Click OK or Cancel.
11. If you clicked OK, the system confirms the number of records that will be affected and asks if you want to continue. Click OK to complete the mass update or Cancel to cancel the operation.

Note: Some mass update operations can take several minutes to complete if the number of records to be updated is several thousand or more.

Simple Examples

• To populate a new, user-defined field on the Student table so that it holds every student’s full name in the format ”First Middle Last” (for the purposes of running a Quick Report or customizing labels, for example), use the following syntax in the Expression box:

• {person.firstName} {person.middleName} {person.lastName}

• A school has just completed construction on a new wing. All current room numbers need to be prefixed with the letter ’A’ to indicate the A wing:

• A{roomNumber}         (the value for room number is appended to the literal 'A')

Note: An operation like this is best performed using a temporary, user-defined field:

• Enable a user-defined field called New Room in the Data Dictionary on the School Classroom table.
• Mass update the New Room field with the above expression.
• If successful, mass update the Number field using the Another Field option (copying from another field eliminates the risk that is inherent in using expressions).
• Reset the temporary New Room field in the Data Dictionary so it can be used again later.

Variable Token Functions

Variable tokens can also include functions to manipulate the resulting value. Functions are delimited with parentheses followed by a list of arguments. The supported functions are outlined in the following tables:

Note: Variables are case-sensitive; function names are not.

Function	LOWER(fieldName)
Description	This function converts all letters in a string to lowercase.
Parameters	fieldName – the accessor to evaluate
Example	From the Student List, you could use this expression to convert all middle names to lower case: {LOWER(person.middleName)} The name Charles would become charles.

 

Function	UPPER(fieldName)
Description	This function converts all letters in a string to uppercase.
Parameters	fieldName – the accessor to evaluate
Example	From the Student List, you could use this expression to convert all middle names to upper case: {UPPER(person.middleName)} The name Charles would become CHARLES.

 

Function	PROPER(fieldName, allWords)
Description	This function converts the first letter of a word to uppercase and the remaining letters to lowercase.
Parameters	fieldName – the accessor to evaluate allWords – either true or false. If true, then all words in the value will be capitalized. If false, then only the first word will be.
Example	From the Course List, you could use this expression to convert all course names to proper case: {PROPER(description, true)} The course name INTRODUCTION TO PHOTOGRAPHY would become Introduction To Photography. {PROPER(description, false)} The course name INTRODUCTION TO PHOTOGRAPHY would become Introduction to photography.

 

Function	PADL(fieldname, length, ’padCharacter’)
Description	This function adds enough pad characters to the left of the string (a prefix) to reach the specified length. This is typically used for alignment purposes.
Parameters	fieldName – the accessor to evaluate length – the final, padded length of the string padCharacter – the single character used for padding, this value must be wrapped in single quotes (e.g., ' ' or '0')
Example	From the Student List, you could use this expression to right-align grade levels: {PADL(gradeLevel, 2,’0’)} Before: 7 8 9 10   After: 07 08 09 10

 

Function	PADR(fieldname, length, ’
Description	This function adds enough pad characters to the right of the string (a suffix) to reach the specified length. This is typically used for alignment purposes.
Parameters	fieldName – the accessor to evaluate length – the final, padded length of the string padCharacter – the single character used for padding; this value must be wrapped in single quotes (e.g., ' ' or '0')
Example	From the Course List, you could use this expression to align course numbers and descriptions: {PADR(number,5,’ ’)} {description}   Before: 123 Cooking 101 1750 Chemistry 24689 History   After: 123     Cooking 101 1750   Chemistry 24689 History

 

Function	IF(fieldName, operator, ’
Description	This function represents a conditional evaluation. If the condition evaluates to true, then one value is output; otherwise, the other value is output.
Parameters	fieldName – the accessor to evaluate operator – one of the following values: equals equalsIgnoreCase notEqualTo notEqualToIgnoreCase contains beginsWith endsWith isEmpty comparedValue – the test value used for comparisons, this value must be wrapped in single quotes trueResult – the resulting value if the condition evaluates to true falseResult – the resulting value if the condition evaluates to false
Example	From the Student List you could use this expression to set calendar codes:   {IF(gradeLevel,equals,’12’,’Senior’,’Standard’)}

Notes: When the Attendance activity tracking preference is enabled by the district (root organization), intermediate organization or school: If an override reason is required for modifying the records selected for Mass Update, then the Override Reason and Activity Comment fields appear on the Mass Update pop-up. Select an override reason for Aspen to save any edits in Mass Update. If you select Other, enter an Activity Comment to describe the reason. The selected override reason and activity comment will apply to all records that are updated. Example: If a user who does not have the Override archive lock date privilege runs Mass Update, Aspen skips the records with dates prior to the Archive lock date. If the system administrator, whose role has that privilege enabled, runs Advanced Mass Update and selects Force save, Aspen will update all the records, including the archived ones.