8 Replies Latest reply on Jun 28, 2010 10:12 AM by philmodjunk

    requests without matches



      requests without matches


      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 report that will list each Request with pertinent information (make, model, budget, requested by, etc.) that do not have any matches. The report that I have simply lists all of the Request records in the system, but is there any way to display the layout where only the Requests that do not have any matches are shown (i.e. the List of Matches portal on that specific Request record in empty).


      Please advise. Thanks. 

        • 1. Re: requests without matches

          It seems to me this question is about how to Find via a relationship. The thing is, you cannot Find for "=" (empty) in a related field (portal or single field, doesn't matter); the relationship is not valid for those. What you can do is Find for "*" (something), then Omit.

          • 2. Re: requests without matches

            There is a relationship between the Request and Machinery tables in that if Make, Model, Year, and Budget in the Request table = Make, Model, Year, and Price in the Machinery table, the specific records in the Machinery table are displayed in a portal for each Request portal. So is there a script that can omit all of the Requests which have matching Machines (i.e. omit all Request records with something in the portal)?


            Please advise. Thanks.

            • 3. Re: requests without matches

              I suggested that in your other thread:


              Enter find mode []

              Set Field [Relatedtable::Field ; "*"]

              Omit Records

              Perform find[]


              Omit records does the same operation in this script that clicking the "omit" button does in manual finds.

              • 4. Re: requests without matches

                This report does not show any Machines, only the Request and Contact information as follows:


                Request ID     Name     Company      Make      Model      Year       Budget



                It is based on the Request table. 


                As of now, the report includes every record in the Request table. What I want to do is omit the records which have matches in the Machinery table. For this to happen, do I need to include fields from the Machinery table as well? If so, how would the report be organized so the final version would just provide a list of Requests that don't have matches. 

                • 5. Re: requests without matches

                  You do not need to add these fields to your layout. Just run the script I've suggested subsituting your table occurrence and field names for the placeholder Relatedtable::field that I used.

                  • 6. Re: requests without matches

                    But which field would I enter "*" in? This report is basically a list of all the Requests in the database. In the Form view, there is a portal on each Request record that shows any matching items in the Machinery table. In the list of the Requests, there is no portal obviously so it only shows information inherent to that table (i.e. only fields from the Request table). The purpose of this report is to provide a quick look at all the Requests that do not currently have matches. So as far as I can tell there is no field in the Request table where I can enter "*" and see only the Requests without matches.


                    Put simply, when I enter Find mode on the Request layout, I enter "*" in the Machine ID field in the portal (i.e. linked to the Machinery table), and choose to Omit Records. This Find gives me all the Requests without matches. What I want is this found set in a report format. 

                    • 7. Re: requests without matches

                      Never mind, what I did was add the Machine ID field to the report and ran the script you suggested with Machine ID set to "*". It worked. Thanks.

                      • 8. Re: requests without matches

                        Glad it worked, but you don't have to add the field to your layout for the script to work.