10 Replies Latest reply on Sep 19, 2009 7:47 AM by MacUserDan

    report groupings



      report groupings


      I need a bit of guidance for setting up a "report". My goal is to  produce a report with the following information:


      Date Range:            (selected with each run of the report, find will work fine, only one range per report)

      donation level         (data should be grouped, sorted by this text name which is assigned from the total of donation transactions a member for the given period  .. 

                                          e.g. Gold might be $1000 - 1999, Platinum might be $2000 & over ...

                                          there may be several donations during the period) 

          member name     (just a lookup from the order information)



      Basically, I have the following files / fields within the database:


      Orders                - memberID, OrderID, OrderDate

      OrderDetail         - OrderID, transactionCode, Amount  -- certain transaction codes make up the "donations"

      Membership        - memberID, memberName



      I can get the information together but cannot seem to group and sort it correctly. Perhaps I need an additional file / fields?


      Thanks for any help.



        • 1. Re: report groupings

          Have you checked out subsummary reports in filemaker's help file? That's the type of report you describe and you should be able to set it up in a report based on your orders table.

          You also don't indicate whether each detail line corresponds to a single order or a single client. Either approach is possible with this type of report.

          • 2. Re: report groupings

            I was having trouble with the subsummary reports.


            Within the date range (orders) there can be several orders each with several items (orderDetails) for each memberID. The total of the OrderDetailAmount (a stored calculated value) for each memberID within the selected time period determines the donation level. I have created a summary (total of) field for the Amount field. The report should then be sorted / summarized by the donation level. The $$ value of donations are not to be shown on the final report.


            I don't seem to be able to get the breaks and summary totals correct. Perhaps it is the linkage of the files within the database? I am starting from the wrong table in the layout?  My selection (find) is handled within a simple script that opens the layout, performs the find, displays in preview, and returns to the calling layout.


            Does this explain enough of what I'm trying to do? I'm sure that most of the problem is how I'm looking at the problem, not the tools available. I also don't quite get the difference in the summary field and the subtotal of the amount field. A pointer to any documentation or example would help. 


            Thanks for your assistance. 



            • 3. Re: report groupings

              I'm not suprised you've had trouble. I've been playing with a test database and it took several tries before I got the results I wanted.


              You'll need some additional calculation fields and some new relationships, but once you've done that, you can base a subsummary report on your membership table to get what you want.


              Go to Manage | Database | Relationships, select your Orders table and click the button with two green plus signs to make a new table occurrence (TO) based on Orders. Name it OrdersByDateID.

              Define two global date fields in Membership: gDate1 and gDate2.

              Create this relationship:

              Membership :: memberID = OrdersByDateID AND Membership :: gDate1 < OrdersByDateID :: OrderDate And Membership :: gDate1 > OrdersByDateID :: OrderDate


              Define a global field in Orders, gTransactionCode

              Select the OrderDetail TO and create a new one labled "SelectedDetails"

              Create a relationship: 

              OrdersByDateID :: OrderId = SelectedDetails :: OrderID AND OrdersByDateID :: gTransactionCode = SelectedDetails :: TransactionCode


              Now you can create at least one additional calculation field in Membership, DonationLevel.


              Let ( Donation = Sum ( SelectedDetails :: Amount ) ;

                     Case ( Donation > 10000 ; "Platinum" ; Donation > 5000 ; "Gold" ; Donation > 1000 ; "Bronze" ; "All Others" ) )


              Now you can first specify a date range in gDate1 and gDate 2. Then select/enter a transaction code in gTransactionCode.

              Now find all membership records whose DonationLevel is NOT "All Others".

              Display the found set on a subsummary report with a subsummary part defined to be visible "when sorted by Membership :: DonationLevel."



              My DonationLevel labels happen to sort alphabetically in an order that matches the total amounts. If this is not the case in your solution. You'll need a second calculation just like DonationLevel which returns a number instead of the text. This number can specify your sort order.

              If you need to specify more than one transaction code, you can make gTransactionCode a text field and format it with check boxes. If you select two or more checkboxes, the relationships will match all selected transaction codes.

              To increase flexibility, you can replace the literal numbers, 10000, 5000, 1000, etc. with references to fields. Then you can change the qualifying donation totals simply by editing these fields.

              • 4. Re: report groupings

                Thanks so much for your help. I almost have the report working now though some parts still seem a bit of a mystery as to why it works that way ... maybe someday I'll be suddenly enlightened.


                There is still one part that gives me trouble, however. How do I select only the records that do not have "All Others" as the DonationLevel. In the script is used find to omit all records with DonationLevel ="All Others". After the sort, when I preview the subsummary report, all the members making donations seem (I have still to check out in detail) to have the correct DonationLevel and DonationAmount BUT the group "All Others" shows with all members who had transactions of any kind during the time period and those members who donated less than the minimum (1000 in this case). 


                I don't understand why the members with any kind of transaction appear in that group, I thought that they should not appear since I made the link between OrdersByDate and SelectedDetails as indicated below ... Perhaps I did it incorrectly though the amounts seem to be correct if I just select one of the transaction codes or all of them. Of course the real  problem is that the group appears at all.


                Any hints?


                Thanks again. 



                • 5. Re: report groupings

                  I described the find in general terms, leaving it to you to decide whether you want to do it by hand or in a script. The relationships will not exclude non-qualifying donors (all others) as they have nothing to do with this unstored calculation field. Instead, you have to control what records appear in your report by performing a find.


                  By hand.

                  Select a layout that includes the Donation Level field.

                  Enter find mode.

                  Enter "All others" in that field.

                  Click the "Omit" button

                  Click Perform find.

                  Sort the found set as previously described.


                  By script

                  Enter Find Mode []

                  Set Field [membership:: DonationLevel ; "All Others"]

                  Omit Record // this is the same as clicking the omit button when doing a manual find

                  Perform Find []

                  Sort [Restore; no dialog]  //click specify to set up the previously described sort order.


                  • 6. Re: report groupings

                    Thanks for the help. I finally seem to have everything working. Firstly I had to put the field DonationLevel in on the report as a field .. the find didn't seem to work with it just on the section break. I just set that field to print in "white" in the body and everything is fine. My script came out a little bit different than yours but maybe that is a difference in versions (I"m running Pro 10) but it works correctly and that is all that matters right now.


                    Thanks again for your help. 


                    • 7. Re: report groupings

                      Glad you've got it working.


                      I'm also using FMP 10 so that shouldn't explain any difference.


                      Set Field doesn't rely in any way on whether it's referenced fields are on the layout so that shouldn't be an issue in your find script.


                      Feel free to post your script here if you want to understand the differences between your script and mine.

                      • 8. Re: report groupings


                        *Set Field doesn't rely in any way on whether it's referenced fields are on the layout so that shouldn't be an issue in your find script.


                        Yesterday I certainly needed to add the two fields in order to make the report work. I must have been doing something incorrectly. Today I deleted the fields and all works well without the fields. .... go figure.


                        *Feel free to post your script here if you want to understand the differences between your script and mine.

                        Here's my script that works every time now. Instead of "All Others" I used "NONE" for records with donations below any group range. 


                        Donation Classifications

                        Go to Layout [ “Donations by Group” (tblMembership) ]

                        Show Custom Dialog [ Title: "Donation Report Range"; Buttons: “OK”, “Cancel”; Input #1: tblMembership::gDate1, "Beginning Date"; Input #2: tblMembership::gDate2,

                        "Ending Date" ]

                        Perform Find [ Specified Find Requests: Omit Records; Criteria: tblMembership::DonationLevel: “=NONE” ] [ Restore ]

                        Sort Records [ Specified Sort Order: tblMembership::DonationLevelSort; descending tblMembership::DonationLevel; ascending tblMembership::DonationAmount; descending ] [ Restore; No dialog ]

                        Print Setup [ Orientation: Landscape; Paper size: 8.5" x 11" ] [ Restore; No dialog ]

                        Enter Preview Mode

                        [ Pause ]

                        Enter Browse Mode Show All Records Go to Layout [ original layout ]


                        Your script and my notes 

                        By script

                        Enter Find Mode []

                        Set Field [membership:: DonationLevel ; "All Others"] I can only get this format by Set Field by Name. Set Field does not result in the correct selection of data.

                        Omit Record // this is the same as clicking the omit button when doing a manual find

                        Perform Find [] This gave me an error indicating that there were no records matching the specifications and showed me a blank form to modify. 

                        Sort [Restore; no dialog]  //click specify to set up the previously described sort order. 

                        This script resulted in my report having all donors including the "All Others" ("NONE") group being shown on the report. 


                        Things worked correctly when I used your manual method with the two fields added in order to set up the find manually. By trial and error I came up with my script. I even tried deleting the extra fields to no avail. I'm sure I must have been doing something incorrectly.





                        • 9. Re: report groupings

                          Hmm, the script as posted should work for you, though I see a few discrepancies that might or might not play a role.

                          Referring to both scripts, I'd add


                          Go To layout [“Donations by Group” (tblMembership)]


                          To the beginning of the script to make sure the correct table context (tbleMembership) is established for the find.

                          Set Field is completely independent from what fields are present on the current layout--I've used this script step for years--but the table context could be an issue here.

                          Set Field and Set Field by Name should produce identical results IF the the above mentioned table context is correctly established.


                          Also can't tell if it's an issue or not, but given the above line from your script, the Set Field step should read:


                          Set Field [tblmembership:: DonationLevel ; "All Others"]  //There's an extra space after the second : to avoid having an emoticon appear in the post.


                          Make sure that you've correctly specified both parameters. For tblmembership:: DonationLevel, you click the first specify button and select the appropriate field from the list. For the second, you click the second button and type the text into the specify calculation dialog.


                          The error message you got after the Perform Find was executed indicates that the Set Field did not enter "All Others" into the DonationLevel field.


                          If all else fails, send me a private message by clicking my forum name. I'll then send you my email address so you can send me a copy of the file to examine.

                          • 10. Re: report groupings

                            Thanks for the additional information. I'll try your suggestions and will get back to you if I have any problems.