CiviCRM Community Forums (archive)

Hi all,
I hope this is the right board.

I'm trying to create a custom report. The issue is that I'm also trying to do so using custom data entered ala the custom data field.

For example:

I want to create a data set that contains an organizations investmentors. That is, for  a give organizational contact(part of CiviCRMContact) I want to create a custom group that contains a list of their companies that have invested in that compay. I will call that group 'Investment Information'


Set: 'Investment Information'
Fields: Investor, Amount, Date Invested

Multiple Records: Yes
Display type: Tab with Table.

Lets call a sample organization 'The green cross'
The green cross may have the following investment information.

Investor, Amount, Date
Abc, 20000, 1998-2-1
CPK, 23000, 1992-1-1
DLK, 120000, 2010-1-4


How can I access these data in a custom report?
I am starting to create a DAO as /CRM/Custom/DAO/investmentInformation.php but am not sure if this will work.
I also have no idea how to tie the information in the DAO to the actual database
I am assuming that I create the &fields() function but am unsure how to point the where variable to the proper location in the CiviCRM database(variable is multiple record)



note that id is the investor contact id
 static function &fields()
  {
    if (!(self::$_fields)) {
      self::$_fields = array(
        'id' => array(
          'name' => 'id',
          'type' => CRM_Utils_Type::T_INT,
          'title' => ts('Internal Contact ID') ,
          'required' => true,
          'import' => true,
          'where' => 'civicrm_contact.id',
          'headerPattern' => '/internal|contact?|id$/i',
          'dataPattern' => '',
          'export' => true,
        ) ,
        'amount' => array(                                                         //where is this in the database
          'name' => 'amount',
          'type' => CRM_Utils_Type::T_MONEY,
          'title' => ts('Amount') ,
          'precision' => array(
            20,
            2
        ) ,
        'invest_date' => array(
          'name' => 'invest_date',
          'type' => CRM_Utils_Type::T_DATE,
          'title' => ts('Investment Date') ,
          'import' => true,
          'where' => 'WHERE IS THIS',
          'headerPattern' => '/^birth|(b(irth\s)?date)|D(\W*)O(\W*)B(\W*)$/i',                    //ignore pls
          'dataPattern' => '/\d{4}-?\d{2}-?\d{2}/',                                                                 //ignore
          'export' => true,
          'html' => array(
            'type' => 'Select Date',
          ) ,
        ) ,
       


Should I be concerned about using multiple records fields?
In the tooltip it mentioned that multiple record fields dont work well with reports.

So in summary, should I even structure my data this way, AND, if I do structure it this way how do I access the data for the purposes of a report.


Thanks

Hi Joanne,
I thought I had deleted my own duplicate after I decided this was the correct board. I probably forgot to hit the confirm button or something like that. Sorry about that.

Those boards you linked are definitely useful but are very general. I guess I'm interested in seeing if anyone has developed their own custom report based on custom data. If not I'll go ahead and try it then post my own results.