contact page displays fewer records than are in the database

[cerhorn]

I'm converting data from Sugar to another CRM package, and noticed that a simple SQL query that joins the contact table to the user table by assigned user id returns more records than the contact page search facility. I am eliminating deleted records in my SQL statement, so that is not the problem.

We're using the Sugar appliance 4.51e, on a VM Ware box.

Any ideas?

[andopes]

Hi cerhorn

By default SugarCRM use LEFT JOIN to get the assigned user name, so it display contacts even if they have no assigned_user. If you are using a INNER JOIN then you will get only the contacts containing assigned user name.
Let me know the query you are using.

Best regards

[cerhorn]

The problem is that I only want records assigned to a particular user, so an inner join is fine. However when I use the advanced search on the contacts page to search by user, I get less records than the inner join query.

Doing some more research, I exported the entire contact table to Excel using the export function in Sugar. Then I counted how many rows for one user and compared to the result of the advanced search by user. The advanced search missed records that are assigned to this user. The differences seem to be contacts with no parent account.

[andopes]

Hi cerhorn

Edit the file include/ListView/ListViewData.php and add this code

 echo "<LI>$main_query"; 
  

Just bellow the line:

 $main_query = $ret_array['select'] . $params['custom_select'] . $ret_array['from'] . $params['custom_from'] . $ret_array['where'] . $params['custom_where'] . $ret_array['order_by'] . $params['custom_order_by']; 
  

Analyze the returned query. You will identify the reason for fewer records.

Cheers