12 Replies Latest reply on Jun 30, 2012 11:41 PM by moj6054

    How to limit data in a report?

    gingerl

      I'm REALLY new to Filemaker Pro and need some help with a database I'm creating (I've used MS Access for the last several years, so I was able to at least set up a basic Filemaker database). I have hundreds of customers and multiple job records for each customer. I need to be able to pull up a customer and print a report based on only one job record of my choosing at a time. I have the data entered and have set up a report, but I haven't found an easy way to select the specific reoord before previewing the report. In Access, I used an input box that would let me enter the job # and then generate the report for just that job...can I do that in Filemaker?

        • 1. Re: How to limit data in a report?
          fitch

          Let's say you have a customers table and a job table, related by customer ID. (I say table for simplicity, but really we're talking about table occurrences on the relationship graph.) On your customer layout, you might have a portal that shows jobs for that customer. You can put a button in that portal that goes to the related job, using a layout based on the job table.

           

          Or, you could start from the jobs layout and search for the job # there. It's all about the layout/context.

           

          I.e., print your report from the context of the job, not the customer.

          • 2. Re: How to limit data in a report?
            gingerl

            I tried that...I think.  The portal shows all jobs for the related customer record, but when I click on the portal, I can't seem to select just one of the job records and the report that opens shows all of the jobs for that customer.  My button setup on the portal is Go To Related Record with Options to: Get related record from: Job details; Show Record using the jobs report and I've checked Show Only Related Records and Match Current Record Only.  I feel like I'm missing something really obvious...

            • 3. Re: How to limit data in a report?
              pthomas

              Do you have the button with the GTRR inside the portal? So you can see one instance of the button against each of the portal rows?

               

              e.g.

              Capture.PNG

              1 of 1 people found this helpful
              • 4. Re: How to limit data in a report?
                gingerl

                Ummm...no. How do I do that?

                • 5. Re: How to limit data in a report?
                  pthomas

                  Just make your portal a little wider and the button small enough to fit into the portal row and then drag it down into the row as per the screenshot in my previous post - the grey button with the orange icon in it is configured to go to the related record.

                   

                  Cheers,

                   

                  Paul.

                  • 6. Re: How to limit data in a report?
                    gingerl

                    I added the button to the portal header and here's what my screen looks like with the GTRR button on each record.  These are two records for the same customer and, regardless of which button I click, my report still shows both records.  Ughhh...  Again, I feel like I'm missing something obvious and I appreciate your patience.

                    JobsScreen.jpg

                    • 7. Re: How to limit data in a report?
                      pthomas

                      I think you will need to create a new table occurrence based on the Job Details table.

                       

                      This new TO will need to relate back to the Job Details table on the unique identifier for Job Details.

                       

                      Then you want to set your go to related record using the the new TO.

                       

                      So if for example your graph currently looks like this:

                       

                      Customers (CustID) ---- (CustID) Job Details

                       

                      You need to add in another link like this:

                       

                      Customers (CustID) ---- (CustID) Job Details (JobID) ----- (JobID) Job Details Lookup

                       

                      Then have your GTRR working off the "Job Details Lookup" table occurrence.

                       

                      Currently as you are going to the related Job Details it will show you all of the Job Details for that customer as that is how the relationship is defined!

                       

                      If you don't want to add in the extra relationship you could script it instead.

                       

                      So have the button run a script that does the following:

                       

                      Set a variable = Job Details ID

                      Enter Find Mode

                      Go to your report layout.

                      Set the Job Details ID to the Variable.

                      Perform the find.

                       

                      Cheers,

                       

                      Paul.

                      • 8. Re: How to limit data in a report?
                        thosliot

                        You're not missing anything obvious. When you do a GTRR, matching current record only, the resulting foundset will be all records related to the current record (as you've found); putting the button inside or outside the portal only affects which record is current after the GTRR - from inside the portal, the portal record will be current, whereas from outside, the first record (either unsorted or sorted depending on the relationship definition) will be current.

                         

                        With the button inside the portal, probably the easiest (but not necessarily the quickest) way to isolate the required Job record is to script the button as follows:

                         

                        Freeze WIndow

                        Go To Related Record

                        Show All Records

                        Omit Record

                        Show Omitted Only

                         

                        cheers

                         

                        Tom

                         

                        On 5 Dec 2011, at 21:19, gingerl wrote

                         

                        I added the button to the portal header and here's what my screen looks like with the GTRR button on each record.  These are two records for the same customer and, regardless of which button I click, my report still shows both records.  Ughhh...  Again, I feel like I'm missing something obvious and I appreciate your patience.

                         

                        • 9. Re: How to limit data in a report?
                          gingerl

                          Tom...thanks so much!!!  I used the script you outlined and it works beautifully!

                          • 10. Re: How to limit data in a report?
                            gingerl

                            Paul..thanks for your patience and advice.  We were so close!  I used the script Tom outlined in his post and the button you showed me how to add works like a charm.  Thanks again!

                            • 11. Re: How to limit data in a report?
                              fitch

                              Probably not a big deal, but be aware that the "Omit /Show Omitted" method can potentially be problematic in a multi-user setting. It would be rare, but it can happen. I would vote for the "Find" script that Paul outlined, it's more airtight and should be slightly faster.

                              • 12. Re: How to limit data in a report?
                                moj6054

                                Hi, I have a question like for this topic.

                                How can show only records that are Greater than 0?

                                I mean, when I have a purchase list and then some of that product buy, for second print of this purchase list, I can see only those product that does't buy?