Expression for ReplyTo email address

[CPansewicz]

Hi,

I am trying to write an expression to choose one person with the "Processer" user-type to populate the ReplyTo email address field for an outgoing email. I would like the expression to filter from the user_type field instead of a specific persons name as well.

The expression I have works fine. But, it chooses the first person on the Google Spreadsheet with the user_type Processer. The issue is if the spreadsheet is re-sorted, then the ReplyTo person changes. Is there another way to write this so it is more specific, and will not change if the spreadsheet is re-sorted?

I hope this makes sense. Thank you!

ANY(
SELECT(
User[user_id],
[user_id].[user_type] = "Processer",
TRUE
))

[Markus_Malessa]

I think for one you might want to consider why the 'spreadsheet' is being reordered vs display data inside the application. Next, although the documentation for the ANY() function tells you that it selects an arbitrary item when using SELECT() to generate the list, I think I have encountered more often than not that it is still the first item of the list that it picks rather than 'random'. I think the reason the documentation says it's arbitrary is because the SELECT() function does not necessarily observe the order of records within the base table for the data, i.e. Sheets in this case.

So the next best thing you can do to preserve the original data order would be to introduce an index type column that indicates the original sort order and then use the ORDERBY() function with the SELECT() function like ANY(ORDERBY(SELECT())). Keep in mind that the _ROWNUMBER column will likely not help here either as it will likely change when the base data is resorted. Again, here is where you might want to consider who can edit the spreadsheet directly and why would they sort the data? Personally, I would probably revoke direct access to the spreadsheet and only allow access through the app.

[CPansewicz]

Ok. That makes sense. Thank you! Can you look at this expression? I have

ANY(
ORDERBY(
SELECT( User[user_id], [user_id].[user_type] = "Processer", TRUE ),
User[email_updates], FALSE)
)

When I test the data though, I receive the same output whether I choose True or False for the Orderby clause - screenshot is below. The key column is the [user.id]



Thank you! Camille

[Markus_Malessa · Accepted solution]

I think you need to take out User and just leave [email_updates]

[CPansewicz]

Thank you for reviewing the expression! I really appreciate your time.