Problem with filtering by user type in Slices

AppSheet AppSheet Q&A
,
1

Hi boys,

I need some help from you experts.

I should filter the reservations view of the EXCURSIONS table in a slice by user type and base.

users (super admin, admin) can see all reservations, while users (Prenotazioni and Skippe) should only see the reservations of the bases [BASE] that they select when registering. ex(san vito)

Schermata 2023-01-05 alle 18.21.32.png
Schermata 2023-01-05 alle 18.21.32.png2880×1800 574 KB

Schermata 2023-01-05 alle 18.02.08.png
Schermata 2023-01-05 alle 18.02.08.png2880×1800 536 KB

in the excursion tables there is the variable [BASE] which auto-fills according to the type of excursion

the [BASE] is the starting point of the excursion.

Schermata 2023-01-05 alle 18.23.36.png
Schermata 2023-01-05 alle 18.23.36.png2880×1800 649 KB

Schermata 2023-01-05 alle 18.24.28.png
Schermata 2023-01-05 alle 18.24.28.png2880×1800 403 KB

I tried with this code:

IF(

OR(

ANY(SELECT(UTENTI[TIPO UTENTE],[NOME UTENTE]=USERSETTINGS(“NOME UTENTE”)))=“SKIPPER”,

ANY(SELECT(UTENTI[TIPO UTENTE],[NOME UTENTE]=USERSETTINGS(“NOME UTENTE”)))=“PRENOTAZIONI”),

ANY(SELECT(ESCURSIONI[BASE],[BASE]=ANY(SELECT(UTENTI[BASE],[NOME UTENTE]=USERSETTINGS(“NOME UTENTE”))))),

ISNOTBLANK(ESCURSIONI[BASE])

)

I tried this code but it doesn’t work:

Schermata 2023-01-05 alle 18.19.29.png
Schermata 2023-01-05 alle 18.19.29.png2880×1800 633 KB

this code doesn’t work either:

ANY(SELECT(ESCURSIONI[BASE],AND(ANY(SELECT(UTENTI[BASE],[NOME UTENTE]=USERSETTINGS(“NOME UTENTE”)))=“SAN VITO”, [_THISROW].[BASE]=“SAN VITO”)))

can you help me?

2

I also tried this formula

IF(
ANY(SELECT(ESCURSIONI[BASE],[_THISROW].[BASE]=ANY(SELECT(UTENTI[BASE],[NOME UTENTE]=USERSETTINGS(“NOME UTENTE”))))),
ANY(SELECT(ESCURSIONI[BASE],[_THISROW].[BASE]=UTENTI[BASE])),
ISNOTBLANK(ESCURSIONI[BASE])
)

but I get this error: Cannot compare Text with List in ([_THISROW].[BASE] = UTENTI[BASE])

3

Just a rookie ,but :

As the red error says, you need to use a formula that returns True or False as a result . That formula will be applied to every row of the table , and if it returns true, it will show that row.

I’m still trying to understand your table so I can suggest a possible formula.

Rookie method : create (at least temporarily) a Virtual Column , and find a propper formula for this column to write True or False in it.

Then, in the slice formula, you will write :

[Virtual Column]=“true”

1 Like
4

Ifs(

Or(Usersettings(tipo utente)=“super admin”;Usersettings(tipo utente)=“admin”); True;

Or(Usersettings(nome utente)=“Prenotazioni”;Usersettings(nome utente)=“Skippe”); in([base];usersettings(base) );

)

1 Like
5

Thanks for the help, in the excursions table there are several excursions with different starting points variable EXCURSION [BASE].

for Skipper and Reservations users can select the base they can view in the table USERS[BASE] which has the same values as EXCURSIONS[BASE].

I should filter EXCURSION[BASE] when USER[BASE]= EXCURSION[BASE]

I hope I explained myself

6

Utenti is the User table, and has security filters set to only load one row, the one of the current user of the app?

Or do you have the [base] also selectable in the Usersettings?

7

yes [base]is selectable in the table user not in usersettings

8

[Base]= any(select(utenti[base];[nome utente]=usersettings (“nome utente”)))

9

ok i have to get ESCURSIONI[BASE] when same base is present in usertable as USERS[BASE]

10

I solved with this formula, but now I have another problem if the user must be able to see more than one base the formula makes me see only one. how can I do?

IF(
ANY(SELECT(UTENTI[BASE TRAPANI],[NOME UTENTE]=USERSETTINGS(“NOME UTENTE”)))=“SI”,
[BASE]=“TRAPANI”,
IF(
ANY(SELECT(UTENTI[BASE SAN VITO],[NOME UTENTE]=USERSETTINGS(“NOME UTENTE”)))=“SI”,
[BASE]=“SAN VITO”,
IF(
ANY(SELECT(UTENTI[BASE CASTELLAMMARE],[NOME UTENTE]=USERSETTINGS(“NOME UTENTE”)))=“SI”,
[BASE]=“CASTELLAMMARE”,
ISNOTBLANK(ESCURSIONI[ID PRENOTAZIONE])
)
)
)

11

Here’s your most recent expression, formatted to my preferences:

