6 Replies Latest reply on Aug 30, 2011 1:02 PM by philmodjunk

    Organizing by top account



      Organizing by top account


      I have a database of clients that placed multiple orders throughout the year.


      i can export them all in excel then add them all up to give me a total number.  and then even add up each instance of a client's name to get the total from each client.


      but is there a way to have FM do this for me?  to export my accounts in order of who ordered the most to whom ordered the least?  without ahveing every order?  just one number added up completely?



        • 1. Re: Organizing by top account

          First, you can define a summary field that computes an order total.

          Next, sort your records by a common client field to group them by client.

          Then you can export your records to Excel using the "grouped by" option to group your records by client and export this summary field as the total.

          This will produce one row for each client in your exported data.

          You can also use this summary field with a sub summary part to produce the same report format from within FileMaker.

          • 2. Re: Organizing by top account

            You can:

            - create a summary field of the total order value.

            - find all the relevant orders.  From the Help Page for Sorting:

            2.            In Browse mode, click Sort in the status toolbar, or choose Records menu > Sort Records.

            3.            Move the grouping field (eg: CustomerID) to the Sort Order list, or select it if it is already there.

            4.            Select Reorder based on summary field or click Specify.

            5.            In the Specify Field dialog box, select a summary field (Total Sales) and click OK.

            6.            Select a sort order (in this example, Descending order).

            7.            Click Sort.


            That will sort the records the way you want them.  Then choose File -> Export Records and export the CustomerID and summary values only.  You will have one exported record per Customer, with their total order value, in total order value order.

            • 3. Re: Organizing by top account

              these are new terms to me and i tried looking them up but you are def a faster and more knowledgable resource.


              can you explain what you mean by:

              -define a summary field

              -sort by a common client field

              -using "grouped by" option -in the gourp by section it just says (unsorted)

              -use this summary field with a sub summary part to produce the same report format from within FileMaker.



              • 4. Re: Organizing by top account

                In FileMaker, you can define fields with a number of different specified types in Manage | Database | Fields. One of these field types is "summary". Define a new field and select this as the field type and you'll get a dialog that pops up where you can specify what field it is to summarize and how it is to do so.

                You haven't indicated what fields you have defined already in your table. If you are going to export a total for each client, you must have some field defined in the table that identifies the client in each record. Sort your records by this field. In most cases, this will be a clientID field that links you to a client table, but I don't know if you have set something like that up or not. If you do, you can also choose to sort by a client name field int he related client table. Either way, this sort groups all your records in an order that groups them by client.

                You can't use the grouped by option unless you first sort your records in order to group them.

                You can create a summary report in FileMaker that looks exactly like what you want to export as an excel file. If you are interested in creating such a report, take a look at this tutorial that walks you through the creation of such a report and which also demonstrates several variations that are possible--including one that is one row for each client like your excel file export.

                Creating Filemaker Pro summary reports--Tutorial

                • 5. Re: Organizing by top account

                  So i created a Summary Field called 'Client Total Summary'.  in the options i chose the radio button for 'Count Of' (bc that is the only button that shows my 'Business Name' field).  then i chose 'Business Name' in the available fields box.

                  this is where it gets messed up.

                  i performed a search for all of my records from 1/1/2011...8/30/2011 to give me a range of records.

                  i then exporte3d records as an excel format.


                  in the specify filed order for export box, in the 'group by' box i chose 'Business Name' bc 'Client Total Summary' wasnt available.  then in the field export order box below, i chose 'date created,' 'PO #,' 'Shipping month,' 'Final cost,' and 'Business Name.'

                  i hit export and the dialogues box stating "the export type does not support repeating fields.  Only the first item from each repeating Field will be exported."

                  how do i fix this issue?

                  • 6. Re: Organizing by top account

                    First, according to what you just posted, you didn't sort your records. If you do not sort your records, they will not be grouped by client.

                    Also, the options you selected ("count of" business name) report the number of orders, not a total value. I don't know if this is what you wanted here or not. If you wanted a total of the final cost, then you'd select the final cost field and specify the "total of" the final cost field.

                    Finally, to "fix the issue", I suggest eliminating the repeating fields by breaking up your repetitions into individual records instead of one record with repeating fields. That's a general recommendaion based on very little information as this is the first that I've heard from you that such repeating fields even exist in your system, but it's almost always a better database design as you'll find numerous limitations due to using repeating fields were a table of related records will not have the same limitations.

                    I have in fact, assumed that you have many records for the same client with one record per order and that you intend to export one row per client to your excel file.