App Formula no longer working

[Dialect_Junk]

I have an app formula that worked last week but now doesn’t seem to be working. And it works in the editor but not in the app.

I have 2 enumlist columns:
1 - “Assigned To”
2 - “Employee Email”

In the “Assigned To” column I select the names of employees that I want to assign work to. The “Employee Email” column populates the employee emails using following expresssion:

SELECT(
Employees Master DB[Employee Email], IN([Employee Name], [_THISROW].[Assigned To])
)

When I test the expression, it works and successfully pulls the emails of the employees I selected in the “Assigned To” column. But it doesn’t work in the app and it doesn’t record the email in the spreadsheet.

Again, this worked last week. I checked the spreadsheet and all of the work orders I assigned last week show the correct employee emails.

Any suggestions are appreciated!

[hru]

Dialect_Junk:

Again, this worked last week. I checked the spreadsheet and all of the work orders I assigned last week show the correct employee emails

This could be something that you need to report to support@appsheet.com ?

[Suvrutt_Gurjar]

Dialect_Junk:

SELECT(
Employees Master DB[Employee Email], IN([Employee Name], [_THISROW].[Assigned To])
)

Hi @Dialect_Junk,

I believe in the expression you have shared, the sub expression IN([Employee Name], [_THISROW].[Assigned To]), the part [_THISROW].[Assigned To] returns only one value as a text whereas the IN() expression syntax requires the second part of the expression to be a list.

the syntax of IN() expression is IN( match-value , in-list )

Typically, the app editor throws an error for the expression, when second part is not a list.

Could you please try an expression

SELECT(
Employees Master DB[Employee Email], IN([Employee Name], LIST([_THISROW].[Assigned To]))

OR another variation
SELECT(
Employees Master DB[Employee Email], [Employee Name]= [_THISROW].[Assigned To])

)

Edit: Hi @Dialect_Junk, I am sorry that I missed the part that your [Assigned to] and [Employeee Email] are Enumlist columns. Please ignore above solution. I will revert with a response, if I get a proper solution.

[Dialect_Junk]

Thank you, kindly.

[Suvrutt_Gurjar]

Hi @Dialect_Junk,

Could you please update in which table these two enumlist columns are. Could you please update where the SELECT() expression is and what type of column you have assigned to this SELECT() expression? From the expression it is clear that [Employee Email] and [Employee Name] are in table Employees Master DB Are these also enumlist columns?

In summary, could you please mention type of associated columns in Employees Master DB and the other table?

[Dialect_Junk]

The 2 enumlist columns (“Assigned To” and “Employee Email”) are in a table called “Work Order Requests.”

The SELECT expression is in the “Employee Email” column, which is an enumlist column. I entered this SELECT expression into the “App Formula” field.

In the Employees Master DB table, the [Employee Email] column is an Email type column and the [Employee Name] is a text type column. The Employees Master DB table is read only.

[Suvrutt_Gurjar]

Hi @Dialect_Junk,

Thank you for all the details.

I tested the expression and setup as you have shared and it works exactly as you have configured and need it to work. In the screenshot below, upon selecting the Customer Names [Customer Names 2] which is an enumlist column, the customer emails are populated as per selection in [Customer Email2] column which is another enumlist column.

Even back end spreadsheet is populated with email list.

I am hereby sharing the settings of Customer Email2 column , just in case it helps

Edit:: One minor difference is in [Name] column is of “Name” type and not text ype in the lookup table in the test setup, but I believe that should not matter.

[Dialect_Junk]

I just discovered that the SELECT expression works when I first assign the work; it perfectly captures the email addresses of the employees I have chosen.

However, if I remove or add a name to the “Assigned To” column after creating the work order, the emails do not get updated. The emails from the original list of names remains.

Am I misunderstanding how the “App Formula” field works? How else would I do this?

[Suvrutt_Gurjar]

Hi @Dialect_Junk,

Thank you. My testing shows that even after changing names in the “assigned to” equivalent column in my case ([Customer Name2], the emails list changes as per selection.

I believe, if one edits the row by opening the corresponding record, even the real columns should reflect the latest changes after saving the record.

Is it possible that there is some other logic , constraint associated in your app?

[Steve · Accepted solution]

Dialect_Junk:

Am I misunderstanding how the “App Formula” field works? How else would I do this?

An app formula for a normal (non-virtual) column is only evaluated when a row is updated, such as when edited in a form or modified by an action. The app formula for a virtual column is evaluated every time the app syncs. The easiest fix here would probably be to convert Employee Email to a virtual column.

[Dialect_Junk]

That did the trick. Thank you for the explanation, Steve.