1 Reply Latest reply on Jan 20, 2017 9:20 AM by philmodjunk

    field that summates from search


      Has anyone made a field that adds numbers from a searched set of another field in a different table? In this case I want the field to show a dollar total of active projects for a particular agency in the agency table. So the search would be based on the agency that is displayed in the current layout/table and would sum dollar amounts from a list of projects from that agency that are active by searching for "active" in a field in a different table. The project dollar amount and the "active" fields are in the other table. The tables are related by the agency abreviation.

        • 1. Re: field that summates from search

          This is a very common database task.


          The details of how you do it, depend on what method you use in your "search" as this is something that might be done via:


          a) a relationship that matches only to the records for which you want your total

          b) A scripted find that finds the records and then returns this total to a record from which the search was initiated

          c) ExecuteSQL()

          d) A filtered portal where portal filter and related records list the records from which you want a total--not a good option if you then want to use that value in a calculation, but it is possible to do so.


          Summary fields can compute totals based on a set of related records (a) or a found set (b). There are SQL aggregate functions such as sum that can be used (c). FileMaker also has an aggregate function, sum that can be used with a).  And a summary field can report a total based on a filtered portal (d).

          1 of 1 people found this helpful