Jump to content

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


This topic is 2429 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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 by siroos12
Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

This topic is 2429 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.