New error even though app definition unchanged. all backups have same error. They didn't when saved

[Lucinda_Mason]

Been off taking my youngest to college. Just opened editor and got two errors. The thing is, I haven’t touched the app definition since Tuesday and everything was working perfectly. I have a similar error on another slice. I’ve been using the app and everything worked fine, UNTIL NOW. It’s like opening the app in the editor CREATED the error that wasn’t there before, now the app doesn’t work either.

This is the error message: (see below for row filter condition):
Table slice ‘All_Active_Private_Requests_Visible_to_User’ uses an invalid filter condition ‘=AND([START_DATE]<=Today(),[END_DATE]+7>=Today(),[ANSWERED]=“Keep Praying”, [SCOPE_NO]<>20, ISNOTBLANK([REQUEST]), OR(LOOKUP(USEREMAIL(), Users, USER_EMAIL, Key_Prayer), SWITCH([SCOPE_NO], 1,AND(IN( LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{1, 3, 5}), IN(ANY([Country_GEO_NO]),LOOKUP(USEREMAIL(), Users, USER_EMAIL, Country_GEO_NO))), 2,AND(IN( LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{3, 5,15}), IN(ANY([Country_GEO_NO]),LOOKUP(USEREMAIL(), Users, USER_EMAIL, Country_GEO_NO))), 3, AND(IN( LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{1, 3, 5, 10, 12, 15, 20}), [GEO_NO]=LOOKUP(USEREMAIL(), Users, USER_EMAIL, GEO_NO)), 4,AND(IN( LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{1, 3, 5, 10, 12, 15, 20}), [GEO_NO]=LOOKUP(USEREMAIL(), Users, USER_EMAIL, GEO_NO)), 5,AND(IN( LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{10, 12}), [GEO_NO]=LOOKUP(USEREMAIL(), Users, USER_EMAIL, GEO_NO)), 10,IN([TEAM_NO], LOOKUP(USEREMAIL(), Users, USER_EMAIL, TEAM_NO)), 11,IN([DPT_NO], LOOKUP(USEREMAIL(), Users, USER_EMAIL, DPT_NO)), 12,IN(LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{15, 20, 22, 24, 26, 30, 40}), 13, OR(AND(IN( LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{10, 12}), [GEO_NO]=LOOKUP(USEREMAIL(), Users, USER_EMAIL, GEO_NO)),IN(LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{15, 20, 22, 24, 26, 30, 40})), 15,IN(LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{1,3,5,10,12,15,20,22,24,26,30,40}), “” )))’. SWITCH function is used incorrectly:Inputs to SWITCH() must be an initial expression, one or more value-result pairs, and a default result. Input 2 is not a value of a consistent type.

Row Filter Condition:
AND([START_DATE]<=Today(),[END_DATE]+7>=Today(),[ANSWERED]=“Keep Praying”, [SCOPE_NO]<>20, ISNOTBLANK([REQUEST]),
OR(LOOKUP(USEREMAIL(), Users, USER_EMAIL, Key_Prayer),
SWITCH([SCOPE_NO],
1,AND(IN( LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{1, 3, 5}), IN(ANY([Country_GEO_NO]),LOOKUP(USEREMAIL(), Users, USER_EMAIL, Country_GEO_NO))),

2,AND(IN( LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{3, 5,15}), IN(ANY([Country_GEO_NO]),LOOKUP(USEREMAIL(), Users, USER_EMAIL, Country_GEO_NO))),

3, AND(IN( LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{1, 3, 5, 10, 12, 15, 20}), [GEO_NO]=LOOKUP(USEREMAIL(), Users, USER_EMAIL, GEO_NO)),

4,AND(IN( LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{1, 3, 5, 10, 12, 15, 20}), [GEO_NO]=LOOKUP(USEREMAIL(), Users, USER_EMAIL, GEO_NO)),

5,AND(IN( LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{10, 12}), [GEO_NO]=LOOKUP(USEREMAIL(), Users, USER_EMAIL, GEO_NO)),

10,IN([TEAM_NO], LOOKUP(USEREMAIL(), Users, USER_EMAIL, TEAM_NO)),

11,IN([DPT_NO], LOOKUP(USEREMAIL(), Users, USER_EMAIL, DPT_NO)),

12,IN(LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{15, 20, 22, 24, 26, 30, 40}),

13, OR(AND(IN( LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{10, 12}), [GEO_NO]=LOOKUP(USEREMAIL(), Users, USER_EMAIL, GEO_NO)),IN(LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{15, 20, 22, 24, 26, 30, 40})),

15,IN(LOOKUP(USEREMAIL(), Users, USER_EMAIL, PI_NO),{1,3,5,10,12,15,20,22,24,26,30,40}),

“”
)))

