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 have two tables, one called Service Calls, and the other Punch List Items. Each one has an Age field as a duration type and is a formula field created by the formula Today() - ToDate([Date Created]). This appears to work perfectly as in my table I can see the age.I created a relationship of Summary Table has many Service Calls and Summary Table has many Punch List Items.In each of the relationships I created a summary field with average of the field Age. I made sure the summary field is filtering active only by filtering out when status is not equal to complete and status is not equal to cancelled.I created a report showing each of these summary fields. And it says there is no data to show. Not sure where it is getting lost???NEXT problem is when I do fix that I want to figure out how to color the font based on the number. So for example if age average is more than 5 days I need that one column to be a colored font (not color the entire row).Thanks!

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.

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 itemsWhat is the average age of all open Service CallsThis way my team can see some metrics on how they are doing and be encouraged to get those numbers down

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).

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?

You have to have a record on the table, otherwise there's nothing there for the other records to summarize up to.

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

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
  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
  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
  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?

Forum Discussion

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

Related Content

Average daily active employees calculation

Calculation/Performance Limits Inquiry

Calculating 1.5 pay

Summary Reports

Calculating number of days between two date fields