Forum Discussion

JessicaFeauto2's avatar
JessicaFeauto2
Qrew Cadet
9 years ago

Create a ""sumif"" type function in a join table for many-to-many relationship

I have two tables:

Study Tasks containing: 
  1. Study Number
  2. Billable Date
  3. Revenue
Forecasts containing
  1. Study Number
  2. Forecast Date
  3. 1st Day of Forecast Month (formula based on Forecast Date)
I created a Join table that is the child of both the Forecasts and Study Tasks table.  I want to create a sumif like function that sums the Revenue for a given Study Number (in Forecasts) when the Billable Date is between the 1st Day of the Forecast Month and the Forecast Date.

I thought I would be able to do this with a summary field but it seems that summary fields will only filter within the child table, not between the child and parent.  What am I missing here?

An important caveat is that each study number will be reforecast each month (ie new Record ID for each Study Number/Forecast Date combination). 
  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    9 years ago
    ok so you need to make sure that on the join table you have lookup fields from both parent tables, especially billable date, revenue, forecast date, and 1st day of forecast month.

    when you create your summary field on the forecasts table, summarize the revenue field with filtering something like:

    where billable date is on or after 1st day of forecast month
    where billable date is on of before forecast date

    try that and let me know.
  • JessicaFeauto2's avatar
    JessicaFeauto2
    Qrew Cadet
    8 years ago
    Hey Eric,

    It's been a few days but I can't get the summary field to pull anything in. I tried this with a clean app and am still not able to get it to work.  Am I missing something?
  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    8 years ago
    This looks correct. If you would like to invite me to your app I could try to troubleshoot for you.