[natalie (Natalie Kadonaga)]

The error means that AppSheet doesn’t think the type of the 2nd input of SWITCH is convertible to the type of the 1st input of SWITCH. In other words, AppSheet doesn’t think 1 is convertible to SCOPE_NO (which may not be the correct behavior, depending on the column type of SCOPE_NO). What’s the column type of SCOPE_NO?

[Lucinda_Mason]

In the Requests table SCOPE_NO is a REF to the Scope_Slice.
In the Scope table, SCOPE_NO is text with UNIQUEID() as the initital value though the DATA are numbers.

[natalie (Natalie Kadonaga)]

Can you open a support issue with support@appsheet.com and (1) link them this post so they assign it to me and (2) provide the account ID of the owner of this app? I will try to unbreak your app ASAP. Thanks!

[Lucinda_Mason]

thank you. Done.

[Lucinda_Mason]

I appreciate Natalie’s quick response:
I believe the behavior you’ve encountered is a case where AppSheet is too aggressive with showing an error. I can write a fix for AppSheet to stop showing you the error, but it will be delayed until we next deploy, which is at least a day from now. In the meantime, if you need an urgent fix, a temporary workaround is to create a virtual column in the Requests table called something like “SCOPE_NO_2”, with app formula “[SCOPE_NO]”, then set the type of SCOPE_NO_2 to Text. Then, replace the usage of “[SCOPE_NO]” in the SWITCH expression to “[SCOPE_NO_2]”.

---

But I’m concerned with WHY NOW? I’ve had these two expressions that are now triggering errors for a long time with NO issues. So is this something new that AppSheet is doing?

I’m wondering why it happened all of a sudden.

[natalie (Natalie Kadonaga)]

(I originally sent this message in the support thread, but I have replicated my response below.)

For my earlier message, I realize now that I think another (possibly better) workaround to fix your SWITCH expression is to add “” around the 2nd/4th/etc arguments, which would look like: SWITCH([SCOPE_NO], "1", ..., "2", ........) . A Text expression like “1” should be convertible to any other type except Lists.

To answer your question, this specific issue was caused by a fix to column value expressions, such as [SCOPE_NO] , so they compile to Ref instead of Text, since the SCOPE_NO column is a Ref. I tried to prepare all app expressions for this change to prevent breakages such as these, but as I’m seeing, I didn’t find all the cases. Your other expressions that broke are likely related to this recent change if they use Ref columns. If you have more details, I can investigate what happened with your other expressions. I understand it’s frustrating to deal with new errors in AppSheet, so I am currently investigating ways to proactively warn apps so that this doesn’t happen in the future and adding more helpful error messages.

[Steve (Steve Coile)]

Woooooowwwwwww…

