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?
Solved! Go to Solution.
[_THISROW_BEFORE].[Email] is the solution.
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]
Sorry, I tried your suggestion and it didnโt seem to work. So Iโm confused by what you mean. Two questions.
Iโm creating the Virtual column in the BULLETIN table, right?
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.
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.
Ah, _THISROW_BEFORE canโt be used in the To: address expression, it appears.
@Steve
Yes, this is the original problem that Iโm trying to workaround. Any other ideas?
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.
@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].
@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?
I tested this with a sample app and it works. What is your email address so I can share that app to you?
Why not share with the community ?
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.
I was just interested in the expression used to reolve.
[_THISROW_BEFORE].[Email] is the solution.
Thanks
@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?
@Mic_L_Angelo Just resent it
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?
Sorryโฆ my mistake. You should now have the access.
@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.
Interestingโฆ when I tested this, I didnโt receive any error messages. We need to check this, for sure.
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.
But if you save it again, it accepts that formula?
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
Itโs possible then that I could see that error message as wellโฆ I just never wait them
@Aleksi App doesn't give any error but in the To field [_THISROW_BEFORE] is not working.
Please start a new topic for help with this.
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
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |