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.
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.
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:
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.
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.
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.
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.
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: