13 Replies Latest reply on Jan 23, 2017 2:33 PM by philmodjunk

    Filter report by year

    avallejo

      Hi all.

       

      So I pulled off this report where I can count how many surgeries each surgeon performed by year/month.

      Now I'm trying how to filter this by year, so if type the year at the white field on the header, it filters that way. Tried to build a script to do that, but can't figure it out.

      Any hints?

       

      Thanks!

       

      AndréCapto_Capture 2017-01-22_01-54-40_PM.png

        • 1. Re: Filter report by year
          siplus

          the "button" you've built is a field. Should be a global, with a year value list attached to it. Once you fill in data and commit, fire a script that GTRR and sort.

           

          Let's say you're sitting on a surgery dashboard table; you have a gYear global in it with a relationship Dashboard::gYear matching Surgeries::year. Once you change the value in gYear you do the GTRR and execute a script in the surgeries table which sorts them by

           

          - doctor

          - month

          • 2. Re: Filter report by year
            avallejo

            Hi!

             

            So that's what I did.

            • I have a table where I store my globals (global_tbl). Created a global field called year_filter, set it to number, global. It's the field on the report's header.
            • Another table that has surgery data. There a field with a calculated value set by Year (surgery_date)
            • I made a relationship bonding this two fields at the Relationship graph.
            • Then I created a script that does GTRR with the following parameters: Show only related records ; Match found set ; from table global_tbl ; using current Layout.   The "current layout" here is the report itself right?

             

            When I go back to the report and run the script, it says that it won't do it because "this layout cannot show the result...

             

            What am I doing wrong?

             

            One information , not sure if relevant. The two tables are in different files, one in the frontend the other in the backend, but they are linked and all relationships works normally, so I think it does not matter, right?

             

            Thanks!

            • 3. Re: Filter report by year
              JackRodges

              You don't need a related table for the date find.

              Use a button to run this script:

              go to layout report

              set field global to year (you can use a field on the layout or use a custom dialog with the field set for entry

              set variable $year to global

              perform find calc year field = $year

              sort report

               

              If the field you are using is a global in the report layout table, you can make it a trigger to run this script after you enter the year number and tab out or press enter.

               

              (on modify trigger)

              go to layout report

              set variable $year to global

              perform find calc year field = $year

              sort report

              1 of 1 people found this helpful
              • 4. Re: Filter report by year
                siplus

                using current layout is the problem, you should use a specific layout in the RR destination table.

                 

                Generally speaking, I get sick when I see <current layout> in GTRR. It is idiotic that FMP defaults to this. 

                You have to know and specify where you are going.

                • 5. Re: Filter report by year
                  avallejo

                  Thanks both of you, but gofmp tip save the day!

                   

                   

                  This forum is amazing...

                  • 6. Re: Filter report by year
                    philmodjunk

                    With the data entered/selected in a global field, there's no need for the variable.

                     

                    Enter find mode [ ]

                    Set field [Yourtable::Year ; YourTable::GlobalYearField ]
                    Perform Find [ ]

                    1 of 1 people found this helpful
                    • 7. Re: Filter report by year
                      avallejo

                      Cool!

                      • 8. Re: Filter report by year
                        avallejo

                        Now, what if...(we're never satisfied...)

                         

                        What if I wanted the find to be true if the surgeon's name is on the "surgeon" field OR in the "2nd_surgeon"field ?

                         

                        Because sometimes he's not the main one, but the auxiliary, and I don't wanna be unfair with anyone...

                        • 9. Re: Filter report by year
                          JackRodges

                          set var $find to global field

                          perform find field $find

                           

                          is ONE script step shorter... 

                          • 10. Re: Filter report by year
                            JackRodges

                            Create your find as normal and then click the duplicate button.  Click Edit and change the field to the second surgeon field. Now the find is an OR.

                             

                            Double Find.PNG

                            • 11. Re: Filter report by year
                              philmodjunk

                              Say you have a global field for specifying the year and a global field for selecting the surgeon. I'd script the find this way:

                               

                              enter find mode [  ]

                              set field [ YourTable::year ; YourTable::globalYear]

                              set field [ YourTable:: Surgeon ; YourTable:;globalSurgeon ]

                              new record/request

                              set field [ YourTable::year ; YourTable::globalYear]

                              set field [ YourTable:: 2nd_Surgeon ; YourTable:;globalSurgeon ]

                              set error capture [on]

                              perform find [  ]

                              1 of 1 people found this helpful
                              • 12. Re: Filter report by year
                                avallejo

                                Working on it...will take some time!

                                Thanks

                                • 13. Re: Filter report by year
                                  philmodjunk

                                  You might find this thread of interest:

                                   

                                  Scripted Find Examples

                                  1 of 1 people found this helpful