Accounts related to itself?

[jcorry]

Apache/2.2.4 (Unix) PHP/5.2.8 ON CentOS (current...not sure which version)
Sugar 5.2e

I have some 23000 Accounts.

Some Accounts are agencies. Other Accounts can be related to one or more agency type account.

How can I create a relationship between Accounts and Accounts of type agency?

For each Account that is type = agency, I have a way to identify the account.

For each Account that is related to one or more agency Account, I have the ID numbers of the Accounts they're related to.

Likewise, I'll need a relationship between Accounts of type agency and the Accounts that belong to them.

I'm trying to avoid creating a custom module 'Agencies' by just using the Account module for both purposes.

Specifically, I need a way to set the relationships after or during importing the data from CSV files.

An Account may belong to one or more Account of type agency.
An agency Account may have one or more Accounts.

???

[josh.sweeney]

There are two easy ways to relate accounts to themselves.

1. Make a many to many relationship between accounts and accounts in studio.
2. Use the member organization subpanel which relates accounts

For both solutions you can just add a new account type field or use the existing one and choose whether it is an agency or any other option that you add.

[jcorry]

OK, got that.

Now...other than going through all 23000 accounts records and choosing which account has which agency and vice versa...how can I populate the relationship table with the links between Accounts and Accounts?

My data is coming from CSV file import.

Some of the records I import will have a value in the field 'agency id'.

I think I get the relationship...it's creating all of the links in that relationship either during or subsequent to the data import process.

I guess I could write a PHP script that uses Soap to set the relationships or something like that....or I could directly manipulate the tables in the database...

For the folks that have done this before, what are the easier ways and what are the pitfalls?

[josh.sweeney]

Automatically relating will be a little harder. Since it is many to many you can use import and have it auto relate. Also, if I remember correctly the current SOAP functionality does not work on custom many to many relationships. You best bet is probably to create a script that hooks directly to the DB.

You may want to look at using Talend for this.

Maybe someone else has some ideas.

[jcorry]

Automatically relating will be a little harder. Since it is many to many you can use import and have it auto relate.

What would that look like?

Accounts would have a field 'Agency' that would, on import, map to one of the columns in my import spreadsheet (Agency ID)?

I'm thinking that:
- importing the data
- creating the relationship
- linking 2 of the records in Sugar's accounts interface
- using a PHP script to read through my CSV files, and for that 'agency ID' field insert a row in the relationship table.

That's probably the easiest way to do it.

I'm open to ideas though.

[josh.sweeney]

Sorry but I meant can't. You cannot relate many to many with import.

[Greetal]

Automatically relating will be a little harder. Since it is many to many you can use import and have it auto relate. Also, if I remember correctly the current SOAP functionality does not work on custom many to many relationships. You best bet is probably to create a script that hooks directly to the DB.

You may want to look at using Talend for this.

Maybe someone else has some ideas.

Talend Open Studio is the right ETL tool to perform that type of operation. The good part with Talend is that it is free to download off the website. Check: http://www.talend.com/