AND(
  [START_DATE]<=Today(),
  [END_DATE]+7>=Today(),
  [ANSWERED]=“Keep Praying”,
  [SCOPE_NO]<>20,
  ISNOTBLANK([REQUEST]),
  OR(
    LOOKUP(
      USEREMAIL(),
      Users,
      USER_EMAIL,
      Key_Prayer
    ),
    SWITCH(
      [SCOPE_NO],
      1,
        AND(
          IN(
            LOOKUP(
              USEREMAIL(),
              Users,
              USER_EMAIL,
              PI_NO
            ),
            {1, 3, 5}
          ),
          IN(
            ANY([Country_GEO_NO]),
            LOOKUP(
              USEREMAIL(),
              Users,
              USER_EMAIL,
              Country_GEO_NO
            )
          )
        ),
      2,
        AND(
          IN(
            LOOKUP(
              USEREMAIL(),
              Users,
              USER_EMAIL,
              PI_NO
            ),
            {3, 5,15}
          ),
          IN(
            ANY([Country_GEO_NO]),
            LOOKUP(
              USEREMAIL(),
              Users,
              USER_EMAIL,
              Country_GEO_NO
            )
          )
        ),
      3,
        AND(
          IN(
            LOOKUP(
              USEREMAIL(),
              Users,
              USER_EMAIL,
              PI_NO
            ),
            {1, 3, 5, 10, 12, 15, 20}
          ),
          [GEO_NO]
          =LOOKUP(
            USEREMAIL(),
            Users,
            USER_EMAIL,
            GEO_NO
          )
        ),
      4,
        AND(
          IN(
            LOOKUP(
              USEREMAIL(),
              Users,
              USER_EMAIL,
              PI_NO
            ),
            {1, 3, 5, 10, 12, 15, 20}
          ),
          [GEO_NO]
          =LOOKUP(
            USEREMAIL(),
            Users,
            USER_EMAIL,
            GEO_NO
          )
        ),
      5,
        AND(
          IN(
            LOOKUP(
              USEREMAIL(),
              Users,
              USER_EMAIL,
              PI_NO
            ),
            {10, 12}
          ),
          [GEO_NO]
          =LOOKUP(
            USEREMAIL(),
            Users,
            USER_EMAIL,
            GEO_NO
          )
        ),
      10,
        IN(
          [TEAM_NO],
          LOOKUP(
            USEREMAIL(),
            Users,
            USER_EMAIL,
            TEAM_NO
          )
        ),
      11,
        IN(
          [DPT_NO],
          LOOKUP(
            USEREMAIL(),
            Users,
            USER_EMAIL,
            DPT_NO
          )
        ),
      12,
        IN(
          LOOKUP(
            USEREMAIL(),
            Users,
            USER_EMAIL,
            PI_NO
          ),
          {15, 20, 22, 24, 26, 30, 40}
        ),
      13,
        OR(
          AND(
            IN(
              LOOKUP(
                USEREMAIL(),
                Users,
                USER_EMAIL,
                PI_NO
              ),
              {10, 12}
            ),
            [GEO_NO]
            =LOOKUP(
              USEREMAIL(),
              Users,
              USER_EMAIL,
              GEO_NO
            )
          ),
          IN(
            LOOKUP(
              USEREMAIL(),
              Users,
              USER_EMAIL,
              PI_NO
            ),
            {15, 20, 22, 24, 26, 30, 40}
          )
        ),
      15,
        IN(
          LOOKUP(
            USEREMAIL(),
            Users,
            USER_EMAIL,
            PI_NO
          ),
          {1,3,5,10,12,15,20,22,24,26,30,40}
        ),
      “”
    )
  )
)

[Steve (Steve Coile)]

If you created a slice called (e.g.) My User on Users with a row filter of:

(USEREMAIL() = [USER_EMAIL])

You could “simplify” your expression to be vastly more efficient:

AND(
  [START_DATE]<=Today(),
  [END_DATE]+7>=Today(),
  [ANSWERED]=“Keep Praying”,
  [SCOPE_NO]<>20,
  ISNOTBLANK([REQUEST]),
  OR(
    ANY(My User[Key_Prayer]),
    SWITCH(
      [SCOPE_NO],
      1,
        AND(
          IN(
            ANY(My User[PI_NO]),
            {1, 3, 5}
          ),
          IN(
            ANY([Country_GEO_NO]),
            ANY(My User[Country_GEO_NO])
          )
        ),
      2,
        AND(
          IN(
            ANY(My User[PI_NO]),
            {3, 5,15}
          ),
          IN(
            ANY([Country_GEO_NO]),
            ANY(My User[Country_GEO_NO])
          )
        ),
      3,
        AND(
          IN(
            ANY(My User[PI_NO]),
            {1, 3, 5, 10, 12, 15, 20}
          ),
          [GEO_NO]=ANY(My User[GEO_NO])
        ),
      4,
        AND(
          IN(
            ANY(My User[PI_NO]),
            {1, 3, 5, 10, 12, 15, 20}
          ),
          [GEO_NO]=ANY(My User[GEO_NO])
        ),
      5,
        AND(
          IN(
            ANY(My User[PI_NO]),
            {10, 12}
          ),
          [GEO_NO]=ANY(My User[GEO_NO])
        ),
      10,
        IN(
          [TEAM_NO],
          ANY(My User[TEAM_NO])
        ),
      11,
        IN(
          [DPT_NO],
          ANY(My User[DPT_NO])
        ),
      12,
        IN(
          ANY(My User[PI_NO]),
          {15, 20, 22, 24, 26, 30, 40}
        ),
      13,
        OR(
          AND(
            IN(
              ANY(My User[PI_NO]),
              {10, 12}
            ),
            [GEO_NO]=ANY(My User[GEO_NO])
          ),
          IN(
            ANY(My User[PI_NO]),
            {15, 20, 22, 24, 26, 30, 40}
          )
        ),
      15,
        IN(
          ANY(My User[PI_NO]),
          {1,3,5,10,12,15,20,22,24,26,30,40}
        ),
      ""
    )
  )
)

