Discussions

Expand all | Collapse all

Combine Summary Reports

  • 1.  Combine Summary Reports

    Posted 21 days ago
    I have two tables: Employee Leave and Active Directory. Employee Leave records have a Shift Type and Date. Active Directory records also have a Shift Type. The Active Directory can have many Employee Leave records.

    I want to summarize the number of Missed Shifts and Available Shifts in one summary report, grouped by week and Shift Type. I also need to add calculated columns like Total Missed %, and Missed % by Shift Type.

    This doesn't seem difficult in theory but I can't seem to combine the two. Attached is an excel version of what I'm trying to create.

    Thanks in advance!

    William



    ------------------------------
    William C
    ------------------------------


  • 2.  RE: Combine Summary Reports

    Posted 21 days ago
    You will need to have a table of Weeks where the key field is the date that represents the Sunday of the week.  Use Excel to load up all the Sundays for say the next 10 years.  Set the Key field to be the date field.

    Then make a relationship where One Sunday has many  Employee Leaves.  Let it create a field for Related Week, but then edit that field to make it a formula field 

    FirstDateOfWeek([my date field])

    Then make summary fields on the Relationship to summarize up to Weeks the various fields you need to sum from  Employe Leaves.


    Repeat the process from the other table, Active Directory.  Then you will be able to do your calculations on the weeks table.


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



  • 3.  RE: Combine Summary Reports

    Posted 21 days ago
    Thanks, Mark! I did everything as instructed for the first table. When i tried to add summary fields to the first relationship i received the following:

    We are sorry -- at this time, you cannot create a summary field using a relationship where the reference field is this type of formula field. Please visit our limits page (https://lamresearch.quickbase.com/db/main?act=dohelp&page=quickbase_limits) or contact Care for more information.

    Any idea how to get around this?

    Best,
    William

    ------------------------------
    William C
    ------------------------------



  • 4.  RE: Combine Summary Reports

    Posted 21 days ago
    Can you tell me more about the date field you are using on the child side? is this a data entry field type?  Or a formula field based on Date Created.

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



  • 5.  RE: Combine Summary Reports

    Posted 21 days ago
    Yes, there is a "Start Date" that is a data entry field

    ------------------------------
    William C
    ------------------------------



  • 6.  RE: Combine Summary Reports

    Posted 21 days ago
    I was a  bit afraid that would happen and so I did my own test first and it did work.  I'm curious about this issue though and would not mind having a look at your app myself.  Contact me directly by my email address below if you would like me to have a quick look.

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