Text aggregation

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • In Progress
Hi , Could you please let me know how to aggregate the text field.

Column1 Column2          Column3
1              20                    20,30,20
1             30                    20,30,20 
1             20                     20,30,20
2             20                    20,25,30 
2             25                  20,25,30
2           30                      20,25,30


I am new to quickbase , Can you please explain this process in detail

Thanks
Photo of Pushpakumar Gnanadurai

Posted 7 months ago

  • 0
  • 1
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb
What does column 3 represent? How is it derived?
it is the text aggregation based on column 1 --- its kind of list aggregate function
Can some one help on this please
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
I don't think that any of us are really understanding the question.
let me re-phrase the question

I have two columns Dept and employee in the table dummy

Dept       employee
1              AA
1               BB
1               CC
2               DD
2              EE


Now I would like to do a text aggregation based on Dept. I expect the result in the new column as 

Dept       employee          text Aggregation
1              AA                     AA,BB,CC
1               BB                    AA,BB,CC
1               CC                    AA,BB,CC
2               DD                      DD,EE
2              EE                        DD,EE

In SQL , Something like 
select dept,listagg(employee,',')  within group (order by dept) from dummy
group by dept

will give an answer

1       AA,BB,CC
2        DD,EE

This is my expectation.

can you please let me know how to do it in quick base. Can you please let me know in detail that will be helpful. I am from SQL background.

Thanks
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

At first glance this appears to be a parent child relation between Department ( parent ) and Employee (child).

Department >> has many >> Employee

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Can you tell us how many child records would you want to float up the text to the parent record? 

This can be done via reverse relationships, for say up a half a dozen or so loops.  But if a Parent can have say 20+ children, at some point it will hurt the performance of the app to have so many looping reverse relationships.
There is no limitations to the child record. They can be N. Do you suggest a different approach to solve this ?

Thanks
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
There is is not currently a native functionality to float up a text value from a field in "N" child records. 

There are some non native techniques which I have seen posted on the forum which can be use to do the aggregation and display them on a record, but they will not actually be a field that could be used in a report, for example.  They would only exists when viewing a record.
Hi Thanks a lot for your answer. Is there a way to write a query ? to derive that value
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
I'm sure there is but i do not know how to do that.  Dan Diebolt would know how.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,884 Points 20k badge 2x thumb