Workflow triggered by a change in specific cell?

(David Ames) #1

I was wondering if it is possible to have a workflow (email) triggered by a change in a specific CELL (as opposed to changes in a ROW).

I am working on a workout app for clients (I’m a personal trainer). I want to be notified when they complete, for example, Workout #6 (workouts are listed in columns, exercises are listed in rows). I want to be notified when they, for example, click a checkbox next to the last exercise in Workout #6 (a checkbox that would be in a specific cell in the data table).

ALSO, if someone could point me in the direction of clear instructions on how to set up a Notification via Email Workflow, that would be great! I’ve searched the help and forums and cannot find a simple explanation on how to set this up.

Thank you!

1 Like
(Levent Kulacoglu) #2

Yes of course. Try this as a conditional rule:

[_THISROW_BEFORE].[YourControlColumnName] <> [_THISROW_AFTER].[YourControlColumnName]
1 Like
(David Ames) #3

Thanks! Unfortunately, I’m going to need a little more instruction on where to put this, and if I need to change “_THISROW_BEFORE” and “_THISROW_AFTER” to reflect row # or row ID or row name.

That’s why I also asked if someone could point me in the direction of clear instructions on how to set up an email workflow. I don’t know where to put this conditional rule that you suggested.

Thank you!

(Levent Kulacoglu) #4

I got it. I will try to be more clear on this. You can start with here:

(David Ames) #5

Awesome. Thank you! I will check it out.

Do I need to change “_THISROW_BEFORE” and “_THISROW_AFTER” to reflect row # or row ID or row name, or anything like that?

(John Baer) #6

Nope. Use them exactly as is. They are system variable that reference the before and after state of EACH of the row(s) that changed.

I understand your confusion. How do you get the specific row you want? AppSheet is row based processing, so you will need to filter down to the specific row by adding to the conditional statement in the Workflow.

Be sure to read the article Levent provided. It gives you exactly the statement format you’ll need.

1 Like
(Philip Garrett) #7

To expand on what John wrote.

When you update a value in an AppSheet table, the entire updated row, and only that row, is sent from the client to the server.

If you have defined a Change workflow rule for the updated table, that rule is invoked when the updated row arrives at the server.

Your workflow rule can examine the value of every field in the updated record. In your case, you apparently only care about changes that occur in a single “cell” or field of the row.

Each workflow rule can include a Condition that is tested each time the rule is invoked. The rule only goes ahead, if that Condition is satisfied.

In your case, the Condition could check to see whether the field you care about has changed. As Levent said, you can determine that be comparing the Before and After values of that field.

1 Like
(David Ames) #8

Thank you both for your help. I got the email to work when ANY row in the listed column is updated.

PHIL - In order to take it further and add the Condition you are talking about…Can you provide syntax for this Condition to check a specific cell, or direct me to some instructions on how to set this up?

Thank you!

(David Ames) #9

PS. The instructions at aren’t really that clear. (I’m sorry if I’m being dense here.)

(John Baer) #10

David, if you don’t mind I can jump in here to help.

First, just to be clear, when you said specific CELL, I assume you mean a specific column in a specific row. Is this right?

You mentioned that you got the workflow rule to work for ANY row where your desired column changes. You now just need to have the workflow rule fire off ONLY when the column changes on the SPECIFIC row. To do this you need to be able to UNIQUELY identify the row by one of its attributes. Then you can add to your condition in the workflow rule so it looks like the following:

AND( [RowAttribute]=“UniqueValue”,
[_THISROW_BEFORE].[QueriedColumn] <> [_THISROW_AFTER].[QueriedColumn] )

Where RowAttribute is the column you can find the value that uniquely identifies your row. Of course, UniqueValue is the value in the RowAttribute column that identifies your row.

An example.

Let’s say you have rows that list the costs for each job completed. One of the columns is Other Costs. You then summarize all these values in a single summary row. You want to send an email whenever a job has been added that has an Other Costs value or Other Costs has been updated in a job. You would add a row column and maybe call it “Row Type”. For each job the “Row Type” = “Job”. For the summary row the Row Type = “Summary”

So now you can create the workflow rule as follows:

AND( [Row Type]=“Summary”,
[_THISROW_BEFORE].[Other Costs] <> [_THISROW_AFTER].[Other Costs] )

If you don’t have any attribute that allows you to uniquely identify the specific row you are after, then you’ll need to add one to make this work.

I hope this helps!!


