Summary field not working--suspect key and reference field issue

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have tables for Project Forecasts and Project Tasks (which include revenue). Each of these tables has a field, Project Number.  On each Project Forecast, I want to summarize the revenue for a given Project Number(so that the forecast and actual revenue can be compared).  Here is the relationship:

Project Forecasts  -----------------------------E Project Tasks
key field: Record ID                                  reference field: Related Forecast
Summary Field: Total Revenue

However, the summary field never pulls in anything.  I suspect this is because the Record ID and Related Forecast don't match.  I can get a report link field to pull in the correct records, because I can change both the key and reference field to Study Number, but this won't work because I need to be able to summarize the data by type of study, project manager, etc.

I also tried creating a relationship where the reference field was a lookup field of the Forecast-Record ID but it wouldn't let me create a summary field.  I saw in the forums that I could populate this Forecast-Record ID field through a form rule, but that won't work because the forecasts are intentionally filled out via grid edit.  

Help :)
Photo of Jessica

Jessica

  • 414 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
So if inherently "Each of these tables has a field, Project Number", why not change the the Key field of the Project Forecasts table to be the Project number field.  

Then A:  either rebuild the relationship to Project Forecasts using that the Project Number on the Projects Forecasts,

or B:  if you already have a lot of lookup fields pulled down and on forms that you don't want to recreate, you can sort of cheat and change the field [Related Forecast] to be a formula field with a formula of [Project number]   
Photo of Jessica

Jessica

  • 414 Points 250 badge 2x thumb
The issue is that every month a new Forecast is generated for each Project Number.  So a given Project Number will have numerous Forecasts.  This is done this way so we can go back and look at how much was forecast for, say, June during previous Forecasts and we can see how things change.

I realized this makes Project Forecasts to Project Tasks a many-to-many and I went down that rabbit hole for a while but thought this would be simpler (if it would ever work).
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Why do you think that this is a Many to Many.  Maybe you can explain more of your tables.  It looks like 

1 Project has Many Project Forecasts
1 Project forecasts has many project tasks?  is that the setup?

In any case, you can have summary fields to summarize for example just forecasts for the previous month, or the 2nd previous month, so the total revenue does not need ot be the sum a of all the forecasts, just a filtered set of them.

  
Photo of Jessica

Jessica

  • 414 Points 250 badge 2x thumb
1 Project has Many Project Forecasts
1 Project has Many Project Tasks

March Forecast for Project 1234--$2000 for March, $3000 for April, $2000 for May
March Forecast for Project 5678--$1000 for March, $1500 for April, $10000 for May
March Forecast for Project 9876--$      0 for March, $5000 for April, $3000 for May

on the Project Forecast, for each Month-Study Number combination
I would like to summarize:
 Total Revenue from Project Tasks
where [Project Forecast-Project Number]=[Project Task-Project Number]
and where [Task-Billable Date] is greater than FirstDayofMonth([Forecast Date])
and less than LastDayofMonth([Forecast Date])
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
What is a "study"  How does that fit into the relationships?
Photo of Jessica

Jessica

  • 414 Points 250 badge 2x thumb
Bah, Study Number=Project Number.  Sorry
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
OK, now i understand.

I suggest that the key field of the Project forecast needs to be changed to a field called
[Related Project - YYYY-MM(key)]

It will get populated by a Form Rule which changes that field to the value in the field

[Related Project - YYYY-MM  (formula)]

The field for that would be 
List("-", ToText(Related Project]), [YYYY], [MM])

ie the user would create a new forecast and indicate the YYYY and the MM in drop down fields. The formula would calculate what the key field would need to be and the form rule would force it into a text field, since the key field to a table may not be a formula field.
 
On the revenue records which are your Task records, you can calculate that same Key field. You know the related project, and you can calculate the YYYY-MM based on the [Task-Billable Date].

Then you use that calculated field in the relationship to project forecasts as the child records are allowed ot have a formula field to calculate their Parent.
Photo of Jessica

Jessica

  • 414 Points 250 badge 2x thumb
Thanks for all of your help.  Unfortunately, this still won't work because Forecasts are populated via grid edit so a form rule won't work.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Hmmm, well what if you show the calculated key and have the users copy and paste it from one column to another?
Photo of Jessica

Jessica

  • 414 Points 250 badge 2x thumb
Figured out a solution.  Instead of bringing it into the Forecast table, I was able to bring "Maximum Forecast Date" into the Projects table and sum everything there.  It means I can only sum the revenue for the most recently created forecast, and lose some visibility to the revenue for old forecast months, but maybe I can just export the report once a month to a drive.  I am also thinking about doing some sort of Record ID#-1 relationship so I could sum revenue for the past few forecasts.  Either way, thanks for your help!  I'm sure I'll use some of these concepts in the future.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
OK, thx for letting me know.
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
I'd recommend you build a Months table, and set up a few formula reference fields.  Then all your summary issues will go away, and you can have accurate monthly forecasts for all the projects you need.

Another option is build a summary report on the "forecast" table with the values you want to keep track of, and then create a report link field on the "projects" table and use the summary report.  That should give you a nice list of all the forecast months on the project form.

Just some other ideas...