Counter of inline view doesn't work for more than 1 inline row

[Hansibar]

General

In the Detail view of a parent row the related child rows are shown inline. On top of that inline view a counter for the related child rows is shown.

Problem

In my setting, the counter only works if there is only one related child row. If there are more child rows, the counter is 0 although all child rows are listed in inline view.

Also, if I refer to the related child rows in a Google Docs template with <<Start:[Related child rows]>>... this works only if there is only one child row. Otherwise nothing is printed.

Background

The related child rows are not accessed directly, but through an auxiliary column in a table that collects information from other tables. That means, it is not the virtual column with the REF_ROWS expression I refer to. I rather have a virtual column in another table, that refers to the REF_ROWS column [Related Childs]:

SELECT(Child[ID];IN([ID];SELECT(Parent[Related Childs];AND([colum1]=[_THISROW].[column1];[column2]="text"))))

The inner SELECT expression already gets the list of IDs, but these IDs are not from the “original” table. So the outer SELECT expression matches them with the IDs from the original table to populate the inline view.

I hope this is not too abstract. I’m happy to explain the whole use case, but it seemed to me that there would be too much noise around the actual problem.

Question

My question is whether my approach is valid - and therefore the counter and the template generator have a bug - or invalid. And if invalid, how a valid approach would look like. Thank you for your support!

[Suvrutt_Gurjar]

---

@Hansibar wrote:

SELECT(Child[ID];IN([ID];SELECT(Parent[Related Childs];AND([colum1]=[_THISROW].[column1];[column2]="text"))))

---

Your exact table relationship is not clear.  But assuming you are referring only one "Parent" and one "Child" table in the relationship, and the expression is used in a VC in the Parent table, the above expression could be simplified as 

SELECT([Related Childs][ID] , AND([colum1]=[_THISROW].[column1] , [column2]="text"))

Where [ID] is the key column of the  "Child" table.

Edit: For your existing expression, you could possibly try

SELECT(Child[ID];IN([ID]; SPLIT(TEXT(SELECT(Parent[Related Childs];AND([colum1]=[_THISROW].[column1];[column2]="text"))), " , ")))

[Hansibar]

Thank you for your reply!

I've tried your suggestions but could't get them to work. And I think you are right, I didn't explain the table structure properly: I have a parent table "Checklistitem" that has two child tables "Text" and "Doc":

I now would like to display the relatetd Texts in a Doc View and also use them in a PDF created from a Doc row.

The expression in the original post was the expression of a virtual column in Doc trying to achieve that, reformulated:

SELECT(Text[ID];IN([ID];SELECT(Checklistitem[Related Texts];...

Thinking about it I'm wondering why just [Checklistitem].[Related Ref_Texts] in a virtual column of Doc doesn't work (I've tried and it doesn't).

[Suvrutt_Gurjar]

Thank you for the updates. 

---

@Hansibar wrote:

Thinking about it I'm wondering why just [Checklistitem].[Related Ref_Texts] in a virtual column of Doc doesn't work (I've tried and it doesn't).

---

I believe you are correct. My testing showed that this expression very much works. Please see the GIF below that shows the related texts in the Docs table detail view and the expression for the related texts.

[Hansibar]

Thank you very much for your swift reply, Suvrutt_Gurjar!

Since it didn't work in my app I also started to build a testing app and as it shows I didn't explain the table structure correctly. Sorry! However, I can also reproduce the error that I encountered originally.

Here is the testing app: https://www.appsheet.com/Template/AppDef?appName=ShowChildInOtherChild-217170355&utm_source=share_ap...

And this is the table structure:

• A Checklist can hold multiple Checklistitems. 
• Each Checklistitem originates from a Template and has therefore a Template_ID.
• Each Checklistitem has a certain type that determines what information is stored. The testing app knows two types: Text and Doc.
• Depending on the Type you can either add Text or Doc as related rows (done by Show if expressions, not implemented in the testing app)

The aim is to access such Texts from a Doc view,

• whose Checklistitem parent belongs to the same Checklist as the Checklistitem parent of the Doc row and
• whose Checklistitem parent has a certain Template_ID.

I've tried to achieve that with the virtual column VC Checklist-Related Texts in the Doc table:

ANY(SELECT(Checklistitem[Related Texts];
    AND([Checklist]=[_THISROW].[Checklistitem].[Checklist];
        [Template_ID]="A")))

And it actually shows the Related Text in an inline list. However, the counter is incorrect; it shows "1" although there are 4 related rows:

Actually, the counter is not very important for me. But I also want to create a PDF from a Doc row (see the bot in the testing app) and it seems as if the root cause of the coutner bug (if it is a bug) is also influencing the PDF generation:

If I use this expression in the template:

<<Start:[VC Checklist-Related Texts]>><<[Subject]>><<End>>

the output is only the Subject of the first Checklist-Related Text, so only 1 related row.

Finally, that seems to be the correct description of what is happening. Sorry for the many steps it took to get here.

[Suvrutt_Gurjar · Accepted solution]

---

@Hansibar wrote:

I've tried to achieve that with the virtual column VC Checklist-Related Texts in the Doc table:

ANY(SELECT(Checklistitem[Related Texts];
    AND([Checklist]=[_THISROW].[Checklistitem].[Checklist];
        [Template_ID]="A")))

---

---

Thank you for more details. I think you need to try the below expression in the Doc table's VC. Wrapping it with ANY() will produce just one item.

SPLIT(TEXT(SELECT(Checklistitem[Related Texts];
AND([Checklist]=[_THISROW].[Checklistitem].[Checklist];
[Template_ID]="A"))), " , ")

The above column needs to be of list type with element type as "ref" and should reference the "Text" table" as shown below. ( My "Text" table name is "Texts_Test") 

Hope this helps. SELECT() on [Related Texts] creates a list of lists. So we need to use SPLIT() to flatten the list of lists.

Hope this helps. I was able to successfully pull the [related Texts based on template ID and the grandparent checklist item.

[Hansibar]

Thank you for your reply! And sorry for the unnecessary steps that it took me to get here.

Your solution works fine, the counter and the PDF creation now work as expected. Thank you also for the explanation!

[Suvrutt_Gurjar]

You are welcome. I have a feeling that the working expression can be further simplified by using dereference expressions instead of a pure SELECT() that can be sync time expensive.

I will post just in case I can successfully test a leaner expression.