IF(
  (
    ANY(
      SELECT(
        UTENTI[BASE TRAPANI],
        ([NOME UTENTE] = USERSETTINGS("NOME UTENTE"))
      )
    )
    = "SI"
  ),
  ([BASE] = "TRAPANI"),
  IF(
    (
      ANY(
        SELECT(
          UTENTI[BASE SAN VITO],
          ([NOME UTENTE] = USERSETTINGS("NOME UTENTE"))
        )
      )
      = "SI"
    ),
    ([BASE] = "SAN VITO"),
    IF(
      (
        ANY(
          SELECT(
            UTENTI[BASE CASTELLAMMARE],
            ([NOME UTENTE] = USERSETTINGS("NOME UTENTE"))
          )
        )
        = "SI"
      ),
      ([BASE] = "CASTELLAMMARE"),
      ISNOTBLANK(ESCURSIONI[ID PRENOTAZIONE])
    )
  )
)

If the BASE TRAPANI, BASE SAN VITO, and BASE CASTELLAMMARE columns are of type Yes/No, you need not compare their values to “SI”, but can instead use them directly:

IF(
  ANY(
    SELECT(
      UTENTI[BASE TRAPANI],
      ([NOME UTENTE] = USERSETTINGS("NOME UTENTE"))
    )
  ),
  ([BASE] = "TRAPANI"),
  IF(
    ANY(
      SELECT(
        UTENTI[BASE SAN VITO],
        ([NOME UTENTE] = USERSETTINGS("NOME UTENTE"))
      )
    ),
    ([BASE] = "SAN VITO"),
    IF(
      ANY(
        SELECT(
          UTENTI[BASE CASTELLAMMARE],
          ([NOME UTENTE] = USERSETTINGS("NOME UTENTE"))
      ),
      ([BASE] = "CASTELLAMMARE"),
      ISNOTBLANK(ESCURSIONI[ID PRENOTAZIONE])
    )
  )
)

Rather than IF() expressions within IF() expressions, you could instead use IFS():

IFS(
  ANY(
    SELECT(
      UTENTI[BASE TRAPANI],
      ([NOME UTENTE] = USERSETTINGS("NOME UTENTE"))
    )
  ),
    ([BASE] = "TRAPANI"),
  ANY(
    SELECT(
      UTENTI[BASE SAN VITO],
      ([NOME UTENTE] = USERSETTINGS("NOME UTENTE"))
    )
  ),
    ([BASE] = "SAN VITO"),
  ANY(
    SELECT(
      UTENTI[BASE CASTELLAMMARE],
      ([NOME UTENTE] = USERSETTINGS("NOME UTENTE"))
  ),
    ([BASE] = "CASTELLAMMARE"),
  TRUE,
    ISNOTBLANK(ESCURSIONI[ID PRENOTAZIONE])
)

We can improve performance by swapping the order of some comparisons so that the SELECT() expression is only performed if [BASE] matches:

IFS(
  ([BASE] = "TRAPANI"),
    ANY(
      SELECT(
        UTENTI[BASE TRAPANI],
        ([NOME UTENTE] = USERSETTINGS("NOME UTENTE"))
      )
    ),
  ([BASE] = "SAN VITO"),
    ANY(
      SELECT(
        UTENTI[BASE SAN VITO],
        ([NOME UTENTE] = USERSETTINGS("NOME UTENTE"))
      )
    ),
  ([BASE] = "CASTELLAMMARE"),
    ANY(
      SELECT(
        UTENTI[BASE CASTELLAMMARE],
        ([NOME UTENTE] = USERSETTINGS("NOME UTENTE"))
    ),
  TRUE,
    ISNOTBLANK(ESCURSIONI[ID PRENOTAZIONE])
)

Because all but one of the IFS() tests is matching [BASE], we could use SWITCH() instead of IFS() for a (trivial) performance boost and to improve readability of the expression:

SWITCH(
  [BASE],
  "TRAPANI",
    ANY(
      SELECT(
        UTENTI[BASE TRAPANI],
        ([NOME UTENTE] = USERSETTINGS("NOME UTENTE"))
      )
    ),
  "SAN VITO",
    ANY(
      SELECT(
        UTENTI[BASE SAN VITO],
        ([NOME UTENTE] = USERSETTINGS("NOME UTENTE"))
      )
    ),
  "CASTELLAMMARE",
    ANY(
      SELECT(
        UTENTI[BASE CASTELLAMMARE],
        ([NOME UTENTE] = USERSETTINGS("NOME UTENTE"))
    ),
  ISNOTBLANK(ESCURSIONI[ID PRENOTAZIONE])
)

Looking back at the exchange with @OptimiX_XcrY , it appears your use of the BASE TRAPANI, BASE SAN VITO, and BASE CASTELLAMMARE are probably unnecessary. I suspect we could actually replace even the SWITCH() expression with a much simpler OR():

OR(
  ISNOTBLANK(
    FILTER(
      "UTENTI",
      AND(
        ([_THISROW].[BASE] = [BASE]),
        ([NOME UTENTE] = USERSETTINGS("NOME UTENTE"))
      )
    )
  ),
  ISNOTBLANK(ESCURSIONI[ID PRENOTAZIONE])
)