SUM of one field, based on criteria from two other fields

[NewBoard]

I am currently working on a database that will score our company's suppliers.

I have one table that contains all contact information for the suppliers (Suppliers), and another related table that contains the actual score entries (Scorecards). Suppliers are scored on pieces shipped on time and quality reports. We only ever send a scorecard to suppliers based on information from the current year, so I have a field in the Scorecards table for year. The layout I am working with is based on the Suppliers table, with a portal displaying the related entries from Scorecards in the specified year.

Now, where I'm running into trouble is my attempt to retrieve a total for Shipped pieces for a specific Supplier in a Specific year. In other words, how do I get FMP to take many related records, and sum only related records based on data in a third field?

[siroos12]

Based your description, I would assume that you have SupplierID field and a global field for year in Supplier table, also a SupplierIDfk and a Year field in Scorecards table.

Make a relationship between table of current layout (in this case Suppliers) and the Scorecards table using 2 fields as below:

 

Suppliers::SupplierID = Scorecards::SupplierIDfk

AND 

Suppliers::Year = Scorecards::Year

You may call the TO of the Scorecards anything like "Scorecard Filter".

Now, to calculate the total for Shipped pieces, simply make a calculation field with below calculation:

Sum(Scorecard Filter:Shipped pieces)

Edited August 2, 2017 by siroos12

[comment]

IMHO, the simple solution is to go to a layout of Scorecards, find the records in the year of interest and group them by SupplierID. Or, if you only need one supplier's scores, do a find for their ID first (or perform GTRR from their record in the Suppliers table), then constrain by year. 

Portals do not play well with reports.

Edited August 2, 2017 by comment

[NewBoard]

23 hours ago, siroos12 said:

You may call the TO of the Scorecards anything like "Scorecard Filter".

I'm sorry, what does "TO" mean?

[Lee Smith]

Table Occurence.