Count and group with a ref

[VictorB]

Hi every one, 

I'm a French novice on Appsheet, I'm struggling with a formula. 

I try to count the number of elements that match a criteria (yes or no) and to organise them by group. 

I have tow Tables : Data_light_spot and Groups with a reference between them.

Data_light_spot : 

Group, Number, Working

AA, 01, Yes

AA, 02, Yes

AA, 02, No

AB, 01, Yes

AB, 02 No

....

Groups :

Group, How many (total), How many working

AA, 3, 2

AB, 2, 1

...

I've first use the virtual row Related Data_light_spot with count()  to find out the value of the column How many (total).

But I can't find a way to count the numbers of "Yes" from the table Data_light_spot and to "group" them into the table Groups. 

hanks for the help

[Joseph_Seddik]

Salut Victor y bienvenu dans la communauté !

There are two ways:

1. You can have a virtual column with the following App Formula:
   
   COUNT(
     SELECT(Data_Light_Spot[Working], AND(
       [Working] = "Yes", 
       [Group] = [_ThisRow].[Group]
     ))
   )
   
   
2. or better, to avoid using SELECT in a virtual column, you can have your "Working" column type as Enum, base type Number, with Allowed values: 0 and 1. In this case, the expression above will simply be:
   
   SUM([Related Data_Light_Spot][Working])
   
   

Relevant readings:

• SELECT() | AppSheet Help Center
• List Dereference | AppSheet Help Center 

[VictorB]

Thanks a lot for your reply, I think we'r very close to solve this. 

Now I have this error : Cannot compare List with Text. 

My column from the table Groups is type Text and the one from the table Data_light_spot is type Ref. 

[Joseph_Seddik]

Please show the expression you are using. Thank you!

[VictorB]

COUNT(
SELECT(data_points_lumineux[FONCTIONNEMENT], AND(
data_points_lumineux[FONCTIONNEMENT] = "Oui",
data_points_lumineux[groupe] = [_ThisRow].[Groupe (cmd)]
))
)

Cannot compare List with Text in (data_points_lumineux[FONCTIONNEMENT] = "Oui")

Is that what you wanted ? 

[Joseph_Seddik · Accepted solution]

COUNT(
  SELECT(data_points_lumineux[Fonctionnement], AND(
    [Fonctionnement] = "Oui", 
    [Groupe] = [_ThisRow].[Groupe (cmd)]
  ))
)

Please read:

• SELECT() | AppSheet Help Center

[VictorB]

Thanks a lot !