4 Replies Latest reply on Oct 30, 2013 7:53 AM by Mike_Mitchell

    Sub summary report with concatenated text field


      Hi folks


      I'm looking for some ideas on how to tackle something. Consider the scenario:

      I have multiple events happening per day

      I track how many people attend each event on the event record


      I have created a sub summary report with totals of the number of people that attend the events on each day during the course of a month and the grand total for the whole month. It looks like:

      Date: Number people

      10/1/2013 10

      10/2/2013 23

      10/3/2013 15

      Total 48


      This is fine, but on the event record I may also enter some text in the Notes field. How could I get an additional column on my report to show a concatenated version of all the notes fields from that day? For it to be more useful I guess having the date and a colon prior to the text would be good.


      And while I'm at it, given this text could be none or very long, what's the approach to having this concatenated field on the report expand vertically to accommodate as much text as is there? Is it sliding or anchors or?


      Hope that all makes sense, but let me know if not. Thanks for any tips.

        • 1. Re: Sub summary report with concatenated text field



          There are a few different ways to approach this. One way would be to use a self-joining relationship (joining the table to itself on the Relationships Graph) based on Date. You would sort the relationship in the order you wanted the notes to appear (is there a Create Timestamp? Or maybe an entry time?) Next, use the List function to assemble all the Notes contents into a return delimited list in a calculation field, like this:


               List ( EventSelf::Notes )


          You might want to parse out the return characters with the Substitute function, but that would be dependent on the final result.


          At this point, you can place the calculation field inside your subsummary part. Yes, you would use sliding to allow it to collapse (not expand) to fit. Just make it big enough to accommodate the size you need.





          • 2. Re: Sub summary report with concatenated text field

            Makes sense Mike. Thanks as always.


            I've done the relationship and created the calc field and placed in the sub summary part. Data being shown is correct.


            However the part is not resizing. The rest of the fields in the part are just one text line high. I've made the notes calc field a few lines high (having to increase the height of the part to do so of course). I've selected the notes calc field and in the inspector checked off 'sliding up based on all objects above' and 'also resize enclosing part'. But the field and the part remain the full height that I set in the edit layout mode. I feel as though I've come across this before but don't remember what the fix was or if there is one.

            • 3. Re: Sub summary report with concatenated text field

              Ignore this. It's fine when report is exported to PDF. Be nice if the same thing happened when the report is generated on screen though. Thanks again Mike.

              • 4. Re: Sub summary report with concatenated text field

                Sliding doesn't work in Browse mode. As you've discovered.  


                Glad it's working.