To learn more about workflow rules in general, see Workflow Introduction.

Workflow Rules are Powerful

You can create a workflow rule that is triggered when an Add, Update, or Delete arrives at the server. You can use a workflow rule to do many things:

  1. Create and send an email, SMS, or notification regarding the modified record.
  2. Further update the modified record. 
  3. Update the parent record of the modified record.
  4. Add, update, or delete child records belonging to the modified record.
  5. Invoke a webhook to invoke an external web service.
  6. You can do a wide variety of other things because once the workflow rule is triggered, it is capable of working on any table and doing almost anything.

The "change" that triggers the workflow rule can be any sort of data change that arrives at the server. Users sometimes add an extra table column whose only purpose is to trigger a workflow rule. You could then create a client action button that updates the extra column's value. A click of this action button would then trigger your workflow rule. See Sending Email from an Action Button below for more details.

You could even add an extra table having a single row and a single column whose only purpose is to trigger a workflow rule. You could then create a client action button that updates the column's value in that table. A click of this action button would then trigger your workflow rule.

Alternatively, you can use a report that is triggered on a schedule you specify. Just like a change triggered workflow rule, a report is capable of working on any table and doing almost anything.

Workflow Rules are Triggered

You can create workflow rules that are triggered when data is added, updated, or deleted. For example, you can trigger a workflow rule when:

  • A new row is added.
  • A existing row is updated. For example, the update may occur as the result of editing a row in a form view, or performing a quick edit, or invoking an client action through a user button click.
  • An existing row is deleted.

Your workflow rule is triggered on the AppSheet server when:

  1.  A user does an add, update, or delete through the AppSheet client and the change is synced to the server. 
  2. A data-change action is invoked on the AppSheet client and this change is synced to the server.
  3. An add, update, or delete is performed using the AppSheet API.

Once the change reached the server:

  1. The server makes the appropriate changes to the spreadsheet or database.
  2. The server ensures that any formulas in the spreadsheet are re-computed. This ensures that the workflow rule will have access to the latest data values. 
  3. The server checks whether your workflow rule should be triggered.

Your workflow rule is triggered when the table you specify in the workflow rule is changed.

You can further restrict when a workflow rule is triggered by specifying the type of changes that trigger the workflow rule. For example, you could trigger a workflow rule:

  • Only when a row is added
  • Only when a row is updated
  • Only when a row is deleted
  • Only when a row is added or updated
  • When a row is added, updated, or deleted

You can still further restrict when a workflow rule is triggered by specifying a condition for the workflow rule. For example. you could trigger a workflow rule when:

  • A new row is added to the Inspections table, and the value in the InspectionPassed column is FALSE.
  • A row is updated in the Orders table, the value in the OrderAmount column is over $500, and the value in the DeliveryDate column is within 10 days of the current date.

Additionally, some users may have limited access to table data for security reasons governed by security filters on the table, or nobody may be able to access the table and it is add only.  In some cases, though, we still want to be able to look at the whole table when checking the condition for whether a workflow rule should be run.  For this case, we have included the ability to toggle a security bypass to allow you to engage these tables as well.

Workflow Rules are Not Triggered

Workflow rules are not triggered by:

  1. Changes made directly to the spreadsheet or database.
  2. Data-change actions invoked by a workflow rule.
  3. Changes made through another AppSheet application, even if that application is using the same underlying spreadsheet or database table.

Changes made directly to the spreadsheet or database

Changes you make directly to the spreadsheet or database do not go through the AppSheet server, so they do not trigger AppSheet workflow rules. For example, if you update a Google Sheet directly, Google Sheets does not notify the AppSheet server about the change. Since the AppSheet server is unaware of this change, it does not trigger any AppSheet workflow rules.

Data-change actions invoked by a workflow rule

Data-change actions that are invoked by a workflow rules do not trigger AppSheet workflow rules. For example, if a client device performs an Add, Update, or Delete that triggers a workflow rule, and that workflow rule invokes a data-change action, that data-change action will not invoke any workflow rules.

Changes made through another AppSheet application

Changes made through one AppSheet app will not trigger AppSheet workflow rules defined in another AppSheet app. This is true even if the two AppSheet apps share the same underlying spreadsheet or database table. For example, you might have a Payroll app and a separate Personnel app that share an Employees table. Workflow rules defined in the Payroll app will only be triggered by adds, updates, or deletes to the Employees table that are performed through the Payroll app. Adds, updates, or deletes to the Employees table that are performed through the Personnel app will not trigger workflow rules defined in the Payroll app.

In short, workflow rules only work within a single AppSheet app; they do not work across two or more apps, even if those apps share common underlying spreadsheet or database tables.

Application Version and Workflow Rules

Each time you change your application we create a new version of your application. These changes may include changes to your workflow rules.

We include the application version each time a user submits their adds, updates, or deletes to the server. This is essential because the user may be submitting changes based on a older version of your application. 

When we invoke your workflow rules, we invoke the version of the workflow rules that match the user's version. This may result in invoking an older version of your workflow rule. You can detect when this occurs by examining the Audit History. The Audit History includes the application version number.

