Forum Discussion

CarolMcconnell's avatar
CarolMcconnell
Qrew Captain
5 years ago

Report user field consolidation

I need help with creating a summary report.  The user will enter a beginning and starting date.  That date could be in the Closed date or the reclosed date fields.

This is how the breakdown should go.

Assigned to with a closed date in the date range entered
Transferred to with a closed date in the date range entered
ReAssigned to with a reclosed date in the date range entered

I need to summarize by Employee name and number of records.  The employee name could be in any of the three user fields, assigned to, transferred to or reassigned to.

I can't think of how to do this.

1. If they were in the assigned to field and had a closed date with in the date range it should be counted as 1
2. If they were in the assigned to field and did not have a closed date with in the date range but they were also in the reassigned to field and the reclosed date is in the range they  should be counted as 1
3. If they were in both and the date range for both closed field where in the date range, then the count should be 2.

Thank you for your help.

------------------------------
Carol McConnell
------------------------------
reports and charts
Tables and fields

3 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    Can two different people get points for the same record?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • CarolMcconnell's avatar
      CarolMcconnell
      Qrew Captain
      5 years ago
      Yes

      ------------------------------
      Carol McConnell
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago
        I think that is is possible but not within the scope of what can be explained on this Forum on a step by step basis.  The fundamental issue is that you need to have sort of 2 counts for different users off 1 record.

        Here is how I would do it, and it would probably take a couple of hours to get this working on a screen share session.
        1. Make a single record in a special table called Reporting Stat Dates to hold the date range. 

        2. Relate that record to all detail records in a relationship where the reference field is a formula of 1.  (Because this Reporting Stats record is Record ID#  = 1).

        3. Lookup the date fields down to the detail records and have a checkbox formula  for [Qualifies for Dates filter?] to calculate TRUE if the record is in date range.

        4. Make a table called Employee Reporting Stats. 

        5. Push a URL formula button which would purge the records in Employee stats and then run two saved table to table imports to populate the Employee(s) from each qualifying record into Employee Reporting Stats.

        6. The results would be displayed on the Reporting Dates record in a Summary report by employee.

        Contact me by email if you would like to work one on one to get this working or I can answer more questions here if what I described seems to be mostly within your skill set.

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------