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

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}),

“”
)))

0 26 1,019
26 REPLIES 26

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?

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.

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!

thank you. Done.

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.

(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.

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

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.

Developers are aware of this lag.

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:

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.

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

Steve
Participant V

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
Participant V

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
Participant V

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],
)

Austin
Participant V

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.

TDhers
Participant V

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

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.

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).

I believe this error is caused by the recent changes to list arithmetic (more info on it here: Changes to SUM(), AVERAGE(), ABS(), and List arithmetic)

The error is because the expression is trying to add Lists of different types: List of Ref (on the left side of “-”) and List of List of Ref (on the right side of “-”).

Can you try removing the LIST() from the right side of “-” (in - LIST(SELECT(AGENDAMIENTO...))?)

I did, removed all the formulas. And I still getting the error message.

the formula:
3X_c_5_c56dd0c41a92a5dadcec6662a44ebf62ee5e89f5.png

With out the right LIST
3X_a_4_a4d41042326976a3d37d49fcee37f7eccf048cbe.png

3X_5_b_5b0369d7c65bcc329618bb8fa720cffc4dd129ad.png

Still getting the error message:

If you get the green checkmark in the Expression Assistant, hit save, but the red error shows up in the editor, then I assume it’s the same “lag” issue, in which case I suggest you try closing the tab and reloading the app to get the error to go away.

@natalie,

That did the trick. Thanks.

FYI we just deployed a fix for the error lag issue.

Thanks Natalie.

Top Labels in this Space