3 Replies Latest reply on Sep 7, 2012 2:14 PM by philmodjunk

    Help with counting portal records

    AaronRoss

      Title

      Help with counting portal records

      Post

      Hello

       

      I have a layout with a portal in it. The layout is called: Payroll and i set up a relationship with Payroll and another layout called: SALES. When you enter the Sales Rep name it gives you all the sales made within a given date range. The problem is the Sales Rep can have over 60 + records (SALES) in the given date range and i need a calulation field to cound the number of records that populates when give the start & end date ranges.

      My question is, is there a calulation or a way to have a field give a count of records that populate in a portal? I tired count (Table: SALES) but it gives me a total count of all his/her records not within the start & end date range.

        • 1. Re: Help with counting portal records
          philmodjunk

          Are start and end range used as part of the portal's relationship or part of a filter expression? I would guess that they are used in a filter expression or count would work for you.

          For summary data from filtered records do this:

          Define summary fields in the portal's table.

          For the count, define a "count of" summary field that counts a field that is never empty such as the foreign key field used in this portal's relationship.

          You could also use summary fields that return the minimum and maximum dates, to identify the start and end dates respectively, but since you already have to specify these dates for your portal filter, this seems unecessary. (But there's a subtle difference between them and these summmary fields, you might specify a date range for January 1st to 31st and the summary fields might return dates of January 2nd to 28th because there are no related records with a January 1st date nor related records with a January date greater than the 28th--thus the dates the user specifies give the filter range and the summary fields report the actual range of dates displayed in the portal.)

          Then add a one row portal based on the same relationship with the same portal filter to your layout and put your summary field(s) into this one row portal.

          • 2. Re: Help with counting portal records
            AaronRoss

            One problem.

            The field has to be part of the PAYROLL layout not part of the portal fields.

            Example: I enter in JOHN DOE in a field called PAYROLL::Sales Rep Name and give a start date of 8/31/2012 and end date of 9/7/2012. Since there is a relationship with PAYROLL::Sales Rep Name and SALES:: Sales Rep Name now it populates 30 deals within that range.

            The field called PAYROLL::Total Deals should somehow calulation the 30 deals in the portal for John Doe

            • 3. Re: Help with counting portal records
              philmodjunk

              If you add a new one row portal as I have specified, you can make the portal borders invisible and this will look like any other field on your layout outside of your existing portal. Just make sure that this new portal has the same filter expression as the first.