Creating reports with custom fields relating accounts and contacts - how does it work?

Question

I am looking for a CRM to install that can do the following:

• Create custom field in account where I can select/link one or more contacts that are the founder of the account (firm)

• Create a custom field in the account with the founding date of the firm

• Create a school field in the contacts

And here comes the trick:

I would like to create reports that show for example: all account/firm names that are founded within a specific date range with contacts linked in the custom founder field that have visited a specific school.

So in summary what I would like to accomplish is to generate a list of accounts that have been founded within a specific date range by founders from a specific school. I think I need custom fields since one founder can be the founder/employee of more than one account.

Does this work with CiviCRM and if so, how do I do it?

Thank you so much for your help!

Dennis

Answer 1

Welcome!

While a lot more information should be collected about your intended processes to set things up in the best way... my short answer would be "Yes, you will be able to accomplish this with civiCRM - but it's possible your solution may require some custom code for the report to look the way you want it to unless you're planning to use Drupal."

Without more information this is what I would start thinking about - it's possible that with more information, however, this is not the optimal solution :) ... only my fourth item mentions any custom code per-say:

• I would use relationships from Organization <-> Individual to define the Firm <-> Founder relationships. Both firms and founders can hold multiple relationships of the same type.

• Create a custom date field for Organization contact types "Date Founded" (or like)

• School needs more information and might be trickier but doable, sounds like it should be either a custom field for individuals (founders) or a custom activity type (visit school), based on your two sentences about it. Can one founder ever have multiple schools listed? Will you collect more information about schools (contact reference field) or just the name (plain text, maybe even a school-named group or tag depending on functions you need)? etc.

• I think your required 'report' as civicrm uses the term would require some custom code (many answers on this SE about how to go about that). You could probably generate this list using certain search techniques 'out of the box' but it would be slightly complex and depend on the level of comfort of your users. Using Drupal Views to generate this list may be an even easier way to go, if Drupal is a viable candidate for you.

Hope this is helpful!