6 Replies Latest reply on Aug 13, 2009 11:55 AM by BetoBoton

    Using Count()



      Using Count()



      I would appreciate someone with a bit of patience with this old man, shedding some light to this problem.


      I'm trying to figure this out and probably there is more than 1 solution but it seems I don't find a way out of it.

      I would appreciate if someone can show me how, and where these calculations will occur.

      I believe the field definition will state the field is a calculation type, and will hold the Count function.

      Then I did that and it doesn't work the way I expected...


      Here is what I've done:

      N Times Calculation (Number) Indexed, = Count ( Referee ID ), Evaluate even if all referenced fields are empty 

      Definitions below



      How do I use COUNT() to solve the following simple 1 to Many case.


      My scenario is this one:


      1- Table Contacts has an indexed field Contact ID (Number) and an indexed field Reference ID(Number) 


      2- Table References with an indexed fields Reference ID(Number)  and Referee ID(Number) 


      3- Relationship Contacts::Reference ID is related   to     References::Reference ID


      Each contact (Contacts::Contact ID) was referred by 1 person References::Reference ID 

      Many references can have the same Referee ID.



      Table Contacts has what I want to be a Calculated via script field Contacts::Referred N Times which is how many times this contact has referred other contacts




      Table References has:


      References::N Times which is how many times this same References::Referee ID occur in this References table and also a

      References::Count which is the Xth time this reference is, for this Referee ID.




        • 1. Re: Using Count()

          BetoBoton wrote:
          Each contact (Contacts::Contact ID) was referred by 1 person

          This is rather confusing: If I understand correctly, this 1 person is also a contact? If so, why do you need the References table? I would define a self-join relationship using the Contacts table only:


          Contacts::ReferredBy = Contacts 2::ContactID



          It's also convenient to define another one in the opposite direction:


          Contacts::ContactID = Contacts 3::ReferredBy



          Now you only need to enter the referrer's ContactID into the ReferredBy field of the referred contact's record.




          A calculation =


          Count ( Contacts 3::ReferredBy )


          will return the number of people a contact has referred.


          • 2. Re: Using Count()

            Thank you very much for your suggestion.


            The references Table has a lot of other specific fields, purposes and relationships, like revenue, incentive programs, service orders, commissions paid, current tasks, scheduling etc


            Isn't it possible (or too difficult) for a field (Referred N times) to be the simple result of how many occurrences of a particular value  in a field (Referee ID) on another table (References) happens? 


            Isn't it possible (or too difficult) that everytime a Contact referrers some person (new contact) I can calculate that shows for example this is the 7th time this contact is referring somebody.


            Maybe I'm trying to use Count(), Summary() etc and the answer is simpler than that.


            Thanks again 

            • 3. Re: Using Count()

              If the act of person A referring Person B has attributes of its own, then you do need another table for References. But then the structure should be:

              • ContactID

              • RefererID
              • RefereeID

              with two relationships between these two tables, BOTH using Contacts::ContactID as the matchfield in Contacts.

              Otherwise you'd be entering the same information twice.

              You can use two TO's of either table (a matter of convenience). Let's say it will be References, so that the relationships are:

              References::RefereeID = Contacts::ContactID

              Contacts::ContactID = References 2::RefererID

              Now just add a calculation field in Contacts =

              Count ( References 2::RefererID )

              to return the number of contacts a person has referred.

              • 4. Re: Using Count()

                Thank you for your answer, and I'm sure your solution is clearly a possible answer to my issue.


                I do need however, to clarify the roles/meanings for:


                 • RefererID -> This is the person who is currently a NEW Contact (i.e.. John) referred to this services by someone else


                 • RefereeID -> This is the person(someone else) who sent John to this services.


                In my references table  I have only once John as RefererID , but I can have many John as RefereeID.


                correct ?


                You know, besides I've been 20 years using English language on an everyday basis, this one got me... 

                Sorry for the silly question but I understood the logic behind what you wrote, just need to clarify.


                Thanks a million.


                • 5. Re: Using Count()

                  It actually doesn't matter much, because once you introduce the joining table, the relationship becomes symmetrical.* Anyway, I used the terms this way: if a new contact Betty was referred by an existing contact Adam, then Adam is the referrer, and Betty is the referee.


                  I am not at all sure that is correct English, and I too find these terms rather confusing - perhaps you should find something easier to grasp at a glance - like Sponsor and Candidate, or even a generic Parent and Child. And of course, you can change the names of the TO's to something more meaningful, too.

                  Note that in the actual implementation, you'll want to append another TO of Contacts to at least one of the TO's of References (the one that your References layout will be based upon), so that you can see contact details from both sides.

                  (*) Note also that because the strucure is symmetrical, it inherently supports a many-to-many relationship; if you want to make absolutely sure a referee can have only one referrer, you need to take additional measures.

                  • 6. Re: Using Count()

                    Thank you very much.


                    I'll need some time trying to comprehend and implement what you just showed me.

                    Also I need to learn a lot more about how relationships work.

                    Looking back into a few books here, and the video training I had, I don't believe I fully understood much beyond the basics of relationships: 1::1, 1::M, and M::M 



                    I do appreciate your answer and I can see a few results coming from your answer.

                    In good old Australian English, "Good On'ya Mate, that was bloody good!":smileywink: