LinkedIn
Twitter
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!
Solved
1 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.
