1 Reply Latest reply on Nov 16, 2009 3:14 PM by philmodjunk

    Prepare report from master and dependent table using only certain rows from dependent table

    datadog1

      Title

      Prepare report from master and dependent table using only certain rows from dependent table

      Post

      I would like tips on how to build a report with fields from a master table and fields from a dependent table where the rows displayed from the dependent table are only those meeting certain criteria.  For example, the master table contains subscribers; the dependent table contains begin and expire dates of subscriptions.  A subscriber may have had several subscriptions over the years, but can only have one has not expired--which is indicated by the expire date being greater than today's date.  I would like help in developing a report which would include the subscriber name from the master record and the Max(ExpireDate) from the dependent record if and only if the Max(ExpireDate) from the set of matching rows in the dependent table is earlier than today's date.

        • 1. Re: Prepare report from master and dependent table using only certain rows from dependent table
          philmodjunk
            

          Peform a find to find only those subscription records that are not expired.

           

          In find mode, enter < "Todays Date" into the expiration date, replacing "Todays Date" with the current date and perform the find.

           

          I'm assuming your master table holds subscriber records and relates to the subscriptions table by a SubscriberID field.

           

          Use Subsummary parts to show fields from the master table, specify your Subscription table's SubscriberID field as it's "sort by" field.

          Sort  your records by the same SubscriberID field and you are done. The report will be visible in browse mode if you are using FMP 10. With older versions, preview or print the report to see what it looks like.