_THISROW.BEFORE workaround help

[Mic_L_Angelo]

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?

[Aleksi]

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]

[Mic_L_Angelo]

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.

[Steve]

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.

[Mic_L_Angelo]

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?

[Steve]

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

[Mic_L_Angelo]

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

[Steve]

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.

[Aleksi]

@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].

[Mic_L_Angelo]

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?

[Aleksi]

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

[gregdiana1]

Why not share with the community ?

[Aleksi]

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.

[gregdiana1]

I was just interested in the expression used to reolve.

[Aleksi · Accepted solution]

[_THISROW_BEFORE].[Email] is the solution.

[Mic_L_Angelo]

mic.aguocha@gmail.com.

Thanks

[Mic_L_Angelo]

@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?

[Aleksi]

@Mic_L_Angelo Just resent it

[Mic_L_Angelo]

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?

[Aleksi]

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

[Mic_L_Angelo]

@Aleksi @Steve I got it to work, ([_THISROW_BEFORE].[Email] in the “To” field) many thanks!

But I must point out that the reason why I didn’t come to this conclusion myself is that when i initially put this expression into the “To” field, I got an error message indicating that the _THISROW_BEFORE column doesn’t exist. This dissuaded me from pursuing that solution further.

Aleksi, the app you shared with me gets the same error (see the attached image). However, when I test the rule, the notification gets sent as expected, and the error message disappears. So it seems to me that the error is related to the _THISROW_BEFORE columns not existing until the rule works once.

In any case, I likely would have resolved this issue myself had i not seen that error message. So maybe this is something the Appsheet team can look into? If my assumption is correct, then it seems I got the error message, well, in error. I could see many more people getting confused here.

[Aleksi]

Interesting… when I tested this, I didn’t receive any error messages. We need to check this, for sure.

[Mic_L_Angelo]

Cool, if you need anything from me, let me know.

FYI. I created another workflow rule with that expression and received that erroneous error message again. So this is not a one-off.

[Aleksi]

But if you save it again, it accepts that formula?

[Mic_L_Angelo]

yep. i don’t receive any errors from the app once i save it. I just get that error when in first put the expression in the “To” field

[Aleksi]

It’s possible then that I could see that error message as well… I just never wait them

[Akshay_patel95]

@Aleksi App doesn't give any error but in the To field [_THISROW_BEFORE] is not working.

[Steve]

Please start a new topic for help with this.

[efff]

Newly created virtual columns are not recognized by appsheet until the app is saved. Also, Appsheet will not recognize a newly added column in googlesheet until it is regenerated. These "unrecognized" columns can lead to such "unable to find column" errors