2 Replies Latest reply on May 22, 2014 3:13 PM by BrianClark

    Find and export records - only the highest value in a category

    BrianClark

      Title

      Find and export records - only the highest value in a category

      Post

           Hi folks,

           As a relative newcomer to FMP, I am trying to export some records and am having difficulty.....

           2 of the tables in my database are:

           MANUFACTURER [mName, contact email, contact name]  (approx 50 records)

           PRODUCT [pID, pName, description, price, mName]  (approx 300 records)

           So, a manufacturer makes many products.....

           I want to export each manufacturers most expensive product, but only the top 10.

           This is probably quite straight forward, but I just cant quite get my head around it!  Any help appreciated.  I am using FMP 12

            

           Many thanks

            

           Brian

            

        • 1. Re: Find and export records - only the highest value in a category
          philmodjunk

               Define a relationship matching Manufacturer to Product by mName (not how I would match records, manufacturers change their names just as people do--Id use a MfgID field.), but in that relationship, specify a sort order by Price in descending order. (double click relationship line and click the sort button.)

               You may want to use separate Tutorial: What are Table Occurrences?for this to keep existing relationships unmodified.

               Define this calculation field, cTopTen, in Manufacturers:

               LeftValues ( List ( Products::pID ) ; 10 ) // be sure to specify the table occurrence for Products from the same relationship where you specified the sort order.

               Define a new relationship to a new occurrence of Products:

               Manufacturers::cTopTen = Products|TopTen::pID

               "Products|TopTen" is my name for a new table occurrence of Products.

               Now you can use Go To Related Records with this relationship to pull up a found set of top 10 products for either one manufacturer, some manufacturers or all manufacturers.

               And this then becomes a found set that you can export.

          • 2. Re: Find and export records - only the highest value in a category
            BrianClark

                 thats really helpful!  Very much appreciated.... i was trying to use the max function, but was obviously so far off track!

                  

                 I did find a similar walkthrough using access for a similar task here - http://www.databasedev.co.uk/access_max_function.html - and tried to translate it into FMP, but got totally confused :)