Expression evaluates differently within the system

[WillowMobileSys]

I have narrowed down an issue to a relatively simple expression that seems to evaluate differently between a Detail View and a Form view. I am confident i have seen this similar problem in another app while helping someone else. I am wondering if anyone else is seeing this kind of issue?

The expression below simply checks that the Role of the person modifying the row is NOT the value of “Zero”:

ANY(SELECT(Authority[Role], [User]=[_THISROW].[Modified By])) <> "Zero"

I assigned this expression to a Virtual Column to verify its result. When I look at a row’s Detail view, the expression is evaluated to “N” or FALSE (see picture below):

If I simply tap the Edit button to navigate to the row in the Form view, the value of the expression evaluates to “Y” or TRUE (pic below):

The main issue is that this expression was part of a Workflow Trigger in a prod app. The Workflow was not correctly triggering. In a test version of the app, all is good if I remove the SELECT.

Thoughts?

[Steve]

I see two possibilities, both related to the odd handling of = and <> operators. I thought it’d been fixed, but maybe not.

1. If [User] evaluates to blank, [User]=[_THISROW].[Modified By] is TRUE regardless what [_THISROW].[Modified By] is.

And/or:

2. If ANY(...) evaluates to blank, ANY(...) <> "Zero" is TRUE.

[WillowMobileSys]

Steve:

I see two possibilities, both related to the odd handling of = and <> operators. I thought it’d been fixed, but maybe not.

I wonder if I try NOT() instead of “<>”?

For completeness, I forgot to mention I have tried many re-organizations of the expression by moving the [Modified By] to the left of “=”, removing the [_THISROW], etc. I have also created other Virtual columns to evaluate the pieces individually:

SELECT(Authority[Role], [User]=[_THISROW].[Modified By]) - whichs returns "First" as expected.

[Modified By]  - which returns "John Baer" as expected.

[User] is the column from the Authority Table and all rows in the table have a value. The value for this particular use case is indeed in the table.

Still bottom line, the same exact expression with the same exact data is being evaluated two different ways between the Edit view and the Form view. Something is amiss!!

[Steve]

WillowMobileSystems:

Still bottom line, the same exact expression with the same exact data is being evaluated two different ways between the Edit view and the Form view. Something is amiss!!

Expressions in form views are evaluated by the app itself, where expressions in workflows are evaluated by the servers, and virtual column values may also be evaluated by the servers. There are known discrepancies between these platforms.

[WillowMobileSys]

Do you think that the Virtual Column for the Edit view is evaluated in a different place than the same Virtual Column in the Form view?

[Steve]

When the row is loaded into the form, the virtual column value is evaluated by the app. When the form is saved, the app’s evaluation is retained in the near-term. But when the app syncs (in the background or with an explicit sync), the virtual column will be recomputed by the server and that recomputed value will be returned to and adopted by the app.

[WillowMobileSys]

Got it! So it could be the Edit view is using the server VC evaluation but the Form view is re-evaluating on the app side when it is opened.

A side note: The Form view seems to give the correct result. I tested in both emulator and on an iPhone. Same results.

I also unsuccessfully tried using NOT(), removing negation altogether and matching in table on different columns. The Edit and Form view always give opposite results.

[Steve]

Might also use the Test option in Expression Assistant. That runs on the server, but may be different server/implementation than workflow and VC calculations.

[WillowMobileSys · Accepted solution]

I have found alternative solutions. If the results are tested as Lists or if a TRIM() function is used, the expression always evaluates as expected. All of these expressions below work.

Comparing Lists:

SELECT(Authority[Role], [User] = [_THISROW].[Modified By]) <> LIST("Zero")
or
SELECT(Authority[Role], [User] = [_THISROW].[Modified By]) <> {"Zero"}

Using TRIM() function:

TRIM(ANY(SELECT(Authority[Role], [User] = [_THISROW].[Modified By]))) <> "Zero"

[Steve]

The list comparisons working make sense to me (sorta?). I’m entirely perplexed by TRIM(), though!