Problems with [_THISROW_AFTER] and [_THISROW_BEFORE]

[andresomza]

Hi, my name is Andrés. I currently have an application in which I track flights for the company I work for.

Overall, it works very well, but I have an issue when trying to do the following:

I have a function that checks whether a crew member has been modified in order to notify them if they have been assigned or unassigned from a flight. For this, I use [_THISROW_AFTER] and [_THISROW_BEFORE].

The problem is that I’m not getting the email addresses of these people correctly. I’m attaching some code I use for debugging and the email I’m receiving (I’ve shortened the emails for security reasons).

[PauloExpert]

From what you shared, you’re correctly using [_THISROW_BEFORE] and [_THISROW_AFTER] to compare crew assignments (like Pilot, Copilot, TCP 1, and TCP 2) before and after a change, which is a great approach to detect if someone was added or removed.

However, the email you’re receiving shows the same email addresses in both “Before” and “After”, which means the expressions might not be pulling the expected values from before the update.

Likely Cause:

In your expressions like:

LOOKUP([THISROW_BEFORE].[Piloto], “Usuario”, “Key”, “Email”)

AppSheet might not actually resolve [Piloto] from _THISROW_BEFORE properly if the syntax isn’t exact. If you just write [Piloto] inside a LOOKUP without explicitly referencing _THISROW_BEFORE or _THISROW_AFTER, it often defaults to the current row.

Fix:

You should rewrite your LOOKUPs like this:

LOOKUP([_THISROW_BEFORE].[Piloto], “Usuario”, “Key”, “Email”)

LOOKUP([_THISROW_BEFORE].[Copiloto], “Usuario”, “Key”, “Email”)

LOOKUP([_THISROW_BEFORE].[TCP 1], “Usuario”, “Key”, “Email”)

LOOKUP([_THISROW_BEFORE].[TCP 2], “Usuario”, “Key”, “Email”)

LOOKUP([_THISROW_AFTER].[Piloto], “Usuario”, “Key”, “Email”)

LOOKUP([_THISROW_AFTER].[Copiloto], “Usuario”, “Key”, “Email”)

LOOKUP([_THISROW_AFTER].[TCP 1], “Usuario”, “Key”, “Email”)

LOOKUP([_THISROW_AFTER].[TCP 2], “Usuario”, “Key”, “Email”)

That should give you the actual emails from the correct before" and “after” records.

[andresomza]

Hi Paulo,

Thank you for the detailed explanation. I completely understand the importance of explicitly referencing _THISROW_BEFORE and _THISROW_AFTER in the LOOKUP expressions, and in fact, that’s exactly how I have them set up in my code.

For example, I’m using expressions like:

LOOKUP([_THISROW_BEFORE].[Piloto], “Usuario”, “Key”, “Email”)

…and similarly for the other crew roles.

However, the issue I’m seeing doesn’t seem to be related to that syntax. All the emails returned—both for “before” and “after”—are the same and correspond to the first record in the “Usuario” table, rather than the actual users involved in the current changes.

Do you have any idea what else might be causing this behavior? I appreciate any insights you might have.

Best regards,
Andrés

[Suvrutt_Gurjar]

Could you update how the crew is changed by the user? Do you have a form for it that user saves after changing the crew?

[andresomza]

Hello Suvrutt_Gurjar,

I am currently loading this crew using a “quick edit” or from an input action.

I updated my debug formula, and the strange thing is that it works outside of the lookup:

In this example I have added a New Pilot

[Suvrutt_Gurjar]

@andresomza

I updated my debug formula, and the strange thing is that it works outside of the lookup:

Thank you for the update. In that case you could pull the emails in the tables only if possible rather than pulling them through LOOKUP() in the template.

[andresomza]

Although it’s not the solution I would have preferred—since I try to avoid using too many virtual columns—it worked. I created four virtual columns for the people I needed, used a LOOKUP to retrieve the email in each, and then referenced them in the bot using THISROW_BEFORE and THISROW_AFTER.

Thank you very much for your help! I really appreciate your support. I hope the issue where LOOKUP can’t access data with THISROW_BEFORE and THISROW_AFTER can be addressed in the future.

Best regards,

[Suvrutt_Gurjar]

You are welcome. Could you update if you have a reference relationship between the table where the crew assignment is done and the Users table.

I believe the table Usario has one record per user and contain the list of users including crew?

Based on your response to the above, we could try getting rid of the virtual columns you have created.

[andresomza]

Hi Suvrutt,

Thank you for your patience and continued help.

I just double-checked, and actually yes — the crew fields (like “Piloto”) are set up as Ref type columns.

Given that, and based on your suggestion, I realized I don’t need to use LOOKUP() inside the bot at all.
Instead, I can simply dereference the Ref field to access the related email directly, like this:

[_THISROW_BEFORE].[Piloto].[Email]

[_THISROW_AFTER].[Piloto].[Email]

This approach is much cleaner and avoids the need for extra virtual columns or complicated lookups.

Thank you very much again

Best regards,

[Suvrutt_Gurjar]

You are welcome.

Excellent, thank you for the update.

Nice to know that you simplified the expression.