1 Reply Latest reply on Sep 12, 2013 8:09 AM by Mike_Mitchell

    Join Relationships

    nmorales

      Hey all

       

       

      I have a joined relationship by ClientID. I was wondering if anyone knew how I could count the Client ID once if it is a duplicate. My goal is count the Clients with a certain stage once instead of counting all there activities with that certain stage.

       

       

      Anyone know the trick? The reason why Im using joins is because I thought it would make the report quicker. Please advise, would love feedback

        • 1. Re: Join Relationships
          Mike_Mitchell

          Hello, Nicole.

           

          One of the easiest ways is to build a value list based on the contents of the field, then use something like this calculation:

           

               ValueCount ( ValueListItems ( Get ( FileName ) ; "ValueListName" ))

           

          You could also use an ExecuteSQL statement to pull it, such as:

           

               ValueCount ( ExecuteSQL ( "SELECT DISTINCT tableName.ClientID FROM tableName" ; "" ; "" ))

           

          although I've heard that the Value List approach is faster (never tested it myself).

           

          If you only want certain items (such as items related to a parent record), you can either build your value list based on that relationship, or you can put a WHERE clause in your SQL query.

           

          HTH

           

          Mike