AnsweredAssumed Answered

Calculating summary fields across tables

Question asked by RolandGomez on Dec 19, 2016
Latest reply on Dec 21, 2016 by philmodjunk

Hi Everyone.

      I think I remember reading awhile ago that this was not possible, but I'm not so sure that was true. Okay here we go.

A little explanation of what we're doing. Our real estate appraisers are being moved over from 100 percent commission to salary; however, their salary going forward is based on what they are able to produce (the salary will change probably every quarter). So, I need to be able to track and summarize their salary vs. their production.

 

In Table A: this is just a place where each record holds the appraisers name, their salary, when they were payed (see following paragraph), and the date the record was created.

In Table B: This table is where each appraisal record is created and it holds the appraiser's production.

 

I should mention that the following is being sorted by "Year", "Month", and Pay Periods 1 and 2"; however, for the purposes of keeping this clear I'm only mentioning the fields I'm having issues with.

 

Table A has these fields:

     Salary (Number Field)

     Salary_Total (Summary Field)

*easy*

 

Table B has these fields:

     Job Fee (Number Field)

     Job Fee_Total (Summary Field)

*also easy*  

 

Here's what I have happening: Within Table B, in a sub summary and via a portal, I have "Salary" and "Salary_Total" displayed, and not only are they displayed, but they are also matched up(sorted) along side with the "Job Fee_Total* of that same time period (I thought I was pretty fancy for doing that).

 

Here's what I need!!!

So for each pay period I need to find the difference of their salary vs. production. I need to be able to do the following calculation in Table B in a subsummary: "Job Fee_Total"-"Salary_Total"

 

thank you in advance for insight you may be able to offer. Also, whether it's elegant or complicated, a solution is a solution!!!

 

Best regards,

-Roland Gomez

 

ps.

Here's an image of my report. The Salary figures haven't gone into effect. These numbers are appearing just so that I can test the calculations. I made some mock salary records.

Screen Shot 2016-12-19 at 7.20.57 AM.png

Outcomes