Once the user has submitted their changes, the client will perform a sync that retrieves the latest data and application version. Future changes will be submitted using that application version.

Example Workflow Rules

The following examples illustrate some of the ways you can use change triggered workflow rules. Sample app ClickToSendEmail contains many of these examples.

Sending Email When a Row is Added, Updated, or Deleted

You can create a workflow rule that sends a email when a row is added, updated, or deleted. For example, you might send an email each time an Order is added or updated. 

The ServiceRequests table and the On Add in ServiceRequests workflow rule illustrate how to send an email each time a new Service Request is created. They work as follows:

  1. The ServiceRequests table contains the customer's name in column Name, the customer's email address in column Email, and an Enum value that indicates whether the service request is Pending, Active, or Completed.
  2. The workflow rule On Add in ServiceRequests is triggered on the AppSheet backend service each time a new row is added to the ServiceRequests table. It generates an email and sends it to the email address contains in column Email.

Sending Email When a Row is Updated to Have a Specific Column Value

You can create a workflow rule that sends an email when a row is updated to have a specific column value. For example, you might send an email when the Status column in the ServiceRequests row is updated to contain the value Active.

AND(
("Active" = [_THISROW_AFTER].[Status]),
([_THISROW_AFTER].[Status] <> [_THISROW_BEFORE].[Status])
)

In this expression, note the use of [_THISROW_BEFORE] and [_THISROW_AFTER]. These are system-provided references that refer to the state of the row before and after the change was made, respectively. This is really useful in order to check if the value of a field changed from one value to another.

Because [_THISROW_BEFORE] and [_THISROW_AFTER] are references, you use a dereference expression to access the value of a specific column in the referenced row.

Sending Email When a Row is Updated to Have Two or More Specific Column Values

The ServiceRequests table and the On Change in ServiceRequests workflow rule illustrate how to send an email when a new service request is in Active state and all needed parts are in hand. They work as follows:

  1. The ServiceRequests table contains the customer's name in column Name, the customer's email address in column Email, an Enum column that indicates whether the service request is Pending, Active, or Completed, and a Yes/No column that indicates whether parts are on order.  
  2. The workflow rule On Change in ServiceRequests is triggered on the AppSheet backend service each time a row in the ServiceRequests table is updated. It sends email to the email address contained in the Email column when either the Status or PartsOnOrder columns are changed, the Status is Active, and there are no PartsOnOrder. By checking whether Status or PartsOnOrder have just changed, we avoid sending email when some other column in the row has changed that is irrelevant to whether the service request is being worked on.
AND(
("Active" = [_THISROW_AFTER].[Status]),
NOT([_THISROW_AFTER].[PartsOnOrder]),
OR(
("Active" <> [_THISROW_BEFORE].[Status]),
[_THISROW_BEFORE].[PartsOnOrder]
)
)

Sending Email and Including File Attachments

You can create a workflow rule that allows the user to control the file attachments appended to the email. For example, you might have a brochure for each product you offer. Each brochure might reside in its own PDF file. The customer could select which products they are interested in and you could attach the appropriate PDF file for each product to the email.

The Customers table and the On Change in Customers workflow rule illustrate how to send an email that includes one or more file attachments selected dynamically by the client. For example, the app user can select the images or PDF documents to include with the email by selecting values from an EnumList. This works as follows:

  1. The Customers table contains the customer's name in column Name, the customer's email address in column Email, and an EnumList of potential email attachments in column Attachments.
  2. When the user edits a row in the Customers table they can select one or more values from the Attachments EnumList. In our case, the user can select Apple, Banana, or Orange, or any combination of the three. When Apple is selected, the file Apple.pdf is included as an email attachment. Likewise for Banana and Orange. The user selected values are stored in the Attachments field of the Customers row.
  3. The workflow rule On Change in Customers is triggered on the AppSheet backend service each time the Customers table is updated.
  4. The workflow rule uses the following expression to determine if column Attachments has a value and if its value has changed. If so, it sends email to the email address contains in the Email column.
AND(
ISNOTBLANK([_THISROW_AFTER].[Attachments]),
([_THISROW_AFTER].[Attachments] <> [_THISROW_BEFORE].[Attachments])
)

The workflow rule determines which PDF files to attach to the email using the following expression:

<<IFS(IN("Apple", [Attachments]), {"Apple.pdf"}) + IFS(IN("Banana", [Attachments]), {"Banana.pdf"}) + IFS(IN("Orange", [Attachments]), {"Orange.pdf"})>>

The Apple.pdf, Banana.pdf, and Orange.pdf files must reside on the server in the application folder. 

Sending Email from an Action Button

You can use an action button on the app to perform a data change. As a result, a single click of an action button in the app can trigger a customized email message that is sent to one or more recipients by the AppSheet backend service. You can label the action button to make it clear that an email will be sent when the action button is clicked.

The Orders table and the On Change in Orders workflow rule illustrate how to send email from an action button. It works as follows:

  1. The Orders table includes the email column Email and the numeric column EmailsSent.
  2. When the user is viewing a row in the Orders table, they can click the Email This Order action button. That increments the value in column EmailsSent.
  3. The workflow rule On Change in Orders is triggered on the ApppSheet backend service each time the Orders table is updated. It uses the expression below to determine if column EmailsSent has changed. If so, it generates an email and sends it to the email address contains in column Email.
