Changing the value of account_id and contact_id in accounts_contacts

[routledge]

Good Afternoon All

I have a strange request to see if anyone knows how to change the value of relationship id's in accounts_contacts.

I have additional fields in both the contacts and accounts tables with the name pwn_id (accounts.pwn_id and contacts.pwn_id). These fields are linked to a view to external database id's. So what i'm trying to do is instead of the values of contacts.id and accounts.id being inserted into contact_id and account_id respectively on the accounts_contacts table, it will be the contacts.pwn_id and accounts.pwn_id values being inserted into contact_id/account_id fields.

I'm using apache 2.2, debian server mysql 5 and sugarcrm 5.2 e CE

Regards

John

[crmbalah]

Hi
Try to import account and contact value separately. And create the relate field from contact to account and store the contact –account relationship based on you old database.

[roblaus]

@routledge: probably it's easier for us to (try to) give you a solution if we knew what you would like to achieve - meaning where and how you want to display the data of the linked database (if this is what you want).
Furthermore it would be necessary to know whether contacts.id and (correctly) contacts_cstm.pwn_id (and the same for accounts) have a 1:1 relation.

[routledge]

Thanks for replying guys

Ok well we are using mysql views which are basically a mirror of the table we want the data from. It works perfectly and is exactly what were looking for. We can update create new records and edit, this will then show in the original table in real time. We would n't have any problem, BUT, the existing data in the external table has an auto-increment integer field that is obviously different to the sugars uuid. I have set the fore mentioned fields as if they were sugars original fields (I know they are not upgrade safe, thats my problem). So we have pwn_id linking to the external id and the sugar id linking to a new field in the external database.

This works great everything updates great and you can create records. The problem is that all three tables (accounts, contacts and accounts_contacts) are views and obviously the accounts_contacts is pulling the sugar uuid and also inserting on new relationship, when its the external data's auto-increment int id that I need to be using to keep everything clean.

If I can pull the external id within the relationship structure then my three months of work is done. The last thing i'm looking to do is start to use logic hooks here instead of views and they are easier and cleaner.

Regards

John

[routledge]

Hello All

Any ideas, maybe I can post more info? please let me know

John

[crmbalah]

HI
write one php script for update relationship data. check the condition based on account and contact name and form the correct relationship. and update account_contact table.

[roblaus]

Sorry, I have been busy lately.

But I am afraid I am only halfway through your thoughts. As far as I understand it now you have an external program which updates Sugar tables (via views) but has its own uid structure. And this conflicts with Sugar because if Sugar is updating something the relationship is made with Sugar's Ids rather than with yours and the external program can't understand the relationship anymore?

If you want PM me a schema of the tables/views and I will have a look. But two things are for sure: I would not change the structure of Sugar's core tables and I would not change the way of how Sugar works because there are too many other things depending on it.

(With my limited understanding) I'd rather go for an intermediate set of tables where both products have their respective structure depicted and subsequently both can be updated (=synchronized).

[christianknoll]

If I understand correclty what you are asing for that if fairly easy:

edit metadata/accounts_contactsMetaData.php

Look at the definition of the relationships and change "... 'lhs_key' => 'id' ... " to "...'lhs_key' => 'pwn_id' ... " and "... 'rhs_key' => 'id' ... " to " .. 'rhs_key' => 'pwn_id' ...

Save the file. Rebuild Relationships in Sugar and with the next object created your respecttive pwn_id fields will be used to write to contact_id and account_id n the relationshiop tale.

christian.

Good Afternoon All

I have a strange request to see if anyone knows how to change the value of relationship id's in accounts_contacts.

I have additional fields in both the contacts and accounts tables with the name pwn_id (accounts.pwn_id and contacts.pwn_id). These fields are linked to a view to external database id's. So what i'm trying to do is instead of the values of contacts.id and accounts.id being inserted into contact_id and account_id respectively on the accounts_contacts table, it will be the contacts.pwn_id and accounts.pwn_id values being inserted into contact_id/account_id fields.

I'm using apache 2.2, debian server mysql 5 and sugarcrm 5.2 e CE

Regards

John