1 2 Previous Next 18 Replies Latest reply on Jun 24, 2010 6:23 PM by m10muhammad

    Create list of matches report



      Create list of matches report


      Version: FileMaker Pro 11 on Mac OS 10.6.2


      Background: My database is used for tracking printing machinery specifically relating to purchases, sales, inquiries, etc. There are three main tables: Contacts, Machinery, and Requests. The Contact table includes all of the people (customer, dealer) associated with the database. The Machinery table includes information on each machine either in stock at the company or available for purchase. The Request table has a form where customer's inquiries can be stored (i.e. they ask for specific make, model, and provide a budget). There is also a relationship that allows for a portal to be made on the Request layout which will list all of the items in Machinery table that have identical characteristics to those in the Request record (so if the request is for a 1999 Komori 2000, the page for that request has a portal which lists any machines in the Machinery table that match those criteria). 


      What I want to do is create a columnar list of matches report with columns Contact, Request and Matches where each Contact (identified by Contact ID) will have a list of all requests, and in turn each Request (identified by RequestID) will have a list of matches (identified by MachineID). There is a relationship between Contact and Request that would make this possible. It would look like below:


      Contact ID             Request ID              Machine ID

      01                           R01                           1000



                                     R02                           1001


      0                         R13                            981



      Please advise regarding how to create this report. 


      Thanks in advance. 



        • 1. Re: Create list of matches report

          Base your report on the Machinery table. Put the Contact,  and Request fields in a subsummary part when "sorted by" RequestID. You can also place fields from Contacts and Requests in this header to show more information such as the Contact's name as well as the contact number.


          That can give you a report that looks like this:


          Contact: O1        Request: R01






          Contact 01   Request: R02








          I know that's not the exact format you've requested, but it's easier to set up. If you can work with that fine. If not, there are ways to modify the above report to see the format you've requested.


          Here's a link to a simple tutorial on setting up summary reports that you may find useful:

          Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Create list of matches report

            Thanks for the quick response but I am a bit new to this application so I have some questions if you don't mind.


            I created the report entitled "List of Matches" and I de-selected the included subtotals/grand totals option. Then it went to the Specify Fields option. What fields do I enter here? Only Item ID, Contact ID/Name/Number etc. and Request ID? 


            Then there is a Organize Records by Category window. What do I put here? Request ID?


            Then there is a theme selection, header/footer, script creator, etc. which I assume are for modifying the appearance of the report and not for what is actually displayed on it.


            Please advise as to which fields to enter in the Specify Fields window and in the Organize Records by Category window.




            • 3. Re: Create list of matches report

              In Specify fields, you can select all the fields you want to see in your report. You can reposition them, add more fields, take fields off the layout etc. when this wizard finishes so don't worry about this step--as you can fix things after the fact quite easily if you need to.


              Request ID is a good choice, based on what you've posted so far, for the "organize" field. Again, you can fix this after the fact if you need to.


              Using those options, I think you'll need to add the sub-summary part I described earlier after you are done. You can select Part Setup... from the Layouts menu (in layout mode) to do that.


              The important details are to specify Request ID as the "sorted by" field for the subsummary part and to then sort your found set of records by this field in order to get the subsummary part to appear.

              • 4. Re: Create list of matches report

                OK I went to Part Setup and there already exists a Sub-summary sorted by Request ID. But when I view the report it looks like:


                Contact ID              Request ID


                      2                                           R01


                      6                                           R02


                From what I see, it is sorting it by Request ID but giving me the Contact ID and Request ID for each Request ID (whereas I would need the Request ID and Machine ID for each Contact ID - so each Contact ID would have a variable number of Request IDs which would each have a variable number of Machine IDs). 


                Please advise.



                • 5. Re: Create list of matches report

                  In layout mode, drag the contact ID field into the sub summary layout part. Remove Request ID from the body and use the field tool to add the machine fields you want to the body.


                  You can move fields around, resize them and even resize the layout parts by dragging the boundary lines between the parts up or down.


                  This is what I meant when I said you could easily fix things after the new layout wizard is finished. (Though improved with filemaker 11, I prefer to just create a blank layout and use Part Setup... to design a summary report instead of trying to use the wizard--it just doesn't get enough parts of the system in the right place to be worth the effort in my opinion.)


                  • 6. Re: Create list of matches report

                    So basically in the subsummary layout part I would have Contact ID and Request ID while in the body part I would have Item ID and associated machine information, all the while having the subsummary sorted by Request ID? Sorry for all the questions...


                    When I do this I see various machines but I don't see any Contact ID or Request ID fields, is it something to do with selecting to show records before/after part? 


                    My layout is organized as such


                    SUBSUMMARY PART 


                    Contact ID             Request ID


                    BODY PART


                    Item ID    Manufacturer    Model


                    and when I see it in Browse mode, this is what I see:


                    Item ID: 1014       Akiyama     SF-2000



                    Item ID: 1013      Bobst            232-222



                    Item ID: 1016     Akiyama       SF-3000


                    Please advise, thanks. Sorry for the inconvenience. 

                    • 7. Re: Create list of matches report

                      How have you sorted the records?


                      Subsummary parts are invisible if the records are not sorted by the break (sorted by) field specified when you created the sub summary part.


                      Summary reports also will not display correctly in browse mode in versions older than filemaker 10. If you are using an older version, you have to enter preview mode or print the report.

                      • 8. Re: Create list of matches report

                        The subsummary is sorted by Request ID (Leading). I am using Filemaker 11. 


                        Is there a need to include either the Contact ID field or Request ID field in the body or only in subsummary. It seems like Contact ID and Request ID are equal in the layout (i.e. they are alongside each other in the subsummary part) but I want the report to be first sorted by Contact ID and then Request ID, with each Contact having a variety of associated Requests and each Request having a variety of associated Machines. The report is based on the Machinery table, is this correct or should it be based on Request or Contact table? 


                        Please advise. Thanks. 

                        • 9. Re: Create list of matches report

                          Perhaps it would help if I give a description of the relationships that exist between the tables in question.


                          The Machinery table has a relationship to a table occurrence of the Request table (RequestCopy) (the related fields are Make = Make, Model = Model, etc. which basically match the specifications in a Request to a Machine in the Machinery table). The table occurrence of the Request table (RequestCopy) is related to a table occurrence of the Contact table (ContactCopy), with the Requested By field in RequestCopy related to the Contact ID field in ContactCopy. 


                          Conversely, there is a table occurrence of the Machinery table, MachineryCopy, which is related to the original Request table (with Make = Make, Model = Model, etc.), and the original Request table is related to the original Contact table (Requested by to Contact ID). 


                          So from what I know, I could either base the report on Machinery/RequestCopy/ContactCopy OR MachineryCopy/Request/Contact. Which table should I use? And does this information about the relationships bring to mind anything else I should to to fix my current predicament?



                          • 10. Re: Create list of matches report

                            The layout should be based on machinery.


                            You can sort by contact ID and Request ID and the subsummary part will be visible.


                            Playing games with how you have sorted your records, in fact, is one way to use a single report layout and get different types of reports--though that's not what we are attempting here. Just make sure you've actually sorted your found set of records specifying an order that includes the Request ID field.

                            • 11. Re: Create list of matches report

                              So what I did was to create the report based on Machinery table with two sub-summaries, the first by Contact ID and the second by Request ID. What this gave me was the following format:


                              Contact ID

                                                   Request ID

                                                                         Machine ID


                              This is what I want to it's good, except for the fact that the machines not associated with a Request (i.e. that do not match the characteristics of a request) show up in a list at the bottom, like the following:


                              Contact A

                                                   Request 1

                                                                         Machine I

                                                                         Machine II

                                                   Request 2

                                                                         Machine III


                              Contact B

                                                   Request 3

                                                                       Machine IV


                                                                       Machine VI

                                                                       Machine VII

                                                                       Machine VIII

                                                                       Machine IX

                                                                       Machine X


                              Is there a way to format the report so that machines not associated with a request (i.e. Machines VI - X) do not show up in the report?


                              Please advise.



                              • 12. Re: Create list of matches report

                                You would perform a find for just the contacts and/or requests you want for your report. Since this find will not keep those material records in the found set, they won't appear in your report.

                                • 13. Re: Create list of matches report

                                  The report is supposed to show all contacts with all associated requests so as to provide an overview of any matches between requests and machines available. It works well in that it only shows the contacts who have requests, and only shows the requests that have matches, but the issue is that it shows all machines, instead of only those matching with a request.


                                  Please advise. Thanks.  

                                  • 14. Re: Create list of matches report

                                    The following find will drop off the extra records:


                                    Enter find mode.


                                    Put an asterisk in the related Contact ID field


                                    Choose the omit option


                                    Perform the find and sort the records.


                                    You can script this so that this all takes place with one mouse click


                                    Go to layout [//your report layout]

                                    Enter find mode[]

                                    Set field [Contacts::ContactID ; "*"]

                                    Omit Record

                                    Set Error capture [on]

                                    Perform Find []

                                    Sort [No dialog; Restore ]

                                    1 2 Previous Next