AND(
ISNOTBLANK([_THISROW_AFTER].[EmailsSent]),
([_THISROW_AFTER].[EmailsSent] <> [_THISROW_BEFORE].[EmailsSent])
)

Sending Email from an Action Button and Prompting for More Data

You can create an action button that updates a row automatically when the action button is clicked. When the action button is clicked, you can prompt the user to enter additional data that controls who will receive the message, what data is contained in the message, and so forth.

The Products table and the On Change in Products workflow rule illustrate how to send email from an action button and prompt the user for the recipient's email address. It works as follows:

  1. The Products table contains a collection of products. Each product has a name, price, and description.
  2. Each row in table Email Form contains the email column Email To and the Ref column Product which refers to the related record in the Products table. 
  3. Each time a new row is added to table Email Form, an email is sent to the email address in column Email To. The email message contains information about the product in column Product.
  4. When the Email This Product action button is clicked while viewing a row in the Products table, the action button opens a form view that adds a new row to the Email Form table. The action button pre-populates the Product column in the new Email Form record with the product name from the Products table. It then prompts the user to enter the recipient's email address. That email address is saved to column Email To in the new Email Form record.
  5. The workflow rule On Change in Email Form is triggered on the AppSheet backend service each time a new row is added to the Email Form table. It generates an email based on the value in the Product column. It sends the email to the email address contained in the Email To column.

Choose and Send a Report from a List of Reports

The Reports table and the ReportOpenOrders and ReportActiveServiceRequests workflow rules illustrate how to choose and send a report from a list of reports. It works as follows:

  1. The Reports table contains a list of reports each of which has a report name and description.
  2. A user can go to the hamburger menu, select the Reports view, choose a report from the list of Reports, and click the Create action to trigger the selected report.
  3. The Create action updates the value in the LastRun DateTime field of the chosen Reports record.
  4. The Reports record update triggers either the ReportOpenOrders and ReportActiveServiceRequests workflow rule depending on the Name contained in the Reports record.
  5. The workflow rule generates a report and sends it to the appropriate email recipients.

Sending Email Only After Adding a Parent Record and All of Its Children 

When you add a parent record along with one or more children records, we first add the parent record, and we then add each of the child records. This can make it difficult to trigger a workflow only after all of the child records have been added. 

You can can use the following technique to ensure that all parent and child records are added before the workflow runs.

This technique uses the Form Saved event to trigger a data-change action which triggers the workflow rule only after the child records have been added.

Do this as follows:

  1. The child table must contain a Ref field to the parent table. Set the Is a part of property of the Ref field to On. This makes it possible to add child records when the parent record is added.
  2. Add a Text field to the parent table. Call the field Status and set its Initial value property to "" (blank). The Status column controls whether the workflow rule fires.
  3. Create a Data Change action for the parent table which sets the Status column's value to "Run".
  4. Create a second Data Change action for the parent table which sets the Status column's value to "" (blank).
  5. Create a Composite Action for the parent table that invokes the two Data Change actions above. This Composite Action invokes the first Data Change action to set the Status column's value to "Run", and the second Data Change action to set the Status column's value to blank. These two Data Changes will be sent to the server as separate updates. The first update fires the workflow rule, while the second update prevents further updates from firing the workflow rule.
  6. Go to the UX >> Views tab. Click Show system views. Select the form for the parent table. Expand Behavior. Find Event Actions and set its Form Saved property to the Data Composite action created in the previous step.
  7. Set your workflow rule's Update event property to UPDATES_ONLY. Set its Condition property to:
[Status] = "Run"

Maintaining Per User Workflow Settings

You can maintain "per user" workflow preferences. For example, you might want to allow each user to specify whether they prefer to receive an email or an SMS when a workflow is triggered.

You can store the preferences as follows:

  1. Create a new table having one record per user. Each record will contain that user's workflow preferences. You might call the table Workflow Preferences.
  2. Create a slice on the Workflow Preferences table so that each user "sees" only their own preference row. You might call the slice Workflow Preferences Slice. You cannot use a security filter because then one user's preference would not be visible to the other user's workflow rule.
  3. Create a view that allows each user to update the preferences in their Workflow Preferences Slice .

You can use the preferences as follows:

  1. In your workflow rule, check the preferences contained in the Workflow Preferences Slice for the current user to control what action is taken.

For example, if you wish to allow each user to specify whether they prefer to receive an email or an SMS when a workflow is triggered, do the following:

  1. Define both an Email and an SMS workflow rule.
  2. In the Email workflow rule Condition property, check whether the current user wishes to receive an email. In the SMS workflow rule Condition property, check whether the current user wishes to receive an SMS.

Invoking Other Workflow Actions

We have described how to send email, SMS, and notifications from the app. However, you can invoke any event-triggered workflow rule from the app using this approach. For example, you could invoke a workflow rule that uses a web hook to invoke an external web service.

Did this answer your question?