Expression to reference a column with a virtual column

AppSheet AppSheet Q&A
,
1

Hi, I have a virtual column that references a slice of a table. The virtual column (virtualCol) contains a every column from the slice to include col1,col2, col3 and colN.
I am trying to create an expression that counts all the time colN contains the value “true” (for each unique ID on [_ThisRow]).

I’ve just not found a way to reference colN.

2

Hi @seyiosinowo

Please post in Q&A board next time. Tips & Tricks is for sharing tips to other community members :slightly_smiling_face:

Let’s say your virtual column is named [_relatedSliceRows].

Then, a related list of values in an attached column will be:

[_relatedSliceRows][expectedColumnName]

Then, the count of TRUE in it will be:

COUNT(
  SELECT([_relatedSliceRows][id-column],
         [colN]=TRUE
  )
)

For reference:

Build list dereferences - AppSheet Help

SELECT() - AppSheet Help

3 Likes
3

Thanks for the correction. Apologies for the mistake. I thought it was for asking questions related to tips and tricks (don’t know why I translated it that way).

Thanks for the approach. But I am still not clear. “[_relatedSliceRows][expectedColumnName]”. What is “[expectedColumnName]”? And shouldn’t the TRUE be in quotes as “TRUE”?

4

“expectedColumnName” would be “ColN” for you :slightly_smiling_face:

No quotes for TRUE! Otherwise it will be treated as a text and not the actual True/False boolean :slightly_smiling_face:

No worry, it’s a common mistake. I have asked to the moderation team to report an improvement to the dev team, but it got lost on the way.

@lizlynch , as this happen quite often, is it possible to push a message on the “new discussion form”, saying that the Tips & Tricks for is only for sharing tips and not asking for help? Thanks for considering!

5 Likes
5

Thanks so much. It still doesn’t achieve the desire result. This is the formula I am trying to work with.

I have a bunch of logging status that show up in a virtual column. This login statuses are displayed on a view. I want a situation that if one of the login status has “login failed”, the column will display “login failed”.

This is the expression I put together. But it fails to identify any instance where login has failed EVEN when all login failed.

IF(
  AND(
    COUNT(SELECT([user_data][login_status], [login_status] = "Login Failed", [project_id] = [_THISROW].[project_id])) > 0,
    [project_id] = [_THISROW].[project_id]
  ),
  "login failed",
  "login successful"
)

Any advice? Thanks so much. Truly truly appreciate this.

6

Can you try:

IF(
  IN("Login Failed", [Related PROJECTs][login_status])
  "login failed",
  "login successful"
)
7

@Michelle - See note about about messaging for “Tips and Tricks”. Any suggestions for conveying that inquiries should be posted on Q&A? Thank you!

2 Likes
8

This?

(
  0
  + IFS(([col1] = "true"), 1)
  + IFS(([col2] = "true"), 1)
  ...
  + IFS(([colN] = "true"), 1)
)
2 Likes
9

Thank folks. On a lighter note, this is testament to how difficult it would be for AI to replace human engineers. Spent days tweaking prompt and the AI platforms used couldn’t resolve. :grinning_face:

2 Likes