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

    Create list of matches report

    m10muhammad

      Title

      Create list of matches report

      Post

      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

                                                                        1021

                                                                        2342

                                     R02                           1001

                                                                        1200

      0                         R13                            981

       

       

      Please advise regarding how to create this report. 

       

      Thanks in advance. 

       

      Muhammad. 

        • 1. Re: Create list of matches report
          philmodjunk

          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

           

          Machine
          1000

          1021

          2342

          etc.

          Contact 01   Request: R02

           

          Machine

          1001

          1200

           

          Etc.

           

          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
            m10muhammad

            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.

             

            Thanks.

             

            • 3. Re: Create list of matches report
              philmodjunk

              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
                m10muhammad

                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

                                                      R01

                      2                                           R01

                                                      R02

                      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.

                 

                Thanks. 

                • 5. Re: Create list of matches report
                  philmodjunk

                  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
                    m10muhammad

                    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
                      philmodjunk

                      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
                        m10muhammad

                        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
                          m10muhammad

                          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?

                           

                          Thanks. 

                          • 10. Re: Create list of matches report
                            philmodjunk

                            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
                              m10muhammad

                              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.

                               

                              Thanks. 

                              • 12. Re: Create list of matches report
                                philmodjunk

                                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
                                  m10muhammad

                                  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
                                    philmodjunk

                                    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