show only active users

[Yan1]

Hello everyone, I hope you are well!

I'm improving an already developed Appsheet and came across the following scenario.

I need the appsheet to only bring me the email of the user who has the status of "Active". Example:

The users are in the (Collaborator) tab and I have the users

user01@domain.com (Active)
user02@domain.com(suspended)

The formula has to bring me only user01. How do I do?

I tried the Select and Filter formula but without success.

Hugs

[Izzat_Safrah]

Did you try this

SELECT( Table_A[Email],

AND(

[Email]=USEREMAIL(),

[Status]="Active"

))

[Aurelien]

Only the second part of the condition, I think 🙂

SELECT( Table_A[Email],
  [Status]="Active"
)

or, given the information provided by @Yan1:

FILTER("Collaborator", [status]="Active")

For reference:

FILTER() - AppSheet Help

[Yan1]

Hello

Hey guys,
I want to thank you in advance for your help!

I believe that both solutions can solve the problem, however, I am having difficulty inserting the formula, below I will provide more details.

I currently have this "Employee Email" field and this is where I need to bring only "Active" users. I have another formula here in this field, how can I add the Filter formula into this formula? Below is the current formula: SPLIT(SELECT(
Projeto[Owners],
[Id] = [_THISROW].[Id do projeto]
), ",")

Regards

[Aurelien · Accepted solution]

Hi @Yan1 

I see two options.

Option 1:

You may want to change the type of your column [Email do colaborador] from Text to Ref, then select the source table to your table "Employee". This assumes the email is the key-column of your table Colaborador.

Then use this expression in the Valid_If field:

INTERSECT(
  SPLIT([_THISROW].[Id do projeto].[Owners], ","),
  FILTER("Collaborator", [status]="Active")
)

Option 2 (easier from my perspective)

You may want to add an intermediate column virtual column [_projectOwners] with type Ref, source table Employee:

SPLIT([_THISROW].[Id do projeto].[Owners], ",")

Then, use this valid_if expression:

SELECT([_projectOwner][email],[status]="Active")

[Yan1]

Hi Aurelien,

Thanks for your help.

I tested the first solution and it already worked. Now only Active users are appearing and the "old" formula (SPLIT) continues to work.

Thanks again for your help.

Hugs