Rollup Help

Question

I've added a new Parent table "Cases" table to my already-established Child "Call Records" table as suggested to me earlier this week, and created a bunch of roll-up fields so that our end users can enter their fields on their call, and it will roll up to the master Cases into summary fields. Previously, we delineated calls that were subsequent calls of a same case utilizing a field on the call record ("Continuation of Earlier Case"), but it created issues of duplicate data.

That said, two questions:

Is there any way to create such summary but filter out answers = to "Unknown" or "Not reported", unless they are the only answer? I suppose the alternative is simply to have no answer. We've used Unknown/Not Reported on multi-select text fields for the ability to require the field nonetheless, but if on a subsequent call, the data is known, I'd like to disregard "Unknowns".
Can I use the roll-ups to tie to another Parent table? I'd like to tie all these to the hospitals that call us. But in theory, it could one day be more than one hospital (e.g. patient transfers, two hospitals call us). I have a summary of hospitals, which today is universally one hospital deep -- but again, within the realm of possibility to be more one day.

These Case roll-up fields will power a lot of analytics that were on our Call Records previously - demographics of cases, etc., so a lot of variety in the static items creates another issue with data clarity/cleanup.

Thanks!

christinekirk · Answer

3. Is there any way to roll up the record owners of child records into a summary?

markshnier__you · Answer

You can roll up any text field type.&nbsp; So just create a field of type formula textToText([Record Owner])and then roll it up.But then anticipating your next question, how the heck do I turn the block of rolled up text into a list user field?&nbsp;var text value = ToText([Combined Text List-User(text format)]);ToUserList(ToUser(Trim(Part($value,1,";"))),ToUser(Trim(Part($value,2,";"))),ToUser(Trim(Part($value,3,";"))),ToUser(Trim(Part($value,4,";"))),ToUser(Trim(Part($value,5,";"))),ToUser(Trim(Part($value,6,";"))),ToUser(Trim(Part($value,7,";"))),ToUser(Trim(Part($value,8,";"))),ToUser(Trim(Part($value,9,";"))),ToUser(Trim(Part($value,10,";"))),ToUser(Trim(Part($value,11,";"))),ToUser(Trim(Part($value,12,";"))),ToUser(Trim(Part($value,13,";"))),ToUser(Trim(Part($value,14,";"))),ToUser(Trim(Part($value,15,";"))),ToUser(Trim(Part($value,16,";"))),ToUser(Trim(Part($value,17,";"))),ToUser(Trim(Part($value,18,";"))),ToUser(Trim(Part($value,19,";"))),ToUser(Trim(Part($value,20,";"))))

markshnier__you · Answer

In answer to thisCan I use the roll-ups to tie to another Parent table? I'd like to tie all these to the hospitals that call us. But in theory, it could one day be more than one hospital (e.g. patient transfers, two hospitals call us). I have a summary of hospitals, which today is universally one hospital deep -- but again, within the realm of possibility to be more one day.It depends what you mean by tieIf you simply want to be able to view a hospital record and see an embedded report of all the cases that made reference to that hospital, then you would create a report link field on the hospital record and on the left side you would put the hospital name and then on the right side you would configure that multi select field or if it won't take the multi select field you would configure a ToText formula of the multi select Sélect field.&nbsp; BUT, then you set the Report link to accept non exact matches.The effect of this should be at the hospital name is contained within the list of hospitals that was on the case record multi select field then it will show on the embedded report which is filtered by that report link field.&nbsp; It's not actually a relationship so you won't be able to roll up any summary totals like counts or things like that but at least it will show on the hospital record.&nbsp;&nbsp;

markshnier__you · Answer

For this questionIs there any way to create such summary but filter out answers = to "Unknown" or "Not reported", unless they are the only&nbsp;answer? I suppose the alternative is simply to have no answer. We've used Unknown/Not Reported on multi-select text fields for the ability to require the field nonetheless, but if on a subsequent call, the data is known, I'd like to disregard "Unknowns".You should be able to create a filter on a report or on a combine tech summary field and exclude wherethe answer equals Unknownand where the answer equal Not Reported.&nbsp;

christinekirk · Answer

Mark as always, thank you!!!! I am really lost in the weeds on the formulas and spent a collective few hours trying to achieve a few things yesterday. Are you by chance available to connect and screen share for like 10 minutes, and I feel like that will achieve what I'm trying to do.

If not - basically where I'm at:

Filtering for "Unknowns" - I'm still not convinced that'll solve our issue. I don't want to exclude unknowns, if we never knew -- but if I'm making a chart on a roll-up, then the kind of field I want to clean up (but still count) might say, "Unknown" "Female" - and here, I want to count the case once, knowing the gender. I made all the roll-up fields, and I don't want to NOT count unknowns because that just leaves out a potential case where we never knew.
Record Owners: Not sure I follow where to make said field. So I want to pull together the record owners across the child records, on the parent table. A drop down would be SUPER! I hadn't even thought of that - but that might enable us to then do the nurse-by-nurse QA right on this same form. But to start - I'm still lost back at, rolling up the child record owners :).
A couple of things from another thread that I feel like one of y'all will be able to set me straight in 30 seconds flat on a screen share:
1. API Buttons - How to Add New Parent Record ideally in the background and passing on information, or if not in background, as simple as possible in the foreground (I've studied some of Sharon/Quickbase Junkie's stuff available on this, and am just notttt figuring it out).
2. How to use a button to open a specific form in VIEW on a parent table. I got as far as letting me open it in edit, but no matter how much I changed "er" to "dr", it just broke my formula. On all of this, I ended up settling for kind of "Good enough", but I know I can make it so much better!!

Forum Discussion

Rollup Help