Forum Discussion

TineRyming's avatar
TineRyming
Qrew Member
6 years ago

Summary Report from 2 Tables

Hi everyone!

I hope someone can help me with a problem. Here's my situation:

I have a table for each of the following; Projects, Time Entries, Revenue Budgets.

Revenue Budgets and Time Entries each have weekly records that tie back to the same Project (aka. Projects is the parent for each of the 2 remaining tables).

Is it possible to create a report for a project that would show records from both the child tables, grouped by a given week. E.g. show the Revenue Budget record for WE 2/23 and the Time Entry records for WE 2/23? 

I appreciate any help you can give!

------------------------------
Tine
------------------------------
Relationships
reports and charts
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    No really possible without adding a new table.  You would need to have a table called Project Days where the Key field was a text field in the format:

    [Project #] - YYYY-MM-DD

    ie the Record ID# of the project followed by a date in text format.​

    You will have a Relationships like this
    1 Project  < Project Days (reference field will be a formula field calculated off the beginning of the Key field up to the hyphen)
    1 project Day < Time Records  (reference field will be a formula text field to calculate [Project #] - YYYY-MM-DD]   )
    1 Project Day < Revenue Budgets. (reference field will be a formula text field to calculate [Project #] - YYYY-MM-DD]  )
     
    To get this table initially populated you would create a formula checkbox field in the Project Days Table called [Project day Exists?] with a formula of true and look it up down to the two child table.

    Then create a summary report in each of the respective child tables filtered where the [Project Day Exists?] is false and copy those records to the project days table. Then do the same with the other child table to copy across any missing project day records.

    Then subscribe yourself to those report to come to oyu if any are missing in future.

    OK, but how to Automate the creation of the Project Days table going forward?  The answer is to set up an Automation to create the parent Project Days record when a child Time entry is created which has no parent [Project Day exists] = unchecked.   Then another Automation to to the same on the Revenue Budgets table.

    Now you can do your summary fields for the total revenue per Project Day  and the total time entries per Project Day.

    Lastly, put the Report Link field on the Projects form for the Project Days child table records.


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