_THISROW.BEFORE workaround help

AppSheet AppSheet Q&A
,
1

Hey guys. I’m looking for some help with expression.

I got two tables.

BULLETIN table - 13 columns, 1 row of random update information.
MODERATOR table 14 columns, 13 rows of different user info

Heres’s my issue.

I have a drop-down column in my BULLETIN table called “POC Mod”, which lists the key values of the Moderators in the MODERATOR sheet.

I created a workflow rule to send a phone notification when the Mod is changed in the “POC Mod” column

[_THISROW_BEFORE].[POC Mod] <> [_THISROW_AFTER].[POC Mod]

The notification is sent to the new Mod using [POC Mod].[Email] in the Notification’s “To” field
(The MODERATOR sheet has an email column)

However, I also want to send a notification to the old Mod, to let them know they’re off-duty. However, it seems there is no easy way to identify who the old mod is. (The equivalent of a_THISROW.BEFORE value i can use in the notification’s “To” field)

Does anyone have any ideas they’re willing to share on how I can circumvent this? What’s the easiest way to identify who the old mod is here?

1 Like
2

Add a virtual or normal column (name “Email”) with the formula [POC Mod].[Email]. Then you can use it like [_THISROW_BEFORE].[Email]. The reason for this is because you can’t write the formula like [_THISROW_BEFORE].[POC Mod].[Email]

3

Sorry, I tried your suggestion and it didn’t seem to work. So I’m confused by what you mean. Two questions.

  1. I’m creating the Virtual column in the BULLETIN table, right?

  2. How exactly do i use the virtual column as a [_THISROW.BEFORE] formula? I cant use it in the “To” field and it seems the virtual column itself uses the “AFTER” value when the change is made.

4

Mic_L_Angelo:

I also want to send a notification to the old Mod, to let them know they’re off-duty. However, it seems there is no easy way to identify who the old mod is. (The equivalent of a_THISROW.BEFORE value i can use in the notification’s “To” field)

You could try:

LOOKUP(
  [_THISROW_BEFORE].[POC Mod],
  "MODERATOR",
  "row-key",
  "Email"
)

replacing row-key with the name of the key column of the MODERATOR table.

5

Aleksi:

[_THISROW_BEFORE].

@Steve @Aleksi

This is the error i get, and part of the issue i was having before.

It seems i can use [_THISROW_BEFORE.] in regular expressions. Is this correct?

2X_e_e22a7c4aa78cdeffcd33bb21296c49e6e3eeaace.png
2X_e_e22a7c4aa78cdeffcd33bb21296c49e6e3eeaace.png1570×636 37.8 KB

6

Ah, _THISROW_BEFORE can’t be used in the To: address expression, it appears.

7

@Steve
Yes, this is the original problem that I’m trying to workaround. Any other ideas?

1 Like
8

You may have to record the previous moderator email in the row to accommodate your goal. Create a new normal (not virtual) column for this and set its App formula to the expression:

LOOKUP(
  [_THISROW].[row-key],
  "BULLETIN",
  "row-key",
  "POC Email"
)

replacing row-key with the name of the key column for the BULLETIN table.

1 Like
9

@Mic_L_Angelo Create that “Email” column (it can be normal or virtual, both works) in your Bulletin table. Use that [POC Mod].[Email] deref app formula with that column. When you know the email address in a Bulletin record, you can read it into “To” field as [_THISROW_BEFORE].[Email].

10

Aleksi:

[_THISROW_BEFORE].[Email]

@Steve @Aleksi Sorry folks bad news. Neither suggestion works form.

Dererf./Lookups don’t work cuz the value updated at the action is enacted. Which means it sends the assigned and unassigned message to the same (new) person.

And i can’t use the BEFORE column in the “To” field. I get an error. Any other suggestions?

11

I tested this with a sample app and it works. What is your email address so I can share that app to you?

12

mic.aguocha@gmail.com.

Thanks

13

Why not share with the community ?

14

@Aleksi Just a quick follow up. I haven’t seen the link to the app yet. So if you sent it, do you mind sending it again?

15

@Mic_L_Angelo Just resent it

16

Well… it’s just test app. Maybe I will clean it a little bit so it could be used as a sample. Though I can share that app to you as well if needed.

17

I was just interested in the expression used to reolve.

18

[_THISROW_BEFORE].[Email] is the solution.

1 Like
19

Aleksi:

[_THISROW_BEFORE].[Email]

Apologies @Aleksi but i can’t seem to get into the editor version of the app to see the workflow rule. Is it because it’s shared? I successfully downloaded the app and can view it’s data, but it’s not clear how i can access the editor from here.

Any suggestions?

20

Sorry… my mistake. You should now have the access.