Export Individuals with Organization via Relationship

[lcdweb]

I did some searches in the forum, and think I already know the answer to this. But just to confirm --
Am I correct that currently there is no way to export a list of individuals and ALSO include the organization to which they belong via relationships (e.g. the company they are employed by)?
-Brian

[Dave Greenberg]

True.

I think your best bet would be to create a report (CiviReport / BIRT) that gives you the info you want (you can export CSV from the reports too).

[lcdweb]

I took a look at BIRT, but don't think my shared hosting environment will support its requirements.

I went back in and was looking at the "current_employer" field, hoping that I could make that work for my needs. I figured at first it was just a standard field that required manual entry of data. But I couldn't find it in the table structure or ERD, so I started looking for it in the code.

In CRM/Contact/Form/Edit.php, that form field is populated based on whether there's an existing employer/employee relationship defined (if multiple it appears to select the most recently created organization). Interestingly, if you manually type into that current_employer field it will create the employer/employee relationship, and create an org record if the organization doesn't currently exist.

Anyway, what was the intent of creating that field besides being able to basically see the employer relationship on the edit form/summary tab? Has there been any effort to include that field in the export functions? Also seems like you'd want to make that a true table field, and use the existing functions to update the data in the field on form save, rather than simply make it a field that always dynamically shows the relationship defined elsewhere.

-Brian

[lcdweb]

I'm convinced I can hack this to make it work. But need a little help with the API.

I created a field in civicrm_individual for the current_employer data (z_current_employer to distinguish it).

I added the field to CRM/Contact/DAO/Individual.php -- so now it's treated as a field with the individual contact type and will export accordingly.

The file: CRM/Contact/Form/Edit.php contains the two code blocks controlling the current current_employer field -- the first sets the default, the second makes appropriate relationship/organization changes on update. What I *think* I need to do is just add the appropriate code so that whatever the default value is for current_employer is copied to my actual db field (z_current_employer). That way the data is stored in a field, rather than simply dynamically displayed.

I'm not strong enough a developer to easily get my arms around the CiviCRM API. I'm guessing the code for that line would be very simple. Can anyone give me a hand?

-Brian

[Kurund Jalmi]

You might want to check: http://issues.civicrm.org/jira/browse/CRM-1246

kurund

[lcdweb]

Yeah -- I saw that. It's partially what gave me the idea that I could do a workaround.
I'm a php hack, though, and am not sure how to use the API to take the data that prepopulates the current_employer field and update it to the actual db field I created.

[Dave Greenberg]

The "Current Employer" field is basically a shortcut to exposing/updating the latest "Employee of" relationship for an Individual. The data is stored in the civicrm_relationship table. Storing Employee-Employer relationships this way allows for tracking of a person's employment history, notes on the relationship etc.

We haven't addressed export of relationships as of yet because exports of 1:many structures is complicated and possibly not appropriate for CSV formatted outputs. That said, I suspect we'll do something to address the "most-used" relationships like Employer at some point.

In the meantime - some thoughts on your approach for hacking a solution:
- rather than modifying the schema, which potentially could cause upgrade problems etc. - might make more sense to store the Employer Name in a custom field
- I would probably leave the logic in for creating/updating the Employee of relationship - and add logic to ALSO store the Org Name in your custom field (basically a "copy" of the relationship data).  I think the value is present in the postProcess $params array - in which case you could just add a few lines to save that value to your "hard-coded" custom field.

[aspagarino]

I just realized that our company need the "Employer Of" field on the participant event information... I really didn't pay attention to the lack of export until we went life 

How difficult would be to implement the copy approach?

This is quite a limitation in my case and I was wondering if Brian was able to solve it

[lcdweb]

That's an old post.
Current versions (v2.1+) export that field by default.

[aspagarino]

Thanks Brian,

I have 2.1.4... and the csv field after the export for organization name OR current employer is always empty 

I went to CiviEvents and opened a list of participants. I clicked on the fist one to see if the company information is there, and it was under the Relationship tab.

HOWEVER, When I go back to the participant list and export it selecting "Current employer" (under the individual) or "Organization Name" (under the organization), the fields are just empty.

I am using the latest version on Joomla 1.5.10 and Civic 2.1.4

Any ideas

[lcdweb]

In v2.1+, the current employer is set on the main contact summary edit page.
Edit an individual. You'll see a current employer dropdown.

If the relationship to the org does not currently exist, selecting a current employer will create the relationship and set it to the current employer.

You have a situation where you have relationships created, but they have not been recorded as the current employer.

[aspagarino]

You are right Brian... when I edit the participants the "Current Employer" field was empty.

I guess that when I was creating the "contact profile" I used the wrong one? In the field for the Company name I selected "Employee of".

I still don't know how to make the changes so it will update the field "Current employer" instead of "Employee of". On the profile there not an option for "Current employer"

Is there an easy way to fix this... I guess I need to re-import the data to the right field.... but I cannot see the filed for Current employer.

[aspagarino]

Did I missed a step?

When you import the contacts using a csv... isn't it enough to import the field "employee of" and CiviCRM will create a new record if it's not there and it will updated if it's there?

I am still trying to figure out why all the company information for a contact is showing only in the relationship tab instead of the main summary tab.

Thanks again...

[lcdweb]

There's currently no way to set the current employer value on import. I'm 99% sure there's an existing Jira issue for this, as it's definitely a loophole in the existing process.

If you're comfortable with the Civi schema, it's not too hard to fill the data post import. The contact record has a value for the current employer id (corresponding org id). Then you need to fill the employer name (org name) in the indiv contact's org name field (the org name is cached there and that's where the export is pulled from).

[Kiran Jagtap]

hi,

There's currently no way to set the current employer value on import. I'm 99% sure there's an existing Jira issue for this, as it's definitely a loophole in the existing process.

Issue is here CRM-3532
and it's already fixed in CiviCRM v2.2

kiran