How to relate a detail to its record

Hi community,


Let's take an example :


I created multiple records in a section. In this section I have a detail that contains some fields like the "name", the "date", the "quantity". I want to return, through some code in a webservice, all the names of the detail that belongs to a certain record (the record passed as a parameter).


How can I do this link between the detail and its record ?


If we take the standard example of the "contact" section, there is something strange. In the "Noteworthy events", when we create a birthday for instance, we directly can access to the "BirthDate" field from the "Contact" object. But when I create a custom section I can't access like this to the fields of the detail of a specific record.


Do you have any solutions for this ?


Thanks a lot.


Best regards,


Like 0



Hi Jonathan,


When you create a custom detail for a custom section there is always some column that is used for connection. Let's take your example with noteworthy events of contacts. The table that represents noteworthy events of contacts in the database is called ContactAnniversary and if you perform a SELECT query to it:

SELECT TOP 1 * FROM ContactAnniversary --MS SQL
SELECT * FROM "ContactAnniversary" LIMIT 1 --PostgreSQL

You will see that the column that is used for connecting the "Contact" table and  the "ContactAnniversary" table is "ContactId":

So you need to use this column as a reference.


As for the custom section - when you add some detail to the custom section you also specify some column to connect detail and the section. This is also described here (see "Add an existing detail to a record page" paragraph and step 6.(c-d)). And the "Where detail column" field contains the column that connects your detail with your section and it should be used as a connection in your webservice.


Best regards,


Oscar Dylan,


Hey, thanks a lot for your useful answer. By the way, how can you query this foreign key column ? 


For example :


var esq = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "ContactAnniversary");


var contactColForeignId = esq.AddColumn("ContactId");


Doesn't work because, I think, that is doesn't recognize it as a real "column" in the "ContactAnniversary" object. How can I fetch this foreign ID in a C# code and then saying that the Foreign Id of the "ContactAnniversary" object must correspond to the Id of the "Contact" ?


Thanks a lot for your help Oscar ! :)

Jonathan Quendoz,


You shouldn't use an Id suffix for the column name. Esq uses an actual column name from the object (which is "Contact"). So you need to use:


var contactColForeignId = esq.AddColumn("Contact");

And then it will find the column correctly.


Best regards,


Oscar Dylan,


Thanks for your answer, now I encounter another problem :


Let me take another example :


var result = " ";

var esqAnniversary = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "ContactAnniversary");

var esqContact = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "Contact");


var esqColId = esqAnniversary .AddColumn("Id");

var esqColForeignId = esqAnniversary .AddColumn("Contact.Id");



var esqContactColId = esqContact.AddColumn("Id");

var esqContactColName = esqContact.AddColumn("Name");


Then I initialize an entity collection :


var entites = esq.GetEntityCollection(UserConnection);


How can I query, through a filter, both the esq ?


Like :


First I filter through the Contact I want :


var esqFilterContact = esqContact.CreateFilterWithParameters(FilterComparisonType.Equal, esqContactColName.Name, parameter);


var esqFilterId = esqAnniversary.CreateFilterWithParameters(FilterComparisonType.Equal, esqColForeignId.Name, esqContactColId.Name);


So I take only the detail corresponding to the name of the contact in parameter ?

How can I do a query like this ? My example doesn't work, can you help me troubleshooting this ?


Hopefully you can understand my problem and help me through this !






Best regards,


Show all comments