locked
Accessing foreign keys within entities RRS feed

  • Question

  • Dynamics version 2013

    I would like to make a report that requires me to pull together a range of information from various entities.

    I would like to be able to access the foreign keys that exist in some entitites and have discovered that I cannot do this via advanced find. Can I access foreign keys through reporting tools such as SSRS, to build reports with content based on relationships such as:

    Product > OpportunityProduct < Opportunity < Contact < Account

    I have noticed in the Microsoft entity relationship diagrams for CRM that foreign key IDs are represented, (but these are shown as as topics/titles when viewed in the advanced find). Are they represented as proper keys in the entities when viewed via SQL services?  




    • Edited by MMGB Monday, July 13, 2015 2:45 PM
    Monday, July 13, 2015 1:29 PM

Answers

  • When building a report, you should not access the tables directly. The supported way is using FilteredView for each entity, eg. FilteredAccount, FilteredNew_Entity as Microsoft might alter the table structure in the next major upgrade/version. Other reason is FilteredView returns records based on the security role assigned to the current user.

    The foregin key concept is represented by a relationship. For each relationship, 3 fields will be returned, ie. the Id field (which is GUID), the Name (string, eg. Account Name), and the typecode field (which is the entity type, eg. 1 for Account).

    For example, Account has many Opportunities via ParentCustomerId relationship. Then in FilteredOpportunity view, you will find three fields, ie. ParentCustomerId, ParentCustomerIdName, ParentCustomerIdTypeCode.

    You can then build your query like

    Select a.Name, o.Title from FilteredAccount a

    inner join FilteredOpportunity o on a.accountid = o.parentcustomerid


    • Marked as answer by MMGB Friday, August 7, 2015 10:06 AM
    Tuesday, July 14, 2015 12:25 AM

All replies

  • When building a report, you should not access the tables directly. The supported way is using FilteredView for each entity, eg. FilteredAccount, FilteredNew_Entity as Microsoft might alter the table structure in the next major upgrade/version. Other reason is FilteredView returns records based on the security role assigned to the current user.

    The foregin key concept is represented by a relationship. For each relationship, 3 fields will be returned, ie. the Id field (which is GUID), the Name (string, eg. Account Name), and the typecode field (which is the entity type, eg. 1 for Account).

    For example, Account has many Opportunities via ParentCustomerId relationship. Then in FilteredOpportunity view, you will find three fields, ie. ParentCustomerId, ParentCustomerIdName, ParentCustomerIdTypeCode.

    You can then build your query like

    Select a.Name, o.Title from FilteredAccount a

    inner join FilteredOpportunity o on a.accountid = o.parentcustomerid


    • Marked as answer by MMGB Friday, August 7, 2015 10:06 AM
    Tuesday, July 14, 2015 12:25 AM
  • Hi,

    Can you provide a bit more info of what you want to do, you can get pretty much info from related entities with the advanced find so it should be possible.

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Wednesday, July 15, 2015 7:25 AM
  • Hi Rick,

    I am trying to pull out the client Account Address and Contact Address with the opportunity title and the specific products they have subscribed to, including costing/paid monies associated with the product.

    Monday, July 20, 2015 10:08 AM