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

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
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). 
Photo of Jessica

Jessica

  • 414 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
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.
Hi Jessica, did you find a resolution?
Photo of Jessica

Jessica

  • 414 Points 250 badge 2x thumb
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?
Photo of Jessica

Jessica

  • 414 Points 250 badge 2x thumb
anyone else want to help?
This looks correct. If you would like to invite me to your app I could try to troubleshoot for you.