Discussions

Expand all | Collapse all

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

  • 1.  Summary field not working--suspect key and reference field issue

    Posted 04-07-2017 16:08
    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 :)


  • 2.  RE: Summary field not working--suspect key and reference field issue

    Posted 04-07-2017 16:19
    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]   


  • 3.  RE: Summary field not working--suspect key and reference field issue

    Posted 04-07-2017 16:26
    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).


  • 4.  RE: Summary field not working--suspect key and reference field issue

    Posted 04-07-2017 16:31
    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.

      


  • 5.  RE: Summary field not working--suspect key and reference field issue

    Posted 04-07-2017 16:42
    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])


  • 6.  RE: Summary field not working--suspect key and reference field issue

    Posted 04-07-2017 16:44
    What is a "study"  How does that fit into the relationships?


  • 7.  RE: Summary field not working--suspect key and reference field issue

    Posted 04-07-2017 16:45
    Bah, Study Number=Project Number.  Sorry


  • 8.  RE: Summary field not working--suspect key and reference field issue

    Posted 04-07-2017 17:22
    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.


  • 9.  RE: Summary field not working--suspect key and reference field issue

    Posted 04-07-2017 18:20
    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.


  • 10.  RE: Summary field not working--suspect key and reference field issue

    Posted 04-07-2017 19:06
    Hmmm, well what if you show the calculated key and have the users copy and paste it from one column to another?


  • 11.  RE: Summary field not working--suspect key and reference field issue

    Posted 04-07-2017 20:18
    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.


  • 12.  RE: Summary field not working--suspect key and reference field issue

    Posted 04-07-2017 20:20
    OK, thx for letting me know.


  • 13.  RE: Summary field not working--suspect key and reference field issue

    Posted 04-08-2017 07:24
    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...