How to use JavaScript to concat child records into a single field?

  • 1
  • 3
  • Question
  • Updated 7 months ago
  • Answered

I'm trying to learn how to concatenate multiple child records into a single field on a report. I have spent a lot of time searching google and the help forums and have come up with nothing. I don't want to use reverse look up because there could be a lot of records I'm returning or only 1. I figured the best way to accomplish this would be JavaScript. Maybe some tutorials on how to implement JavaScript functionality with QuickBase would help.

Photo of Justin

Justin

  • 10 Points

Posted 3 years ago

  • 1
  • 3
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
UPDATED NEW AND IMPROVED: I have updated this example with more generic code where all the parameters are floated to the top and a template is used to define how to summarize child records to the parent field in the report (and I am not through refactoring yet):

  //parameters:
  var dbid = "bkgzxdr4x";
  var dbidParents = "bkgzxdr6t";
  var dbidChilds = "bkgzxfptb";
  var apptoken = "q2aznmd9h7eymbwavpzjbsc8hcf";
  var n = 100;
  var relatedParentFid = "7";
  var relatedParentLabel = "related_parent";
  var clist = "3.7.10";
  var qbuClass = "QBU_Childs";
  var template = "<ul>{{#.}}<li>{{comment}}</li>{{/.}}</ul>";   


Concatenate Children To Parent ~ ListAll2
https://haversineconsulting.quickbase.com/db/bkgzxdr6t?a=q&qid=5

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=464

More to come ...
Photo of Gaurav Sharma

Gaurav Sharma

  • 6,024 Points 5k badge 2x thumb
Great :) I was looking for the same thing. Finally, I found it.

Thanks,
Gaurav
Photo of Harrison Hersch2

Harrison Hersch2

  • 400 Points 250 badge 2x thumb
Hi Dan. Do you happen to have a sample of your new solution combined with the comma separated approach in the original, vs the HTML lists?
Photo of Paul

Paul, Champion

  • 120 Points 100 badge 2x thumb
Good Morning Everyone

At the risk of sounding silly I have a question

This code does exactly what I need, I created a code page called concatenate.js but I do not know how to call it within the application, in the demo the data appeared in a field called childs is this done with the image onload technique? or am I missing something really obvious.



Many thanks



paul
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Yes it uses the IOL technique. To implement follow the link in the pastie for step by step instructions.
Photo of Stanley

Stanley

  • 160 Points 100 badge 2x thumb
Trying to implement and can get an alert to show up, but not the concat code to show up.  I updated all variables.  I didn't put in underscores in field names since I don't use them.  I changed field FIDs to 3.6.9.  But can't get anything to load.  Any help?
Photo of Richie Eyles

Richie Eyles

  • 92 Points 75 badge 2x thumb
working hard to implement this without luck yet...

Can anyone tell me what these fields are?
var clist = "3.7.10";

Is this from the child table?
var relatedParentFid = "7";
Photo of Gaurav Sharma

Gaurav Sharma

  • 6,024 Points 5k badge 2x thumb
Hi Richie,

clist simply represents the column list and these all fields are from the child table I guess.

3 normally represents the Record ID# in every table, 7 is the related parent fid that you can replace based on your table and 10 is the field Id of that field which you want to include in the results.

Thanks,
Gaurav
Photo of Dan

Dan

  • 1,452 Points 1k badge 2x thumb
Any ideas on how to make the concatenated value exportable? When I export to CSV, I'm seeing the content of the IOL field, rather than the concatenated child values.

Thanks!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
The original example "Concatenate Children To Parent" performed the concatenation client-side (ie in your browser) as opposed to formula fields which are evaluated server-side. So while the example tossed the concatenated values into a cell of the table report and appear next to normal fields they have a different origin and you should not expect the concatenated values to export or print.

To get the export functionality what you would need to do is modify the script to generate a blob of CSV and download it through script. Here is what I think was the original post that introduced the idea of downloading "all the things" (ie anything):.





Download All The Things
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=416

Several of the Pasites use this short download() function which you can use to force a file downlolad by specifying the filename and contenxt:
function download(filename, content) {
  var blob = new Blob([content]);
  var a = document.createElement("a");
  a.href = window.URL.createObjectURL(blob); 
  a.download = filename;
  a.style.display = "none";
  document.body.appendChild(a);
  a.click();
}
download("mydata.csv", "a,b,c\n1,2,3\n4,5,6");
To try it out just past the above code into your console.

If it isn't abundantly clear from similar file related questions in the forum, you can create parse, and manipulate files "out of thin air" - you don't need to start with a file attachment field or an actual file on your hard drive. Just "conjure" your needed file into existence using the Blob() constructor or other similar JavaScript affordances.
(Edited)
Photo of Dan

Dan

  • 1,402 Points 1k badge 2x thumb
Thanks, will look into this. As always, your insight / guidance is appreciated!
Photo of Krissy

Krissy

  • 100 Points 100 badge 2x thumb
Hi Dan, or whoever else can help.  I've been trying to get this to work and all I'm getting are the listed dots.   So it's giving me the correct total number of dots for the exact total number of records but it is not returning the field values.  Any suggestions on what we could be doing wrong?
Photo of Taylor Davis

Taylor Davis

  • 60 Points
A little late, but better than never...

To answer your question (and Vivek's below), the parameter that needs to be modified is actually here:
 var template = "<ul>{{#.}}<li>{{comment}}</li>{{/.}}</ul>";  

You'll want to change "comment" to the Field Label of the target field in your child table. You'll notice in the demo that Dan is querying for values in a field called "Comment"

And for those asking: yes, you need to write your field labels in lower case with underscores instead of spaces.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
I have no idea why you mean by "dots". Since this question is two years old please post a new questions with just the relevant details that pertain to your issue.
Photo of Vivek Vishwanath

Vivek Vishwanath

  • 474 Points 250 badge 2x thumb
HI,
I'm getting this text in the Childs field of the "Parents" table.




<img qbu='module' src='/i/clear2x2.gif' onload="javascript:if(typeof QBU=='undefined'){QBU={};$.getScript(gReqAppDBID+'?a=dbpage&pagename=module.js&rand='+new Date().getTime())};"><div class=QBU_Childs data-rid=1></div>


What is the thing that I missed. please Help. (I updated the Variables in the .js file )
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
The field needs to be a Rich Text Formula field. You have a Text Formula field currently.
Photo of Vivek Vishwanath

Vivek Vishwanath

  • 474 Points 250 badge 2x thumb
I tried with Rich Formula Text Field. but I'm not successful.
I'm getting result as

Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
The code is generating two <li> bullets.

More than likely you have not set the parameters correctly for your application. These parameters are from my demo application:

  //parameters:
  var dbid = "bkgzxdr4x";
  var dbidParents = "bkgzxdr6t";
  var dbidChilds = "bkgzxfptb";
  var apptoken = "q2aznmd9h7eymbwavpzjbsc8hcf";
  var n = 100;
  var relatedParentFid = "7";
  var relatedParentLabel = "related_parent";
  var clist = "3.7.10";
  var qbuClass = "QBU_Childs";
Photo of Vivek Vishwanath

Vivek Vishwanath

  • 474 Points 250 badge 2x thumb
The Two dots are actually the data from comments fields of child table. 

  • aaa
  • bbb

But, its just showing 
  •  
  •  

  • its not pulling the data from the child table. What can be the possible reason.
    I have Identified that I should be using App. specific dbids, fids and apptoken.

    What is "QBU_Childs"?
Photo of Ian Greig

Ian Greig

  • 92 Points 75 badge 2x thumb
I just implemented the solution with some additional sorting using a slist: and it works very well.

Thanks Dan for you scripted solution it works really well and what's even better is I really don't know how to use Javascript but with Dans good support notes I was able to work it out.

This conversation is no longer open for comments or replies.