Discussions

Expand all | Collapse all

Formula to lookup field in all children records

QuickBaseCoach Dev./Training12-26-2018 22:02

QuickBaseCoach Dev./Training12-27-2018 20:42

EOM Develepors12-28-2018 01:05

Harrison Hersch12-28-2018 18:40

QuickBaseCoach Dev./Training12-28-2018 19:54

EOM Develepors12-28-2018 19:11

  • 1.  Formula to lookup field in all children records

    Posted 12-25-2018 18:12
    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


  • 2.  RE: Formula to lookup field in all children records

    Posted 12-25-2018 18:17
    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])


  • 3.  RE: Formula to lookup field in all children records

    Posted 12-25-2018 20:19
    Thanks for the reply. I did that but it only shows the first record and not 25.

    Thanks again


  • 4.  RE: Formula to lookup field in all children records

    Posted 12-25-2018 21:31
    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.


  • 5.  RE: Formula to lookup field in all children records

    Posted 12-26-2018 00:31
    That is correct. I did that and its not showing all records only the first one.


  • 6.  RE: Formula to lookup field in all children records

    Posted 12-26-2018 14:29
    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.


  • 7.  RE: Formula to lookup field in all children records

    Posted 12-26-2018 22:01
    I put in a support ticket. Let's see the outcome.

    Thanks


  • 8.  RE: Formula to lookup field in all children records

    Posted 12-26-2018 22:02
    Yes, please post back what they say.  I'm very curious.


  • 9.  RE: Formula to lookup field in all children records

    Posted 12-27-2018 20:39
    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. 


  • 10.  RE: Formula to lookup field in all children records

    Posted 12-27-2018 20:42
    np


  • 11.  RE: Formula to lookup field in all children records

    Posted 12-27-2018 21:11
    One more question, what would be the best way to take out the ; which is placed between values when converting to text.

    Thanks


  • 12.  RE: Formula to lookup field in all children records

    Posted 12-27-2018 21:16
    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




  • 13.  RE: Formula to lookup field in all children records

    Posted 12-28-2018 01:05
    Thanks so much.


  • 14.  RE: Formula to lookup field in all children records

    Posted 12-28-2018 16:19
    We aren't limiting the summary field to 25 values, we are just only displaying the first 25 in a report.


  • 15.  RE: Formula to lookup field in all children records

    Posted 12-28-2018 16:19
    We aren't limiting the summary field to 25 values, we are just only displaying the first 25 in a report.


  • 16.  RE: Formula to lookup field in all children records

    Posted 12-28-2018 16:35
    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.


  • 17.  RE: Formula to lookup field in all children records

    Posted 12-28-2018 18:40
    Yes, please.


  • 18.  RE: Formula to lookup field in all children records

    Posted 12-28-2018 19:54
    Ticket entered. Case #579202


  • 19.  RE: Formula to lookup field in all children records

    Posted 12-31-2018 14:28
    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.


  • 20.  RE: Formula to lookup field in all children records

    Posted 01-03-2019 03:04
    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


  • 21.  RE: Formula to lookup field in all children records

    Posted 01-03-2019 13:49
    Hi. Can you clarify this further? That sounds like something that can already be done with summary field calculations.


  • 22.  RE: Formula to lookup field in all children records

    Posted 12-28-2018 19:11
    Please update with the outcome. Thanks


  • 23.  RE: Formula to lookup field in all children records

    Posted 12-29-2018 12:36
    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


  • 24.  RE: Formula to lookup field in all children records

    Posted 12-30-2018 23:55
    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


  • 25.  RE: Formula to lookup field in all children records

    Posted 12-31-2018 00:23
    Huh? Was that a response to a different thread?


  • 26.  RE: Formula to lookup field in all children records

    Posted 12-31-2018 00:29
    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.


  • 27.  RE: Formula to lookup field in all children records

    Posted 12-31-2018 00:34
    Oh, so you need to handle more than 25 unique children?


  • 28.  RE: Formula to lookup field in all children records

    Posted 12-31-2018 00:36
    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.



  • 29.  RE: Formula to lookup field in all children records

    Posted 12-31-2018 13:35
    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.


  • 30.  RE: Formula to lookup field in all children records

    Posted 01-09-2019 01:28
    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.


  • 31.  RE: Formula to lookup field in all children records

    Posted 02-24-2019 03:36
    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