3 Replies Latest reply on Jun 23, 2016 8:45 AM by RolandGomez

    Filtering within an executed search?


      Filemaker Pros/Users

           I'm stumped. Ok, again, I work for a real estate appraisal firm where our clients are lenders(bank of america, jpmorgan chase, comerica bank, etc). These lenders will need appraisals for a real estate loan and so what they do is ask a group of appraisers to submit a bid amount for an appraisal they need. On our end we track the number of bids we submit throughout the year for all of our clients. It tells us a few things like if we are over bidding or under bidding. I've done some pretty fancy things that demonstrate each client throughout the years, how many bids, totals of these bids...throughout the years and months...pretty cool because you can see the ebbs and flows of their needs. Anyways. The boss has never been really happy with the layout of the reports and finally he's been able to convey exactly what he wants. Problem. I'm not sure how to make this happen.


      He would like to search an entire year and then be able to see the first three months, six months, nine months, and the entire year for a specific client. So in my head I need to be able to filter 1)the number of bids from 2) each client 3) into these time periods. Is this possible in one report?

      Bid Report.png

      Thanks everyone.


        • 1. Re: Filtering within an executed search?

          You could write a script that performs the search for the 3 month period, collects the results into a set of variables, then repeats this process for the 6, 9, and 12 month periods.  Then on your report layout, display the variables instead of fields.

          • 2. Re: Filtering within an executed search?

            Hello, Roland. brsamuel has already given you one option. There are at least a few other ways to create the report, depending on your exact needs.


            1) Create a second table (either with one record for each year, or just one record with global fields). Create a pair of date fields for each time period. Then, create relationships from the report table back to your original data, one relationship per time period. At that point, your summary fields (from the context of the report) will show you the summaries for the time periods.


            2) Use ExecuteSQL to pull the data and put them into either variables or merge fields.


            3) Use scripting to create an HTML table with the data you want in each cell. (Note: This technique typically works better when you have an unknown number of columns, which is not the case here.)


            4) Use scripting (perhaps server-side) to store the data in dedicated fields in a report table. Then, use that table to display the data.


            1 is probably the easiest to implement. 4 will perform the fastest (for the end user). A lot depends also on whether you plan to keep a history of these report data, or just summon them at runtime. (Note that 4 can be combined with 2 - use ExecuteSQL to pull the data, then stuff them into the fields.)





            • 3. Re: Filtering within an executed search?

              Gentlemen (brsamuel and Mike_Mitchell)

                        I'm going to try your recommendations. Although I've just now dipped my feet into utilizing variables within my reports (I'm still very much a novice in my eyes), I will indeed get my hands dirty with these options. I'm just happy to know that it indeed can be done. Thank you so much for your directions. I'm always greatly appreciative to the help I receive here.


              Best regards,