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

    Join Relationships


      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

          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.