Summary Report on Dates

Question

I am trying to create a summary report around a bunch of dates and am stuck...&nbsp; Here's the scenario.We are a marketing organization and collect a bunch of dates for people we market towards.&nbsp; 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.&nbsp; In short, our table looks like:Prospect NameComm Sent DateComm Read DatePositive Response DateNegative Response DateBooked Call DateI need to create a summary report across our entire prospect list that looks something like:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Jan 2021&nbsp; &nbsp; &nbsp; &nbsp;Feb2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Mar2021&nbsp; &nbsp; &nbsp;Apr 2021 &nbsp; .................&nbsp; &nbsp;Dec 2021Comms Sent&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 18&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30Comms Read&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;16&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 14&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12Positive ResponsesNegative ResponsesBooked CallsI have not been able to figure out how to create the monthly columns at all.&nbsp; I am partially able to create the transposed version of the above however it's not ideal.&nbsp; The only options I am seeing in the Summary Report related to dates are "distinct count", which isn't quite right.&nbsp; We might (often do) send multiple communications in a single day.&nbsp;&nbsp;Any recommendations / suggestions would be greatly appreciated!------------------------------Jeremy LahnersLeadBaller------------------------------

markshnier__you · Answer

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)
mark.shnier@gmail.com
------------------------------

Forum Discussion

Summary Report on Dates

1 Reply