Discussions

Expand all | Collapse all

Self Join Relationship puts Summary Field on Detail, not Master

  • 1.  Self Join Relationship puts Summary Field on Detail, not Master

    Posted 08-31-2017 14:24

    Hi,

    I have a Budget Entries table where each record represents one year. I've joined this table to itself because I need to capture the prior year's gross budget impact (year N needs to know the impact of N-1). 

    When I try to add a summary field to the master Budget Entry in the relationship, a field named Prior Year Budget Gross, it gets added, instead, to the detail.

    The effect of this is that my "Prior Year Budget Gross" is behaving as a "Next Year Budget Gross" instead, but if I add the "Budget Entries" default report to a Budget Entry, it appropriately shows its prior year.

    Has anyone run into this before? Is it expected behavior that I just wasn't expecting? Is there another way to solve this problem?

    Thanks in advance for any assistance.

    Dave


  • 2.  RE: Self Join Relationship puts Summary Field on Detail, not Master

    Posted 08-31-2017 16:29
    The way it displays in the relationship is an error, but it still calculates properly.  Because its still a summary of any child records.

    I was worried when I saw in on the details table too, but pay no bother.


  • 3.  RE: Self Join Relationship puts Summary Field on Detail, not Master

    Posted 08-31-2017 17:28
    Thanks for the reply, but it doesn't match my experience. Perhaps I'm misunderstanding what's happening?

    Here's an example, with irrelevant attributes removed and numbers modified:
    • Entry A has Reporting Year 2017, a cost of 100K, a prior year entry of Entry B, and a "Prior Year Cost" of 2 million
    • Entry B has Reporting Year 2016, a cost of 20 million, no prior year entry, and a "Prior Year Cost" of 100K
    • Entry C has Reporting Year 2018, a cost of 2 million, a prior year entry of Entry A, and no "Prior year Cost"
    So - it looks like the children are ending up summarizing their parents.