Forum Discussion

JessicaFeauto2's avatar
JessicaFeauto2
Qrew Cadet
8 years ago

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

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 :)
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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]   
  • JessicaFeauto2's avatar
    JessicaFeauto2
    Qrew Cadet
    8 years ago
    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).
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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.

      
  • JessicaFeauto2's avatar
    JessicaFeauto2
    Qrew Cadet
    8 years ago
    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])
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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.
  • JessicaFeauto2's avatar
    JessicaFeauto2
    Qrew Cadet
    8 years ago
    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.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    Hmmm, well what if you show the calculated key and have the users copy and paste it from one column to another?
  • JessicaFeauto2's avatar
    JessicaFeauto2
    Qrew Cadet
    8 years ago
    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.