Dynamic CRM - SQL query to get entity that lookup relates to
Within the CRM database, where can I find the name of the entity that a lookup field relates to?
For example, I have an Account entity which contains two lookup fields;
Account Contact, which points to theContactentity
Leased Equipment, which points to theEquipmententity
How can I get the name of the entity that each of the fields relates to?
I expected to see this in the MetadataSchema.AttributeLookupValue table, but this doesn't seem to be the right place.
Ideally, I'd like to write a query for this information. Something along the lines of:
SELECT
AttributeName,
AttributeTypeName,
ReferencedEntityName
FROM
unknown.TableName
WHERE
AttributeName IN ('new_AccountContact', 'new_LeasedEquipment')
-- Results:
-- AttributeName | AttributeTypeName | ReferencedEntityName
-- new_AccountContact | lookup | Contact
-- new_LeasedEquipment | lookup | Equipment
Any help trying to achieve this would be appreciated, thanks.
sql-server dynamics-crm dynamics-crm-2011 microsoft-dynamics asked Nov 12 '18 at 16:51
Klicker
189111
add a comment |
Within the CRM database, where can I find the name of the entity that a lookup field relates to?
For example, I have an Account entity which contains two lookup fields;
Account Contact, which points to theContactentity
Leased Equipment, which points to theEquipmententity
How can I get the name of the entity that each of the fields relates to?
I expected to see this in the MetadataSchema.AttributeLookupValue table, but this doesn't seem to be the right place.
Ideally, I'd like to write a query for this information. Something along the lines of:
SELECT
AttributeName,
AttributeTypeName,
ReferencedEntityName
FROM
unknown.TableName
WHERE
AttributeName IN ('new_AccountContact', 'new_LeasedEquipment')
-- Results:
-- AttributeName | AttributeTypeName | ReferencedEntityName
-- new_AccountContact | lookup | Contact
-- new_LeasedEquipment | lookup | Equipment
Any help trying to achieve this would be appreciated, thanks.
sql-server dynamics-crm dynamics-crm-2011 microsoft-dynamics asked Nov 12 '18 at 16:51
Klicker
189111
add a comment |
Within the CRM database, where can I find the name of the entity that a lookup field relates to?
For example, I have an Account entity which contains two lookup fields;
Account Contact, which points to theContactentity
Leased Equipment, which points to theEquipmententity
How can I get the name of the entity that each of the fields relates to?
I expected to see this in the MetadataSchema.AttributeLookupValue table, but this doesn't seem to be the right place.
Ideally, I'd like to write a query for this information. Something along the lines of:
SELECT
AttributeName,
AttributeTypeName,
ReferencedEntityName
FROM
unknown.TableName
WHERE
AttributeName IN ('new_AccountContact', 'new_LeasedEquipment')
-- Results:
-- AttributeName | AttributeTypeName | ReferencedEntityName
-- new_AccountContact | lookup | Contact
-- new_LeasedEquipment | lookup | Equipment
Any help trying to achieve this would be appreciated, thanks.
sql-server dynamics-crm dynamics-crm-2011 microsoft-dynamics asked Nov 12 '18 at 16:51
Klicker
189111
Within the CRM database, where can I find the name of the entity that a lookup field relates to?
For example, I have an Account entity which contains two lookup fields;
Account Contact, which points to theContactentity
Leased Equipment, which points to theEquipmententity
How can I get the name of the entity that each of the fields relates to?
I expected to see this in the MetadataSchema.AttributeLookupValue table, but this doesn't seem to be the right place.
Ideally, I'd like to write a query for this information. Something along the lines of:
SELECT
AttributeName,
AttributeTypeName,
ReferencedEntityName
FROM
unknown.TableName
WHERE
AttributeName IN ('new_AccountContact', 'new_LeasedEquipment')
-- Results:
-- AttributeName | AttributeTypeName | ReferencedEntityName
-- new_AccountContact | lookup | Contact
-- new_LeasedEquipment | lookup | Equipment
Any help trying to achieve this would be appreciated, thanks.
sql-server dynamics-crm dynamics-crm-2011 microsoft-dynamics sql-server dynamics-crm dynamics-crm-2011 microsoft-dynamics asked Nov 12 '18 at 16:51
Klicker
189111
asked Nov 12 '18 at 16:51
Klicker
189111
asked Nov 12 '18 at 16:51
Klicker
189111
asked Nov 12 '18 at 16:51
Klicker
189111
asked Nov 12 '18 at 16:51
Klicker
189111
189111
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The query below returns the primary entity, lookup field name, and the target entity type of the lookup field.
SELECT e.Name 'primary entity' , a.LogicalName 'lookup field in primary entity' , a.ReferencedEntityObjectTypeCode , e2.LogicalName as 'target entity of the lookup field'
FROM MetadataSchema.Attribute a inner join MetadataSchema.Entity e on a.EntityId = e.EntityId
inner join MetadataSchema.Entity e2 on a.ReferencedEntityObjectTypeCode=e2.ObjectTypeCode
WHERE ReferencedEntityObjectTypeCode <>0
AND e.name='account'
answered Nov 13 '18 at 4:33
Alessi
67939
Thanks for you suggestion, however this doesn't give the the name of the entity that the lookup relates to. Instead, this gives me the Name of the Contact instance or the Name of the Leased Equipment. For example, if my Contact was called John Smith, this query returns John Smith, instead of returning Contacts (the name of the entity that the lookup field relates to).
– Klicker
Nov 13 '18 at 9:22
updated original answer to address your question
– Alessi
Nov 14 '18 at 2:42
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53266700%2fdynamic-crm-sql-query-to-get-entity-that-lookup-relates-to%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The query below returns the primary entity, lookup field name, and the target entity type of the lookup field.
SELECT e.Name 'primary entity' , a.LogicalName 'lookup field in primary entity' , a.ReferencedEntityObjectTypeCode , e2.LogicalName as 'target entity of the lookup field'
FROM MetadataSchema.Attribute a inner join MetadataSchema.Entity e on a.EntityId = e.EntityId
inner join MetadataSchema.Entity e2 on a.ReferencedEntityObjectTypeCode=e2.ObjectTypeCode
WHERE ReferencedEntityObjectTypeCode <>0
AND e.name='account'
answered Nov 13 '18 at 4:33
Alessi
67939
Thanks for you suggestion, however this doesn't give the the name of the entity that the lookup relates to. Instead, this gives me the Name of the Contact instance or the Name of the Leased Equipment. For example, if my Contact was called John Smith, this query returns John Smith, instead of returning Contacts (the name of the entity that the lookup field relates to).
– Klicker
Nov 13 '18 at 9:22
updated original answer to address your question
– Alessi
Nov 14 '18 at 2:42
add a comment |
The query below returns the primary entity, lookup field name, and the target entity type of the lookup field.
SELECT e.Name 'primary entity' , a.LogicalName 'lookup field in primary entity' , a.ReferencedEntityObjectTypeCode , e2.LogicalName as 'target entity of the lookup field'
FROM MetadataSchema.Attribute a inner join MetadataSchema.Entity e on a.EntityId = e.EntityId
inner join MetadataSchema.Entity e2 on a.ReferencedEntityObjectTypeCode=e2.ObjectTypeCode
WHERE ReferencedEntityObjectTypeCode <>0
AND e.name='account'
answered Nov 13 '18 at 4:33
Alessi
67939
Thanks for you suggestion, however this doesn't give the the name of the entity that the lookup relates to. Instead, this gives me the Name of the Contact instance or the Name of the Leased Equipment. For example, if my Contact was called John Smith, this query returns John Smith, instead of returning Contacts (the name of the entity that the lookup field relates to).
– Klicker
Nov 13 '18 at 9:22
updated original answer to address your question
– Alessi
Nov 14 '18 at 2:42
add a comment |
The query below returns the primary entity, lookup field name, and the target entity type of the lookup field.
SELECT e.Name 'primary entity' , a.LogicalName 'lookup field in primary entity' , a.ReferencedEntityObjectTypeCode , e2.LogicalName as 'target entity of the lookup field'
FROM MetadataSchema.Attribute a inner join MetadataSchema.Entity e on a.EntityId = e.EntityId
inner join MetadataSchema.Entity e2 on a.ReferencedEntityObjectTypeCode=e2.ObjectTypeCode
WHERE ReferencedEntityObjectTypeCode <>0
AND e.name='account'
answered Nov 13 '18 at 4:33
Alessi
67939
The query below returns the primary entity, lookup field name, and the target entity type of the lookup field.
SELECT e.Name 'primary entity' , a.LogicalName 'lookup field in primary entity' , a.ReferencedEntityObjectTypeCode , e2.LogicalName as 'target entity of the lookup field'
FROM MetadataSchema.Attribute a inner join MetadataSchema.Entity e on a.EntityId = e.EntityId
inner join MetadataSchema.Entity e2 on a.ReferencedEntityObjectTypeCode=e2.ObjectTypeCode
WHERE ReferencedEntityObjectTypeCode <>0
AND e.name='account'
answered Nov 13 '18 at 4:33
Alessi
67939
edited Nov 14 '18 at 2:42
answered Nov 13 '18 at 4:33
Alessi
67939
answered Nov 13 '18 at 4:33
Alessi
67939
answered Nov 13 '18 at 4:33
Alessi
67939
67939
Thanks for you suggestion, however this doesn't give the the name of the entity that the lookup relates to. Instead, this gives me the Name of the Contact instance or the Name of the Leased Equipment. For example, if my Contact was called John Smith, this query returns John Smith, instead of returning Contacts (the name of the entity that the lookup field relates to).
– Klicker
Nov 13 '18 at 9:22
updated original answer to address your question
– Alessi
Nov 14 '18 at 2:42
add a comment |
Thanks for you suggestion, however this doesn't give the the name of the entity that the lookup relates to. Instead, this gives me the Name of the Contact instance or the Name of the Leased Equipment. For example, if my Contact was called John Smith, this query returns John Smith, instead of returning Contacts (the name of the entity that the lookup field relates to).
– Klicker
Nov 13 '18 at 9:22
updated original answer to address your question
– Alessi
Nov 14 '18 at 2:42
Thanks for you suggestion, however this doesn't give the the name of the entity that the lookup relates to. Instead, this gives me the Name of the Contact instance or the Name of the Leased Equipment. For example, if my Contact was called John Smith, this query returns John Smith, instead of returning Contacts (the name of the entity that the lookup field relates to).
– Klicker
Nov 13 '18 at 9:22
Thanks for you suggestion, however this doesn't give the the name of the entity that the lookup relates to. Instead, this gives me the Name of the Contact instance or the Name of the Leased Equipment. For example, if my Contact was called John Smith, this query returns John Smith, instead of returning Contacts (the name of the entity that the lookup field relates to).
– Klicker
Nov 13 '18 at 9:22
updated original answer to address your question
– Alessi
Nov 14 '18 at 2:42
updated original answer to address your question
– Alessi
Nov 14 '18 at 2:42
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53266700%2fdynamic-crm-sql-query-to-get-entity-that-lookup-relates-to%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
