Select value from another table with a specific condition

[DaGi07]

Hello to everyone,
I’m working on app of training course for my company and i have to create a UX where are showed only the employers who have the role of health and safety manager.

I tried with this formula in order to select from the table “CORSI”, the list of values of column [DIPENDENTE] where the value of column CORSO is equal to “RAPPRESENTANTE DEI LAVORATORI PER LA SICUREZZA”. But in the column DIPENDENTE is type ref from an other table “DIPENDENTI” where is also a column “STATO” referred to employers in type enum (enabled or disabled). With this formula I would see only the employers that are also Enabled in the table “DIPENDENTI”.
This is the structure of my app:

The table INFORMAZIONI is where I’m setting this formula

Thanks to all who will help me

[Steve]

Maybe this:

SELECT(
  CORSI[DIPENDENTE],
  AND(
    (“RAPPRESENTANTE DEI LAVORATORI PER LA SICUREZZA” = [CORSO]),
    ("Enabled" = [DIPENDENTE].[STATO])
  )
)

See also:

help.appsheet.com

AND()

Are all true?

help.appsheet.com

Dereference Expressions

Use a dereference expression to get a column value from the referenced row.

[DaGi07 · Accepted solution]

Steve:

SELECT(
  CORSI[DIPENDENTE],
  AND(
    (“RAPPRESENTANTE DEI LAVORATORI PER LA SICUREZZA” = [CORSO]),
    ("Enabled" = [DIPENDENTE].[STATO])
  )
)

TKS Steve, you are ever so precious!

[DaGi07]

@Steve I have last question to make perfect this section of my app.
I have two company “Arancia-ICT” and “Koros Consulting”, as you can see in the following printscreen, the filed on the top [Azienda] is Arancia-ICT and i would see in the nested tables, only the employer of Arancia-ICT company and not Koros Consulting like in this screen.

On the contrary, If i go in the next slide clicking on the side arrow, I would see only the employer of the company Koros Consulting and in this case is right

How can i set this condition depending on the name of the company?
Tks a lot!

[DaGi07]

I tired with this formula but it doesn’t work
SELECT(
CORSI[DIPENDENTE],
AND(“RAPPRESENTANTE DEI LAVORATORI PER LA SICUREZZA” = [CORSO],[SCADENZA]>TODAY(),[AZIENDA]=[_THISROW],
( [DIPENDENTE].[STATO]=“Attivo”)
)
)

[DaGi07]

I solved whit this Formula:
SELECT(
CORSI[DIPENDENTE],
AND(“RAPPRESENTANTE DEI LAVORATORI PER LA SICUREZZA” = [CORSO],[SCADENZA]>TODAY(),[AZIENDA]=[_THISROW].[AZIENDA],
( [DIPENDENTE].[STATO]=“Attivo”)
)
)

[Steve]

Very well done!