Forum Discussion

NathanMaubert's avatar
NathanMaubert
Qrew Trainee
8 years ago

Join and summarize data found in several tables

I am looking to build a report that summarizes data found in multiple tables essentially the database is structured as follows:

Projects have many Cost Codes
Cost Codes have many Time Tickets
Cost Codes have many Expenses
Cost Codes have many Materials

Each Time Ticket, Expense, Material has a cost associated with it.
Each Time Ticket, Expense, Material has a date associated with it.

What I need to do is build a report that summarizes the total cost associated with the time, materials and expenses grouping based on the month (date) each of the categories.  I would like to display this both as a table as well as a bar chart.

Anyone have a solution?

4 Replies

Sort By
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    The best way to summarize data from multiple tables based on a date (month) is to create a "Months" table, and depending on the details that you want it could be good to create a "Days" table.

    I'll explain the "Days" table, as I think this will help you best.  Then you can always roll up things to weeks/months/years summary reports.  
    (If you get stuck or have other questions, post back, because this is considered an advanced technique)

    Create a "Days" table.

    Create one field called "Date".

    Set the 'Date' field as the Key field on the table.

    Use excel to pre-load all the records you might need.
         i.e.  01-01-2014 thru 01-01-2025

    Then create a relationship between all of those tables and the "Days" table.

    Each Day will have many Time/Exp/Materials/etc.

    Once the reference field is made on the child tables, it should be a 'Reference Date' field.

    Then you can change that field properties to be a 'Formula - Date' field.
    Then insert a formula that references the [Expense Date] field.

    Now you will have a 'formula relationship' that will automatically connect all those records to the respective dates.

    Create all the summaries you need. i.e. Total Amount - Time, Total Amount - Expense, etc.
  • NathanMaubert's avatar
    NathanMaubert
    Qrew Trainee
    8 years ago
    Thanks For the Reply. I can see how that will roll-up all of the amounts based on date but I ma not sure how it will be filtered on cost Codes and Projects. I don't just need an over all total but a total specific to a cost code and then project for each month.
    • MCFNeil's avatar
      MCFNeil
      Qrew Captain
      8 years ago
      This might depend on how many different cost codes you have, how often they change, and how many months you want to go back on.

      If all three of those are limited, you could do it with specific summary fields for each, but if you need it to be more dynamic, you will need to create a "Cost Code Summary" table.
      Each record will be a combo of month+cost code. 
      How you create those records will need to be through a custom script.

      I wish it was more simple, but when you are summarizing on multiple data points (date & code) you have to have a record that ties those together.  At least for graph reports.

      With your current set up you could create a few summary reports that might give you close to what you need.


      Matthew Neil - Product Specialist
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    8 years ago
    Created with Lucid Chart (lucidchart.com)



    QuickBase does not have a general purpose query language such as SQL. Queries in QuickBase are restricted to queries of a single table and fields that can be "looked up" from parent or grandparent tables. So you could query the Expenses table and include fields from the Cost Codes and Projects tables that are related through lookup fields. But you could not simultaneously include fields from the Time Tickets or Materials table because those tables are not parents of the Expenses table. However, if you summarized some of the Time Ticket or Materials fields to the Cost Codes table those summarized fields could be included as lookup fields of a query of the Expenses table.

    However, using script and the QuickBase HTTP API you can often generate a set of data that represent a more complicated query and the use HTML templating (eg Mustcahe) to render an appropriate report of the results. When you take this approach you generally use a library such as underscore.js or linq.js (a JavaScript implementation of Microsoft's LINQ - Language Integrated Query) to manipulate the raw data obtained from API calls. This process is essentially a manual implementation of the "query plan" that tools like SQL perform automatically.

    The best way to proceed with a solution is to come up with (1) a sample set of data for each table and (2) a representative report format you want to see come out.