We’ve developed some resources to help you work effectively from home during COVID-19 Click to learn more

get a list of NPS contactperson per company in mirroring db

trying to do: a list of contact persons which has a relation as NPS contactperson

expect to happen: get an overview of all NPS contact persons with emailadress and phone number from SuperOffice mirroring database 

actually happened: The current result shows part of the content, but some companies are missing in the results, while they show a 'relation with NPS' in SuperOffice. 

steps to current situation: The build up of the list starts with contact table. Other tables connected to this are business, category, email, person, phone, phoneformat, relations and relations definition. Current connection with relation is based on registered associate ID from contact to relations. And from relations to relationsdefinition it's reldef ID. Clearly this doesn't work.

question: Can you please explain how to connect relationdefinition properly and get a list of contact persons/companies/phone number/emailadress/ NPS contact person as relation? 

With kind regards, 

Erwin

RE: get a list of NPS contactperson per company in mirroring db

Hi Erwin,

so you have the reldef_id and need to look for those records which has a target_table/source_table = 6 in relations.

Tablenumber 6 (select tablename from crm7.sequence where tablenumber=6) is the person table (even after CDD)

The source and target record is the person_id

https://community.superoffice.com/documentation/SDK/SO.Database/html/Tables-relations.htm

Person have link to contact_id (Company), person_id = record_id from relations.

 

Each persons email address is stored in the email table, person_id = record_id from relations.

 

 

Av: Margrethe Romnes 16. maj 2019

RE: get a list of NPS contactperson per company in mirroring db

thanks! And how to add-in phone number? 

Av: Erwin Herwijnen 21. maj 2019

RE: get a list of NPS contactperson per company in mirroring db

HI Erwin,

Have you seen the database reference that Margrethe linked to for Person and Company? The Phone table is there as well. The owner_id field refers to the person_id or company (contact_id), as expected. Then use the ptype_idx field to determine what phone type.

The database reference guide is your friend, and has built in Search.

Best regards.

Av: Tony Yates 21. maj 2019