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

Question

I have two tables:Study Tasks containing: Study NumberBillable DateRevenueForecasts containingStudy NumberForecast Date1st 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).

EricJHansen · Answer

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.

Sign up

Login to the community