1 Reply Latest reply on Feb 24, 2011 8:27 AM by philmodjunk

    Filtering a portal? Script? Calculation? Help please

    kilbot

      Title

      Filtering a portal? Script? Calculation? Help please

      Post

      Okay,

      This is what I am trying to achieve with my database

      I enter contracts which have a goal amount, then using a portal I pull over the companies that are performing on the job and the amount they are contracted for. What I need to know is how many companies (trying to get the highest number possible) it took to reach the goal amount. I want to get the number of companies that were needed but still would like to show all of the companies in the portal that performed on the contract.

      Example:

      Contract#123                                         CONVERT TO

      Goal $5000

      portal

      Toms Painting $1000                                                               Toms Painting  $1000

      Jays const       $2000                                                               Jays Const       $2000

      bobs trucking  $5000                                                               Dicks Coating    $3000

      dicks coating   $3000                                                               Bobs Trucking   $5000

                                                                                                          Total # of companies to reach goal: 3

      Then I need to know how many companies were over the goal. Based on the example above the number would be 2 companies.

      Dicks Coating

      Bobs Trucking

      bioth of these went over the $5000 goal mark

      Thank you

        • 1. Re: Filtering a portal? Script? Calculation? Help please
          philmodjunk

          If I remember correctly, in an earlier post, I suggested using a filter on a running total to filter out the contractors where the running total exceeded the goal. I can see that that approach filters out one too many contractors as it would filter out Dicks and Bobs instead of just Bobs.

          I'm leaning towards a script like this that would need to be run to set the filter level in a global variable:

          Freeze Window
          If [Not IsEmpty ( PortalRecords::PortalIDField) ]
            Go To Related Record [Show only related records; From table: PortalTable; Using layout: "PortalTable" (PortalTable)]
            Sort [No Dialog ; Restore ]//sort in ascending order by amount
            Go To Record [first]
            Loop
               Exit Loop if [PortalTable:RunningTotal
          > parentTable::GoalAmt ]
               Go To Record/Request/Page [Next ; Exit after Last]
            End Loop
            Set Variable [$$FilterMax ; Value: PortalTable::RunningTotal]
            Set Variable [$$OverGoal ; Value: Get ( FoundCount ) - Get ( RecordNumber ) + (PortalTable::RunningTotal > ParentTable::GoalAmt ) ]
            Go To Layout [original layout]
            Refresh Window [Flush cached Join Results]
          End IF

          If you then use this portal filter expression: PortalTable::RunningTotal < $$FilterMax, you see Toms through Dicks listed, but Bobs will be excluded.

          Define a Count Of Summary field in the portal's table to count the number of records. Make a second portal to this same portalTable with the same filter, but with only one portal row and only this summary field in it. It will report your count of 3 companies.

          $$Overgoal will report your 2 companies over the goal. You can use a number field in the parent table instead of this global variable if you want.