Microsoft Dynamics CRM (Archived)

relationship between annotation table and contact table

(0) Share

Report

Posted on by Community Member Microsoft Employee

Hello

I have a situation where I want all the documents related to a contact to be exported to another system.

So far I only have the following information:

1. the annotations entity holds the actual document and each upload has 2 records

How are these records related to Contact entity? I have tried all the GUID and none of them match, only the createdby field appears in both tables but that does not give me unique information for multiple forms submitted by the same contact.

Example:

Contact Tiger submits a form with attachments rat and cat.

Contact Lion submits another form with attachments bat and dog

I have 8 entries in the annotation entity and one for each upload.

1. How do I map them back to lion and Tiger?

2. If Tiger submits another form with an upload how do I differentiate the 2 submissions?

I hope I have explained the situation clearly.

Thank you in advance :)

*This post is locked for comments

All responses (8)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

RE: relationship between annotation table and contact table

thank you Pawel

I did not mention Click Dimension as I did not know that the field mapping would differ.

I will start another thread for this, thank you for your help :)
tw0sh3ds 5,600 on at

Like (0)

Report

RE: relationship between annotation table and contact table

Why did you not mention about Click Dimensions in your first post? This changes this question completely, everybody were just answering about standard OOTB files in CRM. In that case I don't know what is the object structure in Click Dimensions (maybe they are not using OOB annotations?) but for sure you can check it out by analysing their solution in your environment.

Add proper tags and some information that this is about Click Dimensions in question topic and description, maybe somebody which uses this tools will help you.
Community Member Microsoft Employee on at

Like (0)

Report

RE: relationship between annotation table and contact table

Thank you Pawel

I checked this validation multiple times but it doesn't work this way.

I am uploading the files from a form built in Click dimensions, I can see that there is a unique field_ID for every upload. Maybe that is my relationship to the contact, in which entity can I find this field_ID?
Aric Levin 30,188 on at

Like (1)

Report

RE: relationship between annotation table and contact table

See below. Maybe this will help you with SCRIBE:

Fields:

Relationship:
Suggested answer

tw0sh3ds 5,600 on at

Like (1)

Report

RE: relationship between annotation table and contact table

There is no mapping table - objectid in your annotation record is contactid in contact record (). This is as ismple as that. If you cannot find a match, probably you have bugs in your SQL statement but for sure the IDs must match between the tables (of course annotation table will contain records related to other objects, but if you inner join two tables on contactid and objectid they will be filtered out)
Community Member Microsoft Employee on at

Like (0)

Report

RE: relationship between annotation table and contact table

Thank you Aric

I cannot find the mapping for the objectID in annotation entity to any field in contact entity. So i guess there are other tables that contain the mapping from the annotation entity to contact entity.

I am using scribe to retrieve the data from CRM into a SQL server.

if I could get the mapping entity relation between these two tables would be great.

Thanks again!
Nisha
Suggested answer

Nijos 1,672 on at

Like (1)

Report

RE: relationship between annotation table and contact table

Hi,

Please check the "regarding" field in annotation.
Aric Levin 30,188 on at

Like (2)

Report

RE: relationship between annotation table and contact table

Hi,

The Note (annotation) entity includes two fields which you are linked to the Contact entity.

The objectid field (or Regarding field - Display Name) in the annotation entity is the Guid of the contact record that the annotations are stored within.

In order to get the annotations for a particular record (for example Lion), you would have to pass the id of the Lion record to a query (RetrieveMultiple) that will retrieve the notes for that query.

You are probably note using the subject field, which is what you can use to differentiate between the different uploads, or possibly just by file name. Basically if Tiger submits their second form, you put something in the subject field that specifies info about the submission.

If you require additional information please specify in further details.

Hope this helps.