Summary data in virtual column

[Jim]

I am trying to set up some summary information and can’t seem to get past the “Cannot compare List with Text”. This is how the data is structured.

1. I have a list of gliders in a list spreadsheet. Let’s call the gliders A, B, C. This data is not the key of the table. I have a uniqueid for that.
2. I have a glider log spreadsheet that has a lookup to the list spreadsheet in order to fill out the glider identifier and then the user fills out other information (pilot, flight hrs, etc.)
3. For reporting purposes I want a virtual column in the list spreadsheet to sum the count and flight hours from the log so that when I view the list I will see this information for each glider in the list. I also plan to use a date filter on the log but I think I can add that later if I can get the proper statement to do the summary.

Here is a statement I have tried. Glider is the name of glider column from the list spreadsheet where I want the summary data. The summary will be a virtual column. Bluebook log is the spreadsheet with Aircraft column having the name of the glider that the particular row refers to.

COUNT(SELECT(Bluebook log[Aircraft],(Bluebook log[Aircraft] = [_THISROW].[Gliders])))

Here is the error

Cannot compare List with Text in (BLUEBOOK LOG[Aircraft] = [Key].[Gliders])

Can I get some advice on how to properly do this?

[Steve]

Bluebook log[Aircraft] is a column reference that produces the list of the values in the Aircraft column of the Bluebook log table. This is the “List” the error refers to. The expression, (Bluebook log[Aircraft] = [_THISROW].[Gliders]), attempts to compare that list of values to a single Text value from the Gliders column of the current row.

What I suspect want is to compare the Gliders value with the Aircraft column value of each row in the Bluebook log table. To do that, omit the table name with the Aircraft column value reference: ([Aircraft] = [_THISROW].[Gliders]). This makes you COUNT() expression:

COUNT(SELECT(Bluebook log[Aircraft],([Aircraft] = [_THISROW].[Gliders])))

See also:

help.appsheet.com

Column Value Expressions

Write an expression to get a value from a specific column and row.

help.appsheet.com

List Expressions and Aggregates

List expressions use operators that result in a list or numeric value.

help.appsheet.com

SELECT()

Gather column values from row in table or slice

[Jim]

That was easy. Works like a charm. Thanks!