[Steve (Steve Coile)]

Have you tested this?

IN(
  ANY([Country_GEO_NO]),
  LOOKUP(
    USEREMAIL(),
    Users,
    USER_EMAIL,
    Country_GEO_NO
  )
)

In my experience, LOOKUP() produces a singular value. In my experience, if the column value looked-up is a List or EnumList, LOOKUP() will convert it to Text, which I would expect would prevent IN() from seeing it and searching it as a list.

I almost think you’ve got the arguments for IN() reversed: the first argument should be a singular value to be found (the “needle”) and the second the list n which to find it (the “haystack”), so I wonder if the expression should instead be:

IN(
  LOOKUP(
    USEREMAIL(),
    Users,
    USER_EMAIL,
    Country_GEO_NO
  ),
  [Country_GEO_NO],
)

[Lucinda_Mason]

Thanks. That got it working. But I did have to close the tab and reload the app to get the error to go away, even though the expression checker said it was fine.

@Steve, Now I have to look at all of your suggestions. Thanks for taking the time.

see: Editor error checking not working consistently

[GreenFlux (GreenFluxLLC)]

@Lucinda_Mason

Thanks. That got it working. But I did have to close the tab and reload the app to get the error to go away, even though the expression checker said it was fine.

I’ve been seeing this today, too. The editor is not showing errors until after a formula has been saved and synced. And the error remains after the cause is corrected, and takes a few more saves/syncs to catch up.

It seems like the ‘current’ version number is not in sync with the errors being reported.

[Steve (Steve Coile)]

Developers are aware of this lag.

[Kirk_Masden]

I had missed this thread but @tsuji_koichi pointed me to it. I wonder if this lag may not be related to what I experienced today:

Edits not "taking"

I’m having trouble with edits inexplicably not “taking.” What I mean is that changes I have made to the settings seem to be reflected in the editor and, initially at least, in the app, but after I save the changes they are no longer reflected in the app, though they are reflected in the actual settings I adjusted in the editor. I wrote about this in a comment here: After this, I noticed that I couldn’t make a new view show up as a menu view. Again, the change would be reflected in the app i…

[Aleksi (Aleksi Alkio)]

We have seen few clients yesterday and today seeing a not related error messages and our devs will investigating what that reason might be. It sounds a some kind of cache issue but let’s see.

[Austin_Lambeth]

I have been seeing some funny business with caching over the past couple days too. My full screen apps and the app on my phone doesn’t match the version that is on my editor and it is not updating to the newest version even after refreshing the tab and re syncing the app. Seems to be taking about 5-10 minutes or so for the saves from my editor to reach my app but only some times. (We do not have a stable version even set and I have my user as seeing the latest version anyway)

We’ve also had a separate issue with caching and images only showing in incognito windows but if you use incognito for too long they start to only show up when you go back to a normal window. This has been going on for a couple months that still has an ongoing ticket.

[tsuji_koichi]

Now probably I fully understand what you mean…
I change the app, add one column to a detail view. save the change.
Yes, indeed App editor indicate the new field is added to display in this detail view. However, on the running app, this is NOT displayed. Obvious bug.

@TDhers

[TDhers (Thierry D'Hers)]

Thank Koichi. I saw. Aleksi has the ball so I will let him run with it… No need to add another cook in that kitchen…
Thierry

[tsuji_koichi]

Dont worry, what I can do is to leave everything to the good hands of your team.

Just simply wait for a fix to come.

[James_McFarlane (James)]

Hi All,

For us it started in the weekend when I copied an app and got an error on a column of the newly created app. Tried to address error went as far as deleting the table and the error persisted.

Went to the original app the same error started showing (attached image).