Even due you might get some help here at the forum, I would suggest you to contact a local FileMaker Developer that can help you analyse and develop your solution together with you.
What is the relationship between the two tables?
If it is correct, a calculation field can just reference the summary field from the other table. When referencing a summary field from a related table occurrence, the relationship controls what records are used to compute the summary value. For a given record in Table B, if there are three records in Table A that are related to it, the summary field value will be computed from those three related records. No need for getSummary in that case.
Oh snap! How do I do this! Okay.
Now enters a third Table
Table A- Hold salary Information
Table B-Holds production information
Table C-is where I house employee's information.
so the relationship is via a third table by appraiser's name.
The only reason I have it this way is because I was auto populating the appraiser's information (Table C) into the salary record (Table A). I have no problem getting rid of that relationship and linking Table A and Table B directly.
What I am ignorant about is how to "For a given record in Table B, if there are three records in Table A that are related to it, the summary field value will be computed from those three related records."
I also am aware that these relationships should mostly likely be linked to a file number instead of a name. I inherited this database from my predecessor and I don't have the guts just yet (2017 is the goal) to correct this.
Thanks so much for your help philmodjunk!!!!!!!
Thanks for the heads up; however, I've tried the "getsummary" function and it just doesn't seem to work. I am responding to your comment after philmodjunk's response. I guess the getsummary function isn't working due to the manner in which these tables are related. "a novice I am"(in the voice of yoda). Thanks for the input!
Getsummary is a good option if you want to get a subtotal based on a group of records from the current found set. That does not appear to be what you need here.
Now to answer the question directed at me:
With summary and calculation fields, the context--the starting point of reference specified, is a key detail in what result you get whenever an aggregate value--a value computed from multiple records is needed. Summary fields are just one such option that computes an aggregate value.
If you set up a calculation field in Table C or Table A and select either "Employee Salary" or "Employee" as the "context" for your calculation (there is a drop down at the top of the calculation editor that you can use for making this selection. You'll probably find the correct option selected for you, but if you have other occurrences of these tables, you may have to select the correct one--the one that matches your screen shot.
You can then just put the summary field from Table B into this calculation and it will show a total based only on Log records for a given employee.
You can also set up a layout based on Table C or Table A, put the summary field from the Log table on the body of the layout and you'll see the same value.
You can also use the Sum ( ) function in similar context to get the same sub-total.
Thanks again. I have been able to accomplish that. Perfect. Thanks, but I should have been more specific and I think this is where the challenge might be.
As displayed in my original posting, the image of the report that I run is trying to find the difference between two sub-summaries from different tables.
Table B: The "Job Fee Total" subsummary
Table A: The "Salary Total" subsummary
my challenge is being able to produce the "Salary Total" into Table B within the same time period(this is the kicker).
The only reason they are currently being displayed now is because I've utilized a portal that filters when the
Year, Month, and Pay Period(permonth) match.
Gosh I hope this is making sense.
If your context and relationships are correct, you can just subtract the one summary field from the other. By your relationships, you can define this expression in a calculation field in table C from the context of "employees":
employee_salary::salary_total - log_data::job_fee_total
it doesn't make sense to me why you want to define this on table B. Please explain why that is what you want here.
The end goal is to have the appraiser view in one report the difference of their production vs. their salary. If their salary would stay the same throughout the year...no big deal; however, it will fluctuate from quarter to quarter. The only way I knew to keep track of these changes was to house the salary data(where each record is a payperiod and holds the salary for that time period) in a separate table from the production table(where each record is a job file/appraisal associated with job fee). By the end of the quarter management needs to be able to see if the appraiser is in a surplus or a deficit in order to adjust for the following quarter. The aforementioned calculation you provided is what I've tried in the past. You have now made it very clear that this is a relationship issue. Now I know which rabbit hole to go down! Thanks so much Philmodjunk!!!!!!
OK, but your post argues for keeping this calculation in the employee table, not one of the others. To repeat, why do you want it in the other table? What problem does that solve for you?
Ah I see the confusion. I have definitely misspoken. I have no intention of keeping this calculation in the "Employee" table. I would like to keep this calculation in Log_Data (Table B), where all of the production information is kept.
Sorry, but you misunderstood.
Your design and your description of what you want indicate that this should be defined in the employee table. Why would you want to put it in any other table? What problem does that solve for you to put in another table?
Please note that no data is being "stored" here. This will be an unstored calculation that updates when the field is first displayed on your record. That can be both a good and a bad thing.
It is possible to put this in one of the other two tables. In that case, you refer to the related table's summary field directly and use getsummary to get the needed subtotal from the local summary field--and this will only work if you correctly sort your records to get the needed groups in your found set.