And @David_Ames - just to be super clear here, as all the posts above indicated, the [_THISROW_BEFORE].[Other Costs] <> [_THISROW_AFTER].[Other Costs] part of the formula is checking if the [Other Costs] column has changed in the current row, which will then trigger the workflow.

Took me a while to get used to how the system variables like _THISROW work…

(Levent Kulacoglu) #12

Ahahahaha… @Mike believe me I still sometimes do not understand it :-)))))))) @Aleksi will either kill me or hang me high if I one more time ask for it :-))))))))


:joy: @LeventK - if you can’t figure it out, the rest of us are lost! :scream:

I took a quick look in the support documents and could not seem to find “system variables” other than in “workflow”. Wonder if the _system variables are listed anywhere? Seems they show only on documents about other concepts like Workflow templates and Expressions, but not in a Systems Variables document?

syntax…syntax…syntax and variables… :slight_smile:

(Levent Kulacoglu) #14

Well @Mike, we were positively discussing about those help documentation and their structure where @praveen had also denoted that the post was very important and he will be following. However, for an unknown reason I had noticed that @Peter had chosen/decided to lock and close that post for further comments as of May 13th. And interesting enough, can you believe that it’s the one and only post that is locked/closed in the whole Feature Requests page so far :grimacing::nerd_face:

1 Like

Haha. I guess that post must have been started by someone special :wink:

(Levent Kulacoglu) #16

I believe it’s totally @Kirk_Masden’s fault :slight_smile: He had flooded my post :-))))))) ほんの冗談です :smiley:
Thinking about interesting topics but I’m short of them these days…May be you can give idea or propose a couple of topics @Mike :wink:

(David Ames) #17

OMG, I think this did it! Thank you John! Yes I was looking for an update (YES/NO Checkbox being checked YES) in a specific CELL (14:K for example…Row 14, Column K…“Cable Abs” row, “Workout #6” column in my example) to trigger the email workflow. And NOT to send email when the checkbox was checked in any of the other Rows in Column K. Just that one specific CELL in that specific Row.

John, your AND( [RowAttribute]=“UniqueValue”, addition allowed me to specify the specific ROW, and the [_THISROW_BEFORE].[QueriedColumn] <> [_THISROW_AFTER].[QueriedColumn] ) specifies the specific COLUMN. Brilliant! I was just missing the syntax to specify which ROW.

By the way, For AND( [RowAttribute]=“UniqueValue”,
The RowAttribute is the Column NAME of the first column (specific to your individual spreadsheet.) And the “UniqueValue” is the text that appears in the specific ROW you are trying to trigger.

So the first part of my condition now reads AND( [2 SETS OF 10 REPS]=“Cable Abs”, It took me some playing around with UniqueValue and RowAttribute to figure out that I needed to replace the words ‘RowAttribute’ with the text that is specific to MY spreadsheet (2 SETS OF 10 REPS). And replace the words ‘UniqueValue’ with the text that appears in the cell in Row 14 of my “2 SETS OF 10 REPS” column (Cable Abs).

Now, when the checkbox next to the Cable Abs exercise in Workout #6 is checked, only then will the workflow send me an email saying “Workout #6 is complete.” which is exactly what I wanted it to do!

I hope my explanation makes sense and might help others who are having similar questions and confusions with this particular behavior.

Thank you to all who chimed in to help!!!

1 Like
(Kirk Masden) #18

Thanks @LevenTK! Actually, I was wondering about the closure of the thread but I didn’t want to become persona non grata by asking about it too much.

I know you’re just joking but, on the other hand, there’s a sense in which I can’t help but wonder if something about the way I participated or the content I posted might not have led to the closing. I hope not.

By the way, your Japanese is perfect! If that’s the result of a machine translation, it’s excellent. Or, perhaps you are fluent in Japanese and I just didn’t know. :wink: At any rate, thanks for mentioning me. I hadn’t been following this thread so I would have missed your comments about the closing of the thread if you hadn’t mentioned me.

(John Baer) #19

David, Sorry for the lack of clarity! Some of us have been software developers for years and take things for granted and forget to include the details. Glad you figured it out!

(Peter Dykstra (AppSheet)) #20

@LeventK only closed because it seemed like it was straying from the objective of that category, which is requests for the product itself, not reference material. Definitely not reflective of the importance of the idea - I completely agree that the structure of the content and accessibility can be greatly improved, think your feedback on that is important.