3 Replies Latest reply on Mar 19, 2013 10:29 AM by philmodjunk

    Abstracting Information

    rsagall_1

      Title

      Abstracting Information

      Post

           I have a file with around 1.4 million records. Each contains information on the person who placed the order including their ID number and the amount saved. (There is a lot of other info but it's not relavent to this problem.) I want to find the top 200 people who place an order based on the number of orders they placed, find out how many orders they placed and their total savings. Then I will send them a letter with a special offer. 

           I know I could brute force this by examining each record and running counters, etc. I am looking for a more elegant and less time consuming way to find the top 200. The mail merge is not a problem.

           I am running FM 12 on a Mac with 10.7.5.

           Thanks for any ideas

           Rich

        • 1. Re: Abstracting Information
          philmodjunk

               An ID number for identifying the customer suggests that you also have a related table of customers with one record for each customer. You should use a layout based on that table to produce your list of the top 200 customers.

               Define cOrderCount in the Customer table as Count ( Orders::Customer ID ).

               Sort your customer recorsd by cOrderCount in descending order.

               Use Go to Record to go to the 201st record

               Use omit multiple records to omit that record and all the come after it from your found set.

               You now have your list of the top 200 customers.

               A summary field in Orders can compute the total saved for each customer.

          • 2. Re: Abstracting Information
            rsagall_1

                 There is no related table. The ID numbers (NPI- National Provider Indentifier) are assigned by the Department of Health and Human Services to physicians - the "customers" in my data. 

            • 3. Re: Abstracting Information
              philmodjunk

                   Then you should create such a table. It's possible to use import records with a unique values | validation always validation on the customer ID field to populate such a related table with one record for each unique customer ID.

                   It's also possible to use the "re-order" option at the bottom of the Sort Record dialog to get sorted groups of records with sub totals for each group but with such a large number of records, I think that approach will be much slower as well as more complicated when it comes to getting the needed sub totals and omitting those records not part of the "top 200".