How can I create a report in Quickbase that aggregates multiple rows into a single line per record?
I have a Quickbase table where I track physician credentials. Since each physician can have multiple credentials, each physician has several rows in the table, depending on how many credentials they have. This structure is necessary due to the table relationships required for other functions in my app.
However, I want to create a report that shows all the credentials (along with the renewal and approval dates fields) for each physician on a single line, instead of multiple rows. Is it possible to aggregate the credentials into one line per physician in a report?
I'd really appreciate any help you can provide.
np,
The first step is that on the child Physician Credentials record you need to create a single field called perhaps [Physician Credential for Roll Up] which nicely labels and appends together all the fields for Roll up. (Post back if you need help with that)
Then you will make a Combined Text Summary field (ie on the relationship) of [Physician Credential for Roll Up] called [Combined Text Physician Credentials for Roll Up]
Then because that will look terrible on a report, make a new formula text field on the parent field called [Physician Credentials Summary] with a formula of
SearchAndReplace(ToText([Combined Text Physician Credentials for Roll Up]), " ; ", "\n")
That will replace the field which has a bubble appearance with a one that nice vertical list of the Physician Credentials in one field suitable for a column on a report.
