12 Replies Latest reply on Dec 21, 2016 5:12 PM by philmodjunk

    Calculating summary fields across tables

    RolandGomez

      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

        • 1. Re: Calculating summary fields across tables
          Johan Hedman

          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.

           

          FileMaker Consultants, Data Consultants, Database Consultants

          • 2. Re: Calculating summary fields across tables
            Mike_Mitchell

            Take a look at the GetSummary function.

             

            FileMaker Pro 15 Help

            • 3. Re: Calculating summary fields across tables
              philmodjunk

              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.

              • 4. Re: Calculating summary fields across tables
                RolandGomez

                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."

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

                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!!!!!!!

                 

                -Roland

                • 5. Re: Calculating summary fields across tables
                  RolandGomez

                  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!

                   

                  Best regards,

                  -Roland

                  • 6. Re: Calculating summary fields across tables
                    philmodjunk

                    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.

                    • 7. Re: Calculating summary fields across tables
                      RolandGomez

                      Philmodjunk,

                           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

                      and

                      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.

                       

                      -roland

                      • 8. Re: Calculating summary fields across tables
                        philmodjunk

                        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.

                        • 9. Re: Calculating summary fields across tables
                          RolandGomez

                          Philmodjunk,

                               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!!!!!!

                           

                          -Roland

                          • 10. Re: Calculating summary fields across tables
                            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?

                            • 11. Re: Calculating summary fields across tables
                              RolandGomez

                              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.

                               

                              -Roland

                              • 12. Re: Calculating summary fields across tables
                                philmodjunk

                                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.