Expand all | Collapse all

Join and summarize data found in several tables

  • 1.  Join and summarize data found in several tables

    Posted 03-28-2018 21:58
    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?

  • 2.  RE: Join and summarize data found in several tables

    Posted 03-29-2018 14:36
    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.

  • 3.  RE: Join and summarize data found in several tables

    Posted 03-29-2018 16:57
    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.

  • 4.  RE: Join and summarize data found in several tables

    Posted 04-04-2018 19:29
    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 Specialistmneil@mcftech.com Phone: 440-201-6050 ext. 476" width="300px">

  • 5.  RE: Join and summarize data found in several tables

    Posted 04-05-2018 12:15
    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.