Discussions

Expand all | Collapse all

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

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

    Posted 03-22-2017 17:38
    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). 


  • 2.  RE: Create a ""sumif"" type function in a join table for many-to-many relationship

    Posted 03-22-2017 18:00
    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.


  • 3.  RE: Create a ""sumif"" type function in a join table for many-to-many relationship

    Posted 03-24-2017 21:25
    Hi Jessica, did you find a resolution?


  • 4.  RE: Create a ""sumif"" type function in a join table for many-to-many relationship

    Posted 03-30-2017 13:36
    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?


  • 5.  RE: Create a ""sumif"" type function in a join table for many-to-many relationship

    Posted 04-05-2017 12:18
    anyone else want to help?


  • 6.  RE: Create a ""sumif"" type function in a join table for many-to-many relationship

    Posted 04-05-2017 13:59
    This looks correct. If you would like to invite me to your app I could try to troubleshoot for you.