Virtual Column not computing a formula result.

[AJRIGO]

Hi, need help from you experts.

I am facing an issue as follows: I have a virtual column with a very simple count() formula. When I test it in the "Expression Assistant" it is working normally and it calculates the right result, but when it is shown in the view the column result is 0 (zero). 

It is weird because in the expression assistant it is correct and valid, the test works perfect, but looks like something during the table view to the user is avoiding the calculation of that formula. 

Any idea about what I need to do here? Thanks in advance.

[1minManager]

So virtual columns can act in 2 ways.  When your looking at the result in a form view, or likely also the expression assistant, you're getting the result as or exactly now.  But if your looking at the result in a detail view or table view then your seeing the result at the last sync.

Hope this helps.  If not post the formula and some screenshots

Simon@1minManager.com

[AJRIGO]

Helo Simon, thanks for you quick reply. I started using appsheet very recently, so I am not sure I got what you mean. 

Basically what I am trying to do is: I have 2 tables, one is a "pipeline" as parent, and other table is the list of candidates per pipeline, as a child table. In the parent table view I wanna show the number of total candidates per each role (pipeline), and the number of rejected candidates. 

So my formula is simple: COUNT( [_THISROW].[CANDIDATOS] ) to count the number of  total candidates. [CANDIDATOS] is the column ref (REF_ROWS) to the Child Table. So when I display Parent View I have all candidates in the [_THISROW].[CANDIDATES].  This works perfectly with the right resul in the Expression Assistant. 

However, as you can see below, when I display view it comes with zeros.

I tried to have a regular column (not virtual one) in my spreadsheet, and then to use SELECT(CHILD[CANDIDATES], [ID] = [_THISROW].[ID]) for instance, did not work, so I removed that column from spreadsheet and  tried with a Virtual Column, even so I am facing this issue. 

Thank you very for your support. Kind regards.

[Steve]

You don't need and should not include the dereference of [_THISROW] in your expression.

Please post a screenshot of the configuration screen of the REPROVADOS column.

[SkrOYC]

---

@AJRIGO wrote:

So my formula is simple: COUNT( [_THISROW].[CANDIDATOS] ) to count the number of  total candidates. [CANDIDATOS] is the column ref (REF_ROWS) to the Child Table.

---

BTW, just to prevent future confusion about the platform. The column whose AppFormula looks like REF_ROWS() and apparently you have changed it's name (I don't recommend that, change the Display Name instead) is not a Ref column, it's a List column. The Ref column is on the children, not the parent. It's what is called as "Foreign key".

List columns have all of the children key values, so it's a list of key values and AppSheet then can display those records as an Inline view.

From the screenshot you provided it seems like Cantidatos have 0 children so your count is working fine.

Also, as already mentioned by @Steve, [_THISROW] is not needed in this case. This should be enough:

COUNT([CANTIDATOS])

[AJRIGO]

Thank you all, @1minManager  @SkrOYC  @Steve (read a lot your material, very helpful). 

My challenge is to count only the records (in the child, which are shown in the parent) that have some specific status. 

Now it works if I use formula COUNT( [CANDIDATOS] ), but as you can see the column REPROVADOS has the same number of column CANDIDATOS which is the total. What I need to understand is how to get only the number of candidates that are assigned to status rejected.

Parent Table: PIPELINE: 

Column CANDIDATOS is a type LIST with REF_ROWS to Child Table RECRUTAMENTO:

Child Table: RECRUTAMENTO

Column OPORTUNIDADE is a REF to table PIPELINE.

Column STATUS in the child table is a type ENUM with a list of values:

Hope this screenshots above can help to understand what I am trying to do. 

Thank you very much. 

[Steve]

Please post a screenshot of the configuration of the OPORTUNIDADE column of the RECRUTAMENTO table down to and including the Type Details section.

[AJRIGO]

Here it is: 

[1minManager]

When you go into a Pipeline record and look at the [CANDIDATOS] Ref column, does it show a number of subtable records there?

A bit of a guess but also try  -  COUNT([CANDIDATOS] [Subtable Key Column Name])

[AJRIGO · Accepted solution]

Finally found the solution. What made it works was the following:

I kept a virtual column in the Pipeline table, and the APP FORMULA = 

COUNT( FILTER("RECRUTAMENTO", AND(([OPORTUNIDADE] = [_THISROW].[OPORTUNIDADE]), ([STATUS] = "Status 2") ) ) )
+
COUNT( FILTER("RECRUTAMENTO", AND(([OPORTUNIDADE] = [_THISROW].[OPORTUNIDADE]), ([STATUS] = "Status 4") ) ) )
+
COUNT( FILTER("RECRUTAMENTO", AND(([OPORTUNIDADE] = [_THISROW].[OPORTUNIDADE]), ([STATUS] = "Status 7") ) ) )

[VinuWijemanne]

What was the underlying issue here? I have the sam problem with a simple multiplication forumula on a virtual column.