CiviCRM Community Forums (archive)

I am migrating some common custom reports I have created to Custom Searches.

I have created the custom search template that displays a text box allowing a contact display name to be entered as criteria for the search. Although I have currently some issues with the SQL query (just a bit of debugging), this works as it should.
However, in order to ensure that the records returned are only for one contact (the query takes the text and adds a wildcard) I would like to replace the text box with a lookup box similar to the 'Search Box' on the menu.
Ideally, I could then limit the list to return contacts of a certain type and/or contacts who have a membership record.

I have followed some instructions in the Documentation and cannot get it to work. Can anyone provide some pointers/code? I would be happy to post all the code back into the Documentation on completion.

Thanks in advance

I looked at http://wiki.civicrm.org/confluence/display/CRMDOC40/autocomplete+field and ended up with another bald patch.

A bit more on what I am doing:
1. We offer events known to us as 'Clinics'. Each of these Clinics have a mailing group to which invitations are sent. These Clinics are open to employees of member organisations.
2. For client engagement purposes or for review, we (or the member organisation) need to know the Clinics that employees are being invited to.

I have completed the custom search - it works well (although I have some work to do on the template)

Custom Search Script
<?php

require_once 'CRM/Contact/Form/Search/Interface.php';

class CRM_Contact_Form_Search_Custom_ClinicManagementSearch
implements CRM_Contact_Form_Search_Interface {

    protected $_formValues;

    function __construct( &$formValues ) {     
        $this->_formValues = $formValues;
$this->_columns = array(
ts('Contact Name') => 'sort_name',
ts('Job Title') => 'job_title',
ts('Email Address') => 'email',
ts('Phone') => 'phone',
ts('Group Name') => 'title',
);
    }

    function buildForm( &$form ) {
        $this->setTitle('Clinic Management Search');
        $form->add('text', 'organization', ts('Organisation Name'));
        $form->assign( 'elements', array('organization') );
    }

    function templateFile( ) {
        return 'CRM/Contact/Form/Search/Custom/Sample.tpl';
    }

function all( $offset = 0, $rowcount = 0, $sort = null, $includeContactIDs = false, $onlyIDs = false) {
$select = 
'contact_a.id as contact_id,
 contact_a.sort_name,
 contact_a.job_title,
 civicrm_email.email,
 civicrm_phone.phone,
 civicrm_group.id,
 civicrm_group.title,
 organization_contact.display_name
';
$from  = $this->from( );
        $where = $this->where( $includeContactIDs );

if ( ! empty( $where ) ) {
$where = "WHERE $where";
}
            
$sql = " SELECT $select FROM $from $where ";
        $sql .= 'ORDER BY contact_a.sort_name';

        if ( $rowcount > 0 && $offset >= 0 ) {
            $sql .= " LIMIT $offset, $rowcount ";
        }        

//CRM_Core_Error::debug('sql',$sql);
//exit();                
                
        return $sql;
    }

    function from( ) {
$sql = "
civicrm_contact contact_a
JOIN civicrm_relationship
ON contact_a.id = civicrm_relationship.contact_id_a
AND civicrm_relationship.relationship_type_id = 4
AND is_active = 1
AND (end_date >= NOW() OR end_date IS NULL)
JOIN civicrm_contact organization_contact
ON civicrm_relationship.contact_id_b = organization_contact.id
LEFT JOIN civicrm_email
ON contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1
LEFT JOIN civicrm_phone
ON contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1
LEFT JOIN civicrm_group_contact
ON contact_a.id = civicrm_group_contact.contact_id AND civicrm_group_contact.status = 'Added'
LEFT JOIN civicrm_group
ON civicrm_group.id = civicrm_group_contact.group_id
";
return $sql;
    }

    function where( $includeContactIDs = false ) {
        $organization_name = $this->_formValues['organization'];
return "contact_a.contact_type = 'Individual' AND organization_contact.display_name LIKE '%{$organization_name}%' 
AND civicrm_group.title LIKE 'Clinic%'";
    }

    function count( ) {
        $sql = $this->all( );
           
        $dao = CRM_Core_DAO::executeQuery( $sql,
                                           CRM_Core_DAO::$_nullArray );
        return $dao->N;
    }
       
    function contactIDs( $offset = 0, $rowcount = 0, $sort = null ) {
        return $this->all( $offset, $rowcount, $sort,  false, true );
    }
       
    function &columns( ) {
        return $this->_columns;
    }

    function setTitle( $title ) {
        if ( $title ) {
            CRM_Utils_System::setTitle( $title );
        } else {
            CRM_Utils_System::setTitle(ts('Search'));
        }
    }

    function summary( ) {
        return null;
    }
         
}
Custom Search Template. Note I have removed the actions menu and the checkboxes due to the resulting list containing 'duplicate' contactids.

<div class="crm-block crm-form-block crm-contact-custom-search-form-block">
<div class="crm-accordion-wrapper crm-custom_search_form-accordion {if $rows}crm-accordion-closed{else}crm-accordion-open{/if}">
    <div class="crm-accordion-header crm-master-accordion-header">
      <div class="icon crm-accordion-pointer"></div>
      {ts}Edit Organisation{/ts}
    </div><!-- /.crm-accordion-header -->
    <div class="crm-accordion-body">
        <table class="form-layout-compressed">
            {* Loop through all defined search criteria fields (defined in the buildForm() function). *}
            {foreach from=$elements item=element}
                <tr class="crm-contact-custom-search-form-row-{$element}">
                    <td class="label">{$form.$element.label}</td>
                    {if $element eq 'start_date'}
                        <td>{include file="CRM/common/jcalendar.tpl" elementName=start_date}</td>
                    {elseif $element eq 'end_date'}
                        <td>{include file="CRM/common/jcalendar.tpl" elementName=end_date}</td>
                    {else}
                        <td>{$form.$element.html}</td>
                    {/if}
                </tr>
            {/foreach}
        </table>
        <div class="crm-submit-buttons">{include file="CRM/common/formButtons.tpl" location="bottom"}</div>
    </div><!-- /.crm-accordion-body -->
</div><!-- /.crm-accordion-wrapper -->
</div><!-- /.crm-form-block -->

{if $rowsEmpty || $rows}
<div class="crm-content-block">

{if $rowsEmpty}
    {include file="CRM/Contact/Form/Search/Custom/EmptyResults.tpl"}
{/if}

{if $rows}
<div class="crm-results-block">

        {* This section displays the rows along and includes the paging controls *}
    <div class="crm-search-results">

        {include file="CRM/common/pager.tpl" location="top"}

        {* Include alpha pager if defined. *}
        {if $atoZ}
            {include file="CRM/common/pagerAToZ.tpl"}
        {/if}
       
        {strip}
        <table class="selector" >
            <thead class="sticky">
                <tr>
                {foreach from=$columnHeaders item=header}
                    <th scope="col">
                        {if $header.sort}
                            {assign var='key' value=$header.sort}
                            {$sort->_response.$key.link}
                        {else}
                            {$header.name}
                        {/if}
                    </th>
                {/foreach}
                </tr>
            </thead>

            {counter start=0 skip=1 print=false}
            {foreach from=$rows item=row}
                <tr id='rowid{$row.contact_id}' class="{cycle values="odd-row,even-row"}">
                    {foreach from=$columnHeaders item=header}
                        {assign var=fName value=$header.sort}
                        {if $fName eq 'sort_name'}
                            <td><a href="{crmURL p='civicrm/contact/view' q="reset=1&cid=`$row.contact_id`"}">{$row.sort_name}</a></td>
                        {else}
                            <td>{$row.$fName}</td>
                        {/if}
                    {/foreach}
                </tr>
            {/foreach}
        </table>
        {/strip}

        <script type="text/javascript">
        {* this function is called to change the color of selected row(s) *}
        var fname = "{$form.formName}";
        on_load_init_checkboxes(fname);
        </script>

        {include file="CRM/common/pager.tpl" location="bottom"}

        </p>
    {* END Actions/Results section *}
    </div>
    </div>
{/if}



</div>
{/if}
{literal}
<script type="text/javascript">
cj(function() {
   cj().crmaccordions();
});
</script>
{/literal}
As you see in the code, the value entered is passed as a string and entered into the sql with wildcards. In some searches this would list more than one organisation. This is obviously not the desired result due to results missing the organisation column.
A lookup that accepts entering the organisation display name returning the contactid is the exact result required. A 'combo' box or 'list' box would be counter-intuitive.

Great, great thing you did here.

I was able to use your code and the pages Donald suggested to get ajax working on some custom fields in some internal civicrm forms.

Thank you both!