Forum Discussion

CharlotteMorin's avatar
CharlotteMorin
Qrew Cadet
3 years ago

Dynamic Report Filtering on Child Summary Fields

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
------------------------------
Relationships

3 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    3 years ago
    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
    ------------------------------
  • CharlotteMorin's avatar
    CharlotteMorin
    Qrew Cadet
    3 years ago
    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
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Qrew Champion
      3 years ago
      if you make a dashboard URL link to the special record in edit mode, that will save some clicks.  There is not a way to get rid of the option to delete on a per report basis  The only way is to remove Permissions to delete.  Many admin's are pretty ruthless about blocking normal users from deleting.

      ------------------------------
      Mark Shnier (YQC)
      mark.shnier@gmail.com
      ------------------------------