counting number of referrals

[thebrianexp]

i have a contact database, it has leads, clients and referrals.

when you are in the referrals section, i would like a count of all clients and leads that have the referrals name in their referral section

say im in 'tommy' and he is a referrer. it will show 24 people he referred based on everyone who has 'tommy' in their referral field.

it would be awesome if i could do it by a month to month basis, based on their creation date.

and have that data go into a portal..

yea?

[Søren Dyhr]

And the relational structure is recursive or what??

--sd

[thebrianexp]

what do you mean

[Søren Dyhr]

Describe your relational structure? I'm not pretty sure clients and referrals should go to separate tables, or that I'm not getting what referrals in the first place - aren't they some sort of persons too?

You need a join table called consultations/meetings or such, from where the statistics should take place!

--sd

[thebrianexp]

well i attached the file.

theBE.zip

[Søren Dyhr]

Im not sure I know the beat of the drum you follow relationally - explain design objectives and mission, including description of the legacy base.

--sd

[FestiveEmbalmer]

Your business rules and structure affect the answer to your question.

For example Clients and referrals are both types of persons.

I don't know what a 'lead' is, but it sounds like it would be a potential client- therefore a person record.

If you just have a "Persons" table, you are going to have a little recursion, because you will be relating the person table to itself.

All of that aside, the way I would do it (which may not be correct) is to at the very least put both leads and clients in the same table with a field that designates which they are. Relate this table to the Referrer table (which can just be a table occurence of the original table). You will not want to use the Name as the key to this relationship, but a serial number. You can set the entry field to use a value list that shows only the names from the desired table, but actually enters the serial key.

Than just create a calc field : Count (keyfield). Make sure the key is from the client TO and the calc is evaluating from the referrer TO.

see attached

matt

reeferer_Madness.zip

[FestiveEmbalmer]

You can add the function of doing it by month, but you will have to use a multicriteria relationship between the tables.

good luck,

matt