4 Replies Latest reply on Oct 1, 2009 10:55 AM by skn

    URGENT HELP req for a count

    beckett85_1

      Title

      URGENT HELP req for a count

      Post

      i need a way to count the number of related records, but set to a certain value

      my situation is:

       

      i have projects, each has related jobs, i need to count the number of jobs complete, and jobs pending payment

       

      thus i need something like

       

      count(jobs::status="complete") and count(jobs::status="pending payment")

       

      is there any way that you can count through this requirement or do i need to create multiple portals?

       

      thanks heaps, any ideas would help as this is mega urgent

        • 1. Re: URGENT HELP req for a count
          Orlando
            

          Hi beckett85

           

          What you can do is create a flag for each status in your related table and then create a couple of summary fields to count the number of records flagged.

           

          For the flag fields create a new Calculated number field as below, these need to be created in the table of the related records, not the main table you are viewing the portal from.

           

          AlertStatus_Complete
           
          If ( status = "complete" ; 1 )

           

          and

           

          AlertStatus_Pending
           
          If ( status = "pending payment" ; 1 )

           

          Now you need to create the summary fields to total these up

           

          SumStatus_Complete

           

          Make the Type Summary and click 'Create'

           

          the Options for Summary Field dialog will popup and you need to make sure 'Total of' is selected on the left and 'AlertStatus_Complete' is highlighted in the list of Available Fields and click 'OK'

           

          Now do the same for Pending and these will be all setup.

           

          Now on your layout with the portal to this Jobs table you can place these two fields and you will see the total for both.

           

          I hope this helps and let me know if anything is unclear. 

           

           

           

           

          • 2. Re: URGENT HELP req for a count
            beckett85_1
               Thanks heaps mate, that solved so many problems, perfect solution
            • 3. Re: URGENT HELP req for a count
              comment_1
                

              beckett85 wrote:
              i need something like

              count(jobs::status="complete") and count(jobs::status="pending payment")


              Try a calculation field (result is Number) in the Projects table =

               

              ValueCount ( FilterValues ( List ( Jobs::Status ) ; "complete¶pending payment" ) )

               

               

              This is of course assuming that "complete" and "pending payment" are mutually exclusive.

              • 4. Re: URGENT HELP req for a count
                skn
                  

                Hi,

                 

                I have a table that is sorted by  a "Case ID" field, I want to create a field that uses the count function to do a running count for the Case ID's and start the count over when a new set of Case ID's are in the field?  I know I can create a summary field to do this, but the user would need to modify this field later and from what I've read a summary field can't be modified.