Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
6 years ago

Summary Table not Calculating Averages, and trying to color fields in the table

So two things I am struggling with.  First one is the more critical item which is pulling data through.  I made a summary table so that I can create data on a report to put on my dashboard pages.  I h...
APIs and custom code
Formulas and functions
Relationships
ArchiveUser's avatar
ArchiveUser
Qrew Captain
6 years ago
Ivan - Based on the information you've provided (and without me knowing a lot about your end goal) it sounds like you may need to revisit your approach. I'm guessing that a single Service Call could have many Punch List Items. If this is indeed the case, you would need to setup a relationship between these two tables and then do a Summary field from Punch List Items to Service Calls. You can then build a Summary Report on the Service Calls table to see the information you're looking for.
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    6 years ago
    So actually I think maybe I confused you on that.  Punch List Items and Service Calls are COMPLETELY independent of each other.

    I am trying to calculate an average of each for reporting purposes for every open item.

    So what is the average age of all of the open punch list items
    What is the average age of all open Service Calls

    This way my team can see some metrics on how they are doing and be encouraged to get those numbers down
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    6 years ago
    AH! Ok, so your setup is sound, then, you probably just need to set the 'Related Summary' field on every record for both Punch List Items and Service Calls to 1 (assuming you have only 1 record on that table). And, to make sure it keeps up, go into the properties of the 'Related Summary' field on those tables and set the Default value to 1 (or whatever the Record ID# of that Summary Table record happens to be).
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    6 years ago
    I am not sure I understand why I would need to create a record in the summary table,  If the table is an empty table with a summary field via relationships wouldnt it automatically summarize that data?  Or do I need to create one blank record for it to do that?
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    6 years ago
    You have to have a record on the table, otherwise there's nothing there for the other records to summarize up to. 
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    6 years ago
    I created a record, the fields are still blank on the summary table
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    6 years ago
    You'll need to set the 'Related Summary' field on every record for both Punch List Items and Service Calls to 1 (assuming you have only 1 record on that table). And, to make sure it keeps up, go into the properties of the 'Related Summary' field on those tables and set the Default value to 1 (or whatever the Record ID# of that Summary Table record happens to be).
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    6 years ago
    ahhh taht is it, I am following you now.  I misunderstood how summary fields work as I thought it automatically just populates.  So I can hide the related summary table from any forms and just set the default value to 1 correct.  That will automatically make sure it all pushes through without any user intervention.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    6 years ago
    Correct
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    6 years ago
    Now my followup question.  Is there a way to customize the look of the data in that report based on its value.  For example weh ave a company goal of 5 days or less on average for a service call.  So anything at 5 I would love to be yellow and anything beyond 5 red.  Less than 5 green.

    But each cell needs to be independent not the entire row.

    I am trying to put this report on a dashboard so it is a single row of summary data of KPI's for the company.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    There is not a way to colorize cels on a Summary Report. At EMPOWER in June they demos a new Report Type called Heatmaps, and those allowed cell colorization on a Summary Report. When used in an Executive Dashboard (also something new that is coming out) So you may need to wait until that comes out