How to relate a detail to its record

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

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

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,

Jonathan