Skip to main content
Question

Dynamic Report Filtering on Child Summary Fields

  • September 21, 2021
  • 0 replies
  • 56 views
Forum|alt.badge.img+7
I have a Company table with two child tables - 
> Activity-1
> Activity-2

The user wants a report with a lot of company info, plus the count of related Activity-1's (easy, with summary field) and the count of related Activity-2's (easy, with summary field).

But they also want to be able to see the count for Activity 1's and 2's only within a certain date range (which changes). 

Is there a way to see the summary counts of child records within a filter range the user can set each time the report is run?

------------------------------
Charlotte Morin
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+24
A way to implement this is to create a special helper table for this purpose call perhaps company date filters.

Enter one record in the table and then prevent anybody else including the admin from adding records.

Put a two date fields on the form representing the analysis time period start and end.

Make two relationship to the child tables. 
Relate that single record which will be Record ID = 1 to every child record table through a reference field with the formula of 1.

Lookup the two date fields.

Make summary fields to count valid children taking into account the date filters.

Make a field on Companies with a formula value of 1.

Make a report link on this special helper record matching record ID of the helper record to this 1 field in Companies.  Put the report link field on the helper table form and specify the report to be used and set the report to only show in view mode.

So, the user will edit the special record, set the dates, save and the resulting filtered report will be embedded right on that special record number 1.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
Mark Shnier. Your Quickbase Coach
Forum|alt.badge.img+7
  • CharlotteM
  • Author
  • Registered
  • September 22, 2021
Thank you :-)

I got it to work!

Just one question, at the end,  you say

So, the user will edit the special record, set the dates, save and the resulting filtered report will be embedded right on that special record number 1.

The way mine is working is:

1. The user goes to the table
2. Click Edit (on the sole record)
3. Change Date/s
4. Click Save
5. Click View (on the sole record)
6. See the report.

That seems like a lot of clicking, is there any way to streamline?

And more importantly, the report shows with the usual options:

Full Report | Grid Edit | Email | More

I've looked around but can't find any way to get rid of those. I'm particularly concerned with the "click in the square and delete all records in this report" option, which would be a disaster for this report - but not for others which they might run. Can these options be turned off on a per-report basis?







------------------------------
Charlotte Morin
------------------------------
Terms & ConditionsAccessibility statement