CiviCRM Community Forums (archive)

This is an Access query/schema that relates the Civi Contact and Individual records through recording a phone call.  My thinking is that this query will be important in building an alternate frontend for inserting the right records into the civicrm_phonecall table.

Here is the SQL:
SELECT civicrm_email.email, civicrm_individual.first_name, civicrm_individual.middle_name, civicrm_individual.last_name, civicrm_phonecall.target_entity_table, civicrm_contact_1.display_name AS ContactBy
FROM (((civicrm_contact INNER JOIN civicrm_individual ON civicrm_contact.id = civicrm_individual.contact_id) INNER JOIN (civicrm_location INNER JOIN civicrm_email ON civicrm_location.id = civicrm_email.location_id) ON civicrm_individual.contact_id = civicrm_location.entity_id) INNER JOIN civicrm_phonecall ON civicrm_contact.id = civicrm_phonecall.target_entity_id) INNER JOIN civicrm_contact AS civicrm_contact_1 ON civicrm_phonecall.source_contact_id = civicrm_contact_1.id
WHERE (((civicrm_phonecall.target_entity_table)="civicrm_contact"));

Hopefully, this helps somebody move this integration effort forward.

Best,
Ken

THANKS! Dave.

Yeah, my activists are Civi Contacts, after jumping through some hoops, they are also Joomla users, and the two are associated in _uf_match.

Thanks for the tips.   I am actually using a brute force approach of addressing the tables directly using code generated by PHPRunner.  I am just about there...The generated PHP code includes a login page that checks the Joomla database for a u/p match (not checking disabled/blocked or user groups at present), then provides access to lists and forms for each table.  I need to figure out how to lookup and store the CiviID from uf_match based on the JoomlaID.  Shouldn't be hard, but I am a complete noob at PHP, hence my use of the code generator.

I will take a crack at using the 2.0 tables.  Hopefully, there's a pretty schema diagram available?

Thanks,
Ken

OK, I have packaged the code I generated.  I just checked that the login works, and allows me to connect to the Activities table of Civi 1.9 under Joomla 1.0.13, IOW, the login function checks against the Joomla database, and upon authorization, allows the user access to the menu, which is a list and form which allow adds to the Activities table.

You should be able to just unzip the application somewhere under your web root on any shared hosting environment that has PHP and MySQL.  Change the parameters in include/dbcommon.php as required for your setup.

This simple application was developed in http://www.xlinesoft.com/phprunner/?1102, which I recommend highly as a very productive PHP Generator with a good developer community.

I understand that this is outdated now that 2.0 is in beta, but am posting this in the hopes that maybe someone can use it as is, and that others more capable can extend it.

Download at http://www.stoprealid.info/downloads/civi1a-for-dist.zip

And be a good American patriot and oppose RealID! 

Best,
Ken