Forum Discussion

JeremyLahners's avatar
Qrew Cadet
3 years ago

Summary Report on Dates

I am trying to create a summary report around a bunch of dates and am stuck...  Here's the scenario.

We are a marketing organization and collect a bunch of dates for people we market towards.  For each prospect we capture the date we send a communication, the date they read/review it, the date they respond to it (positive and negative responses are captured in separate date fields) and the date we book a sales call with them.  In short, our table looks like:

Prospect Name
Comm Sent Date
Comm Read Date
Positive Response Date
Negative Response Date
Booked Call Date

I need to create a summary report across our entire prospect list that looks something like:
                                          Jan 2021       Feb2021          Mar2021     Apr 2021   .................   Dec 2021
Comms Sent                     10                     15                       15                18                                   30
Comms Read                     8                      12                       16                14                                   12
Positive Responses
Negative Responses
Booked Calls

I have not been able to figure out how to create the monthly columns at all.  I am partially able to create the transposed version of the above however it's not ideal.  The only options I am seeing in the Summary Report related to dates are "distinct count", which isn't quite right.  We might (often do) send multiple communications in a single day.  

Any recommendations / suggestions would be greatly appreciated!

Jeremy Lahners

1 Reply

  • The challenge here is that with any Quickbase report, a record can only appear once.  But you need the record to appear up to 6 times.  I think that you will need to have an Automated process to create up to 6 child records.  Then the reporting would be easy, as each child would have two fields, the name of the date like Comms Sent and the Date.

    There are a whole bunch of ways to do this automation and you can use Automations or go bold and plunge right into Pipelines.

    A pipeline solution might be to trigger when a record is added or edited.  You could have the first step be to delete all the 6 children to start fresh and to also handle if a user deletes a date. Then the pipeline could create a Bulk Upsert and add up to 6 children rows into it and then commit the upsert. 

    If you build the Pipeline and get it tested and working for 1 record , you can then have say a date / time field  to also trigger the pipeline and do a giant grid edit and cause the pipeline to run for all your existing records.

    Mark Shnier (YQC)