1 Reply Latest reply on Jul 19, 2017 12:02 PM by philmodjunk

    filtering reports to obtain records (scripts)

    kj_thegreat
    kj_thegreat Jul 19, 2017 11:55 AM

      We need to obtain a value from a record based on filtering an unknown amount of records in a summary field on a different layout than the report, for the date the record was created. Sorry for the long sentence. Just trying to explain my self. Were running fmp14.

      Thank you!

      Please ask questions if there is anything you need to know.

      • 21 Views
      • Categories: Data, Calculations, Scripting, Reporting
      • Tags:
        • 1. Re: filtering reports to obtain records (scripts)
          philmodjunk
          philmodjunk Jul 19, 2017 12:02 PM (in response to kj_thegreat)

          Is the "different layout" based on the same table occurrence as that of the report's layout?

           

          What kind of filtering? What data/criteria specifies which records to include or exclude?

           

          General options that might work:

           

          1. Set up a relationship that matches to the set of records from which this value must be computed, then an aggregate function, Sum, count, average, etc. can compute a value based on the set of related records.
          2. Use the same relationship, but define a summary field in the related table and reference it to get/show the needed value.
          3. Use ExecuteSQL to compute the needed Aggregate value.
          4. A summary field inside a filtered portal might produce the needed value in cases where a relationship might not work.
          5. A script can perform a find using a layout based on a different table, set a variable to the value of a summary field from that table and then return to the report layout where either the variable can be used to show the value or it can set a field in order to show the value.