5 Replies Latest reply on Dec 2, 2016 2:43 PM by jdevans

    Calculation with Multiple Criteria





      I have two tables and I am trying to add a calculated field to table one and look up the value from table 2 when 2 criteria is met. I cannot get the right value no matter how I try.


      Table 1:


      January Units Sold (this is the calculated field I am trying to create)


      Table 2:


      Units Sold




      The relationship between the tables: Table 1: :Item# = Table 2: :Item#

      I am looking for the Units  Sold where year = 2016 and Month = 1



      How can I accomplish that?


      I am running FileMaker Pro Advanced 14.0.2 on Windows.

        • 1. Re: Calculation with Multiple Criteria

          For the method that you are currently using, you'd need additional match fields in your relationship to match by year and month in addition to item number.


          An alternative method would be to use ExecuteSQL as you can set up a WHERE clause that specifies Item number, year and month.


          A script could perform a find on table 2 and return the value as a script result if you chose to use a script to update your field.

          • 2. Re: Calculation with Multiple Criteria
            David Moyer


            if you're using a calculated field, then you are using variables in it (year and month).  Are those variables data fields or global fields?  The approach would be similar for either.  Create a relationship based on all three fields (id, year, month) or if you only need it only for display purposes, add a filter to your portal for year and month.

            • 3. Re: Calculation with Multiple Criteria

              Similar to David Moyer, you can create a field in the parent table (Table1 in this case) and call it 'january'. Have it auto-enter with the number 1 (first month). Make sure every record in the Table1 data has a 1 in it for that field.


              Then create a second table occurrence of Table2, and call it Table2_january. Its relationship should be item#(Table2_january) = item# (Table1), and then add a second relationship that is month(Table2_january) = January(Table1_january). [Assuming that month is stored as a number field.].


              Then your calculation for adding up January only from the Table1 perspective will be Sum(Table2_january::units_sold).



              This is counting on the fact that not every record in Table2 is going to have a 1 in the month field. But some will. When both item# and month# match, it adds.



              There's obviously holes in this, but I kept it pretty simple so you could get started. As others have stated, this doesn't take year into consideration, but you could handle that. You could have a sale_date field that holds the entire date, then have separate fields to just calculate the year ( Year(sale_date) ) and month, ( Month(sale_date) ), and then use them accordingly to make sure your relationship is true. The way this initial stab at it would work, is it would add up all January sales, regardless of year, which probably isn't what you need

              • 4. Re: Calculation with Multiple Criteria

                It works great, thank you for your help!

                • 5. Re: Calculation with Multiple Criteria

                  Glad I could help someone. Far more often, I come to this community seeking help.