Bulk Updating Expressions

Knowing how and when to run historic or new expressions when building apps is a very useful tool when looking to maintain the most accurate and up-to-date Records possible.

When to Re-Run Expressions: 5 Scenarios

1. Standard Expression

• If you have changed a pre-existing Expression.
• If you have created a new expression, this could be in a pre-existing Field or a new expression in a newly created Field.

2. Parent Expression (In Child Apps)

• If you have changed a pre-existing Parent Expression.
• If you have created a new Parent Expression, this could be in a pre-existing Field or a new Parent Expression in a newly created Field.

3. Child Expression (In Parent Apps)

• If you have changed a pre-existing Child Expression.
• If you have created a new Parent Expression, this could be in a pre-existing Field or a new Child Expression in a newly created Field.

4. When Adding a Format 

• If you added a format to a field Formatting.
• If you have added new formatting, you will need to update the data for this field within every record. 

5. Making a Field Searchable

• If you had made a field Searchable.
• If you make a field searchable, you will need to update the data in this field for all records in order for the data to be found using the search bar

 

How to Re-Run Expressions: Large vs. Small-Scale

1.) Standard Expressions - A change to one of its Dependent Fields is required in order for the Expression to update the Field's value.

2,3.) Parent & Child Expressions - A change made anywhere within either the Child Record (for Parent Expressions) or Parent Record (for Child Expressions) is required in order for the Expression to update the Field's value.

 

Large Scale

In cases where you need to run or re-run Expressions across a larger number of Records it is far more time-effective to achieve this via an Export and Import. For any type of expression above this can be done with a simple 'Update' Bit Field - the exact method varies slightly however...

Note: When you export the data from your records, make sure you save the full export so that if any mistakes are made, you have a csv with all of the records' data within this. Then, if there are any issues, you can archive the records and then import them as new records to ensure that they are back to how they were before you exported the data.

 

Creating an Update Bit

Once this field is created you can hide it from general view by not putting it in any Templates, it will still be a stored Field against any Record, one that can be edited from an Export and used as described below. 

1.) Standard Expressions - Put an 'if' condition containing the 'Update' Bit in front of the new or changed expression for example: if([Update] = true, new/updated expression, ''). You should put  value of true or false for [Update] depending on whether its current value is true or false, if true than put false in the if condition and vice versa.

Once you export the records you wish to trigger the Expression in, you should change the [Update] value in the .csv file to the opposite of what it currently is. In .csv format true and false need to be typed in capitals. Then upon re-importing you should find the expression changes have ran. You then need to update the expression back to the original otherwise it won't trigger in the future

Tip - Always keep a copy of the original Export, this can be used to re-import and revert back to before if when making any changes the data is accidentally mis-managed.

2.) Parent & Child Expressions - Simply follow steps outline for 1.) Standard Expressions without the need for an if condition. This is because only any change to a Record is needed, and not one specific to a Dependent Field for these type of Expression changes to run.

 

Scheduled Workflow node:

Another method for re-running expressions is through using workflow. This can be done through a scheduled node that can be run immediately to update fields. There are two actions for the workflow that can be used - update field value and recalculate expressions.

 

This is a trigger for workflow that can be set to a specific date and time or can be run immediately. Select the "scheduled job" trigger from the workflow as shown here:

This will open a box to edit the node so that you can input the trigger date and time - if you want to re-run the expression immediately, this date and time does not matter. Select the user that will be used for this workflow - ensure that it is the a user who has access to all the records that you want to re-run expressions for.

For more information on workflow triggers, see this article - https://support.softools.net/hc/en-gb/articles/115001411211-Triggers

 

This is the start of either workflow. We recommend using a filter if there are a lot of records and you do not want to re-run expressions for all of these - for example, if there is a status field, you could use a filter for all open/live records and not for those that are closed to reduce the number of records used in the workflow.

 

Update field value

This action for workflow can be used to target a specific field. This means that you can select the field that contains the expression you want to re-run.

Select the update field value action

This will open a box for you to input the details. Here you will need to select the target field and then input the expression like so - I have used a date now expression for this example:

Note: The field cannot be selected in this action if the expression is within the field. You would need to remove the expression from the field, select it in the workflow and then add it into the expression section.

 

For more information on this action node, see this support article - https://support.softools.net/hc/en-gb/articles/115001427311-Update-Actions

 

Recalculate expressions

This action for workflow can be used to re-run all expressions within the records. This means that any expressions within the record will be run and the values updated.

Select the recalculate expressions action

This will open a box but there are no details/information that you need to select - you just need to press OK.

 

Note: As this action re-runs every expression within the record, this workflow may not be suitable for records with lots of expressions as it may slow down the workflow.

 

For more information on this action node, see this support article - https://support.softools.net/hc/en-gb/articles/115001427311-Update-Actions

 

After you have selected the desired action, save the workflow and publish the app. When you have done this, you can go back into the workflow in App studio and run the expression immediately, like shown:

This will run the workflow and the expressions.

 

Small Scale

If you're working with a very limited number of records in an App then you can trigger the Expressions in each individual Record by meeting the Standard or Parent & Child Expressions criteria above. Editing Fields directly on a Table Report is another way to achieve this, or you can make use of the In-Line Edit Feature of a List Report to again trigger Expression runs and re-runs.

^In-Line Edit (List)^

^Table Report^