To learn more about workflow rules in general, you can start with the Workflow Introduction section. 

Workflow on Add, Update, or Delete

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.
  • An existing row is deleted.

Your workflow rule is triggered on the AppSheet server when a user does an add, update, or delete through the AppSheet client and then syncs these changes back to the server. Once the client syncs:

  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 field is false.
  • A row is updated in the Orders table, the value in the OrderAmount field is over $500, and the value in the DeliveryDate field is within 10 days of the current date.

Changes made directly to the spreadsheet never trigger workflow rules

Changes you make directly to the spreadsheet 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.


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 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 record is updated to contain the value "Active".

AND( [Status]="Active", [_THISROW_BEFORE].[Status] <> [_THISROW_AFTER].[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. 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 field in the referenced record. 

When comparing before and after values, be aware of the following issue. If both the before and after values are empty, the following expression always return 'true'.

[_THISROW_BEFORE].[Status] <> [_THISROW_AFTER].[Status]

You can circumvent this limitation by ensuring that the after value is not empty. Any of the following expressions do this:

AND( [_THISROW_BEFORE].[Status] <> [_THISROW_AFTER].[Status], ISNOTBLANK([_THISROW_AFTER].[Status]) )

AND( [_THISROW_BEFORE].[Status] <> [_THISROW_AFTER].[Status], ISNOTBLANK([Status]) )

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

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 column is 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( [Status]="Active", NOT([PartsOnOrder]), OR(   AND(ISNOTBLANK([_THISROW_AFTER].[Status]), [_THISROW_BEFORE].[Status] <> [_THISROW_AFTER].[Status]), [_THISROW_BEFORE].[PartsOnOrder]<>[_THISROW_AFTER].[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 Enum list. 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 Enum list 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" enum list. 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 Customer 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([Attachments]),     [_THISROW_BEFORE].[Attachments] <> [_THISROW_AFTER].[Attachments] )

  1. The workflow rule determines which PDF files to attach to the email using the following expression.<<LIST(IF(CONTAINS([Attachments], "Apple"),"Apple.pdf","")) +     LIST(IF(CONTAINS([Attachments], "Banana"),"Banana.pdf","")) +     LIST(IF(CONTAINS([Attachments], "Orange"),"Orange.pdf",""))>>
  2. 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_BEFORE].[EmailsSent] <> [_THISROW_AFTER].[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" Date Time field of the chosen Report record.
  4. The Report record update triggers either the "ReportOpenOrders" and "ReportActiveServiceRequests" workflow rule depending on the "Name" contained in the Report record.
  5. The workflow rule generates a report and sends it to the appropriate email recipients.

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

Currently, 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. The Data Change action sets the value of a Status column to a specific value. The workflow rule's Condition, triggers the workflow rule when the Status column contains that specific value.

Do this as follows:

  1. Add a Status column to the parent record. This Status column indicates whether the workflow rule should fire. The Status column can be a simple text column. Assign this column an InitialValue that indicates that the workflow rule should not fire. For example, you might set the Status column's value to blank.
  2. Create a Data Change action to update the Status column's value. Set the column's value to the value that triggers the workflow rule. For example, you might set the Status column's value to "Run".
  3. Go to the parent form. Set its "form saved" event to invoke the Data Change action.
  4. Configure your workflow rule. Set the workflow rule's "Update event" to fire on updates. Set the workflow rule's Condition to check the value of the Status column. For example, you might specify the Condition:
    [Status] = "Run"
  5. To make the workflow only fire once, you can replace the single Data Change action with a Composite Action. The Composite Action would invoke the first Data Change action to set the Status column's value to "Run", and the second Data Change action to clear the Status column's value to blank. These two Data Changes will come in as separate updates. The first update fires the workflow rule, while the second update prevents further updates from firing the workflow rule.

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?