Formula to lookup field in all children records

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • In Progress
Is there any formula to lookup field in all children records.

I have subscribers that are children from an event which is the parent table.

I want to have a formula text field in the event record which will list all names of the subscribers.

It will look something like this: John, Sam, etc

Thanks
Photo of Micro Dev

Micro Dev

  • 1,536 Points 1k badge 2x thumb

Posted 6 months ago

  • 0
  • 1
Well aren’t you lucky. If you read the release notes from the very recent December 16th release you will see that there is a new type of summary field called combine text. You can now summarize any text field in children up to the Parent to get a list of up to 25 Unique children.

The field will appear in a bubble format, but can be turned into a more text like appearance using a formula field ToText([my summary combine text field])
Photo of Harrison Hersch (QB)

Harrison Hersch (QB), Quick Base Sr. Product Manager (Platform)

  • 1,498 Points 1k badge 2x thumb
Yes, please.
Ticket entered. Case #579202
Photo of Harrison Hersch (QB)

Harrison Hersch (QB), Quick Base Sr. Product Manager (Platform)

  • 1,498 Points 1k badge 2x thumb
Hi, Mark/all. Quick update here. I was a little mistaken between what made it to prod vs some ideas we were throwing around in dev. There are two different limits with this feature:
  1. The number of characters for a single value. This is not limited in the traditional sense but is truncated with an ellipsis on reports just like standard multi-select text fields.
  2. The number of items rolled up into the field. This is limited to 25 today. The open bug is that we aren't showing the ellipsis as the 26th value. I don't know that the bug will be resolved by January, but we will up the limit to 50 in that release to minimize the chances of running into it. I'd like to understand the use cases of needing more than 25 though if anyone could share.
Hope this helps.
Photo of Micro Dev

Micro Dev

  • 1,536 Points 1k badge 2x thumb
There is a lot of different reasons someone would need all the children combined text from a specific field. One would be if you want to check how many children you have within a parent record that have a certain amount of times a certain value in this field. 

Thanks
Photo of Harrison Hersch (QB)

Harrison Hersch (QB), Quick Base Sr. Product Manager (Platform)

  • 1,498 Points 1k badge 2x thumb
Hi. Can you clarify this further? That sounds like something that can already be done with summary field calculations.
Photo of Micro Dev

Micro Dev

  • 1,536 Points 1k badge 2x thumb
Please update with the outcome. Thanks
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
This can be done with one Rich Text Formula Field using the 3Q&S Technique without any limitation on the number of child records and using any adornment of the summarized child records with HTML and CSS. Here is a quick demo of me emulating the the style of the native "combine text" summary fields:



Get My Meds ~ Medications List All
https://haversineconsulting.quickbase.com/db/bn96mgts4?a=td

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

Notes:

(1) Concatenating fields from all child records is also a catamorphism (aka fold, reduce, collect, summarize, aggregate etc). Not only can you use this technique to implement any of the numeric catamorphisms listed in the below post, you can apply catamorphisms to text entities.

Catamorphism Fields
https://community.quickbase.com/quickbase/topics/catamorphism-fields
(Edited)
Oh, so you need to handle more than 25 unique children?
Photo of Micro Dev

Micro Dev

  • 1,514 Points 1k badge 2x thumb
Yes. But based on tech support you should be able to view more than 25. Waiting to hear the outcome of your support ticket. In the mean time I was playing with Dans script.

(Edited)
My initial response from support was he thought was a limit of 25, but he needed to hear back from the actual developers and that would not be until later this week when everyone is back in action after the holidays.
Photo of Micro Dev

Micro Dev

  • 1,514 Points 1k badge 2x thumb
Dan! Can you pls answer this question? Thanks

Please specify where I need to replace with my field IDs also I see you put in a " after the > which is causing an error.
Photo of Micro Dev

Micro Dev

  • 1,514 Points 1k badge 2x thumb
Dan. I am still trying to implement this and I am getting an error.

Here is my code:

"<img src" &
" data-rid='" & [Record ID#] & "'" &
" onerror='\n\n(async () => {\n  var dbid = `bpazt8r8v`;\n  var dbidPatients = `bpazt8saj`;\n  var dbidMedications = `bpazt8sb6`;\n  var apptoken = `(myapptokan)`;\n  $.ajaxSetup({data: {apptoken}});\n\n  var rid = this.dataset.rid;\n\n  var medsString = await Promise.resolve(\n    $.get(dbidMedications, {\n      a: `q`,\n      clist: `6`,\n      query: `{7.EX.${rid}}`,\n      opts: `csv`\n    })\n  );\n  var medsArray = medsString.split(/r?\\n/);\n  medsArray.shift();\n\n  var markup = `<a href=&quot;${dbidMedications}?a=s&query={7.TV.${rid}}&opts=disprec&isDDR=1">`;\n  medsArray.forEach(function(med) {\n    markup += `<span class=&quot;wrapper slk&quot; style=&quot;display:inline-block;&quot;>${med}</span>`\n  });\n  markup += `</a>`\n\n  this.outerHTML = markup;\n\n})();\n\n'>" 

I updated the table ID's and the dbid. after this {7.TV.${rid}}&opts=disprec&isDDR=1">` I am getting an error.

Any suggestion?

Thanks