10 Replies Latest reply on Dec 2, 2014 11:45 PM by lukasb

    Date Range Search

    lukasb

      Title

      Date Range Search

      Post

      Hi, 

      I have a (Client's) layout that shows info into portals from two related tables (Clients Invoicing & Office Payments towards the client). I'm trying to search records (within a date range) of the two related tables and show them on the portals of my main client's page.

      I would appreciate if you could please have a look at the linked file. I'm sure I'm missing something very basic, but I cannot figure out what it is.

      https://www.dropbox.com/s/ez25yixmnyjdsgd/date_range.fmp12?dl=0

      Thank you in advance,

      Lukas 

        • 1. Re: Date Range Search
          applelakshan1

          Hello, 

          you should have date field where you can enter the date  value to each record, lets call it DateX for the moment.

          all you have to do  is 
          create 2  new global date fields  DateFrom  and DateTo (any name)

          Then  link the drop down calender to the both fields (to make it easy)

          create script with this steps

          Enter find mood

          SetField DateFrom&"..."DateTo ----->  DateX

          perform find

           

          after , it should filter the records  within that date range

           

           

           

          • 2. Re: Date Range Search
            lukasb

            Hi applelakshan1,

            Thanks for the quick response. I feel this is what I've done (i'm attaching a screenshot) but still it doesn't work. It says that "The provided find criteria is not valid" when I perform the find.

            Lukas 

            • 3. Re: Date Range Search
              applelakshan1

              hmm you are looking for filter the records in portal . that is something i have to check 

              • 4. Re: Date Range Search
                philmodjunk

                Your set field script step is not set up correctly. There shouldn't be an extra set of [ ] showing in the script editor.

                When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                • 5. Re: Date Range Search
                  lukasb

                  Thanks PhilModJunk!! Well Spotted!

                  The find is perfectly performed now this is one big step forward for me! :) 

                  However I do not get the results shown on the portal. Is there a way to do that?

                  Lukas

                  • 6. Re: Date Range Search
                    lukasb

                    My last question has already be answered by PhilModJunk here:

                    http://forums.filemaker.com/posts/8a71487ff6?commentId=175588#175588

                     

                    My last question of the problem:

                    Is it possible to get a sum of the "amount" field in the filtered portal?

                    Lukas 

                    • 7. Re: Date Range Search
                      philmodjunk

                      Define a summary field in the portal table to sum the amount field. Put it in a one row portal with the same portal filter as your first portal.

                      • 8. Re: Date Range Search
                        lukasb

                        I'm sorry to bother you again, but I cannot make it work. 

                        This only gives me the sum of the amount of all records, even though I've copied-pasted an identical portal as you suggested and placed my sum field within the new portal. The search date range, doesn't seem to affect the result. 

                        surprise

                        Lukas

                        • 9. Re: Date Range Search
                          philmodjunk

                          Better check the details as this is a very commonly used method to get a total based on the records visible in a filtered portal. This is not a Sum calculation field, by the way, it's a summary field that you define in the portal's table.

                          • 10. Re: Date Range Search
                            lukasb

                            Just did it! Amazing!! :) Thanks again PhilModJunk!

                            Your answers helped me a lot throughout this exercise!!

                            Cheers,

                            Lukas!! :)