4 Replies Latest reply on Nov 3, 2011 4:08 PM by PeterDowns

    Report Filtering

    PeterDowns

      Title

      Report Filtering

      Post

      Hi

      I have a table called ProjectedCosts and a relationship with a duplication table called ProjectedCosts_List. I have a a layout that displays data correctly based between selected dates. (The parent table is ProjectedCosts and the portalTable is ProjectedCosts_List) I have 2 global date fields g_startDate2 and g_FinishDate2 which are selected by individual calendar fields on the layout. A  portal is filtered by these dates and displays the correct data based on a task start date and a task finish date. So that works really well. Here is my filtering script - 

      ProjectedCosts_List::Date_Start ≥ ProjectedCosts_List::g_StartDate2 and ProjectedCosts_List::Date_Start ≤ ProjectedCosts_List::g_FinishDate2

       

      What I am having problems with is a report that is printed from this layout. When I set up the report it displays all the records rather than the filtered list. I have tried to set up a script different ways when the print option is selected, but that doesn't work either (see below) - 
      Any ideas would be appreciated 

      Cheers

      Downsy

      script.png

        • 1. Re: Report Filtering
          philmodjunk

          Perform FInd/Replace is used to search for data within a field and changed it, not the step you want here.

          Use this script:

          Go To Related Record [Show only related records; From table: ProjectedCosts_List; Using layout: "ProjectedCosts_List" (ProjectedCosts_List)]
          If [ not Get ( LastError ) //there are related records to go to]
             Enter Find Mode [] //clear the pause check box
             Set Field [ProjectedCosts_List::Date_Start ; ProjectedCosts_List::g_StartDate2g_FinishDate2 & "..." & ProjectedCosts_List::g_FinishDate2]
             Set Error Capture [on]
             Constrain Found Set
          End If

          Note: GTRR, does not change layouts if there are no related records, so you always need to check either for the presence of related reocrds for for an error code when you use this step if you have any script steps located after it in your script or they can do some really unfortunate things to your database because they are operating on the wrong layout and/or window.

          Note, if ProjectedCosts_List and ProjectedCosts are occurrences of the same data source table, the GTRR can specify a layout based on ProjectedCosts instead of ProjectedCosts_List, but the "from table" parameter must still refer to ProjectedCosts_List, not ProjectedCosts.

          • 2. Re: Report Filtering
            PeterDowns

            OK thanks for the quick reply.

            I put your script in and it did filter some results. Unfortunately it is picking up interesting data. Can't work this one out.
            The screen layout shows all correct data for whatever start or finish dates you pick. When you select the popup report it shows the correct data for the month of January 2011 no matter what date you select. December 2010 is the first month of entered data if that affects anything. So any help would be good.

            Script details - 

            • 3. Re: Report Filtering
              philmodjunk

              Please take another look at my posted script. I posted what you have here, then, minutes after posting it, spotted a flaw in how it would work and modified the script. Use the modified version and see what happens. (You must have opened the thread very quicly after I initially posted this script. Wink)

              • 4. Re: Report Filtering
                PeterDowns

                Awesome 

                worked a treat