How can I concatenate a List of Child records

[Jared12345]

Hi community.

Thought I'd asked this question earlier today but it doesn't seem like it went through.

I have a VC column on my child table that puts together a human readable sentence.

It says something like

concatenate ("We bought ", [Qty]," of ",[Product]," @ ",[Price])

I want to then group these child records into a VC column on the parent table, and have a line break separation between each sentence but I'm not sure how I can achieve this. 

Can somebody please advise me. 

All my attempts have ended in failure.

[Suvrutt_Gurjar]

May we know how many child records will be there? Meaning if there are handful of child records(5-7) , we can construct an expression. The expression will be longish though.

[Jared12345]

Thanks for responding. 

Its not possible to say with certainty but I don't think more than 10. 

[Suvrutt_Gurjar]

Thank you. For 10 children, the basic expression can be something like below.

[Related Children] is the system generated rev ref column in the parent table and [Buy Details] is the column you have concatenated in the child table to make a human readable sentence.

The expression can be further improved to at least construct a list without a line break when children are more than 10 and adding bullet point before each sentence and so on.

CONCATENATE(

IFS(COUNT([Related Children])>=1 ,INDEX([Related Children][Buy Details],1)),"
",IFS(COUNT([Related Children])>=2 ,INDEX([Related Children][Buy Details],2)),"
",IFS(COUNT([Related Children])>=3 ,INDEX([Related Children][Buy Details],3)),"
", IFS(COUNT([Related Children])>=4,INDEX([Related Children][Buy Details],4)),"
",IFS(COUNT([Related Children])>=5 ,INDEX([Related Children][Buy Details],5)),"
",IFS(COUNT([Related Children])>=6 ,INDEX([Related Children][Buy Details],6)),"
",IFS(COUNT([Related Children])>=7 ,INDEX([Related Children][Buy Details],7)),"
",IFS(COUNT([Related Children])>=8 ,INDEX([Related Children][Buy Details],8)),"
",IFS(COUNT([Related Children])>=9 ,INDEX([Related Children][Buy Details],9)),"
",IFS(COUNT([Related Children])>=10 ,INDEX([Related Children][Buy Details],10))

)

[Jared12345]

Thank you @Suvrutt_Gurjar . As matter of interest, if the upper limit of child records increases would it be best practice to add additional IFS. I just went back to the supplier records and have found one outlier supplier that they buy 56 items from. Its highly unlikely that all the items would be bought at the same time but what could be done other than extending the expression you provided above?

[Suvrutt_Gurjar]

As mentioned , the expression will revert to a simple list ( without link breaks) when children are more than 10.

I am posting one more approach soon after testing. If testing goes well , it will work for any number of records, with a bit of different look and feel, of course.

[Suvrutt_Gurjar]

Another alternative is to simply use a child table's  related view in the parent table with a single column of [Buy Details] in the view. In general, it makes more sense to show the human readable column [Buy Details] from the child table itself as a related view , instead of taking it to the parent as a list and then again splitting the list there.

The approach is as below:

1. Please create a slice called say "Buy Details" on the child table.

2. Then create a rev reference column in the parent table based on this slice with an expression such as REF_ROWS("Buy Details", "Parent Table Key")

3. Create a table view or card view as shown below based on the slice "Buy Details" and select/ show only one column in the table view and card view. 

Images below

Table View the [But Details] from child table shows in a single column related table view

The card view with a single setting of the [Buy Details] column as shown below

Then you can have as many children [Buy details] as you need. Hope this approach is more elegant and flexible.