Formula to lookup field in all children records

  • 0
  • 1
  • Question
  • Updated 5 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,666 Points 1k badge 2x thumb

Posted 7 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 Micro Dev

Micro Dev

  • 1,666 Points 1k badge 2x thumb
Thanks for the reply. I did that but it only shows the first record and not 25.

Thanks again
That does not seem possible. I have already used that new feature many times and it works.

Can you confirm that you set up a Summary field on the relationship between Events and Subscribers and that you have not applied any filters to the summary field setup.
Photo of Micro Dev

Micro Dev

  • 1,666 Points 1k badge 2x thumb
That is correct. I did that and its not showing all records only the first one.
My only other though is that you may have some consusion over which field is being summarized. Other than that, it will take someone to look at your app, so you should put in a support ticket.
Photo of Micro Dev

Micro Dev

  • 1,666 Points 1k badge 2x thumb
I put in a support ticket. Let's see the outcome.

Thanks
Yes, please post back what they say.  I'm very curious.
Photo of Micro Dev

Micro Dev

  • 1,666 Points 1k badge 2x thumb
This is crazy.... I simply didn't relate the second subscriber to the event. I was to fast and didn't fill out the entire form and forgot that I need to relate it to the event in order to test........

So sorry for the confusion.

Thanks for your help. 
Photo of Micro Dev

Micro Dev

  • 1,666 Points 1k badge 2x thumb
One more question, what would be the best way to take out the ; which is placed between values when converting to text.

Thanks
You can use this formula;

var text value = ToText([my combine summary field]);

List(", ")
Trim(Part($value,1,";")),
Trim(Part($value,2,";")),
Trim(Part($value,3,";")),
Trim(Part($value,4,";")),
Trim(Part($value,5,";")),
Trim(Part($value,6,";")),
Trim(Part($value,7,";")),
Trim(Part($value,8,";")),
Trim(Part($value,9,";")),
Trim(Part($value,10,";")),
Trim(Part($value,11,";")),
Trim(Part($value,12,";")),
Trim(Part($value,13,";")),
Trim(Part($value,14,";")),
Trim(Part($value,15,";")),
Trim(Part($value,16,";")),
Trim(Part($value,17,";")),
Trim(Part($value,18,";")),
Trim(Part($value,19,";")),
Trim(Part($value,20,";"))
)


That will provide a comma (space) separated list of the first 20 values.  To be safe you should probably go up to 25 which is the maximum of the summary field, I believe.

If you wanted to just have a separator of a space then it would be

List(" ",
etc


Photo of Micro Dev

Micro Dev

  • 1,666 Points 1k badge 2x thumb
Thanks so much.
Photo of Harrison Hersch (QB)

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

  • 1,540 Points 1k badge 2x thumb
We aren't limiting the summary field to 25 values, we are just only displaying the first 25 in a report.
Photo of Harrison Hersch (QB)

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

  • 1,540 Points 1k badge 2x thumb
We aren't limiting the summary field to 25 values, we are just only displaying the first 25 in a report.
Well, in my testing, even when displayed on a form, I only get 25.  Should I put in a support Ticket?

I even make a text formula field

ToText([my combine text summary field])

to see the semi colon delimited "real" contents and I still only get 25.
Photo of Harrison Hersch (QB)

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

  • 1,540 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,540 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,666 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,540 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,666 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,224 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)
Photo of Micro Dev

Micro Dev

  • 1,636 Points 1k badge 2x thumb
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.

Thanks
Huh? Was that a response to a different thread?
Photo of Micro Dev

Micro Dev

  • 1,636 Points 1k badge 2x thumb
No. Dan put together a formula using the 3Q&S Technique as seen in this thread.

I need to know where to put my field IDs in order to get it to work.
Oh, so you need to handle more than 25 unique children?
Photo of Micro Dev

Micro Dev

  • 1,636 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,636 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,636 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