locked
Advance Find with more than one table? RRS feed

  • Question

  • Hi,

    I've only been building simple views based on one table so far but...
    Now I need a view using two tables at once.

    I want to query two custom "Extension" tables with a common key field.
    ShipTo.[ShipTo Account]
    MPD.[Parent Account]

    Can I use Advance Find in Dynamics or will I need something else?

    Here is a simple example of the SQL from an Access query that uses the same tables and field that exit in Dynamics.

    SELECT ShipTo.ShipTo, ShipTo.[ShipTo State], MPD.[Machine ID]
    FROM ShipTo LEFT JOIN MPD ON ShipTo.[ShipTo Account] = MPD.[Parent Account]
    WHERE (((ShipTo.[ShipTo State]) Like "*" & [Enter a State] & "*") AND ((MPD.[Machine ID]) Is Not Null));

    Thanks

    Thursday, January 24, 2013 2:03 AM

Answers

  • In most cases if there is an existing relationship between 2 entities you can create an advanced find that can return data from both. 

    Take for example an advanced find starting with the account entity, if go to the bottom of the drop down list when specifying the field to use as criteria, you will see "Related" and under it is the other entities that are related to account (your ShipTo) - such as contact (your MPD) for example - here is would show as Contacts (Parent Customer). From here you could specify search criteria at the contact level and return the related accounts. 

    If you need something more complex, you would probably need to create a custom SSRS report. 


    Jason Lattimer
    My Blog -  Follow me on Twitter -  LinkedIn

    Thursday, January 24, 2013 4:31 AM
    Moderator

All replies

  • In most cases if there is an existing relationship between 2 entities you can create an advanced find that can return data from both. 

    Take for example an advanced find starting with the account entity, if go to the bottom of the drop down list when specifying the field to use as criteria, you will see "Related" and under it is the other entities that are related to account (your ShipTo) - such as contact (your MPD) for example - here is would show as Contacts (Parent Customer). From here you could specify search criteria at the contact level and return the related accounts. 

    If you need something more complex, you would probably need to create a custom SSRS report. 


    Jason Lattimer
    My Blog -  Follow me on Twitter -  LinkedIn

    Thursday, January 24, 2013 4:31 AM
    Moderator
  • Hi Jason,

    I had been experimenting with the relationship in Advanced Find and it seemed like it was working only partially.
    I was able to add both of the tables in the query selection - no problem.

    However, I when I wanted to add columns to the views display - only fields from the parent table were available.
    Just wondering if this sounds normal?

    I am used to older enterprise applications.
    I'm pretty familiar with queries and reports, but using direct access to the database & built in abilities to add joins and display data.
    Dynamics is something new we're trying.
    So maybe I'm just used to built-in tools that are different for Dynamics?

    Thanks,

    Bruce
    Thursday, January 24, 2013 6:49 PM
  • Yes - that is one limitation with the built in tools. The entity you are "joining" on doesn't present its fields for display in the result set (1:N relationship). Only those from the primary entity where there there is a N:1 relationship allow you to select the fields.

    It sounds like you are already familiar with reporting - a link for reference on creating reports and having them available from within CRM.

    Create Reports for Microsoft Dynamics CRM Using SQL Server Reporting Services

     

    Jason Lattimer
    My Blog -  Follow me on Twitter -  LinkedIn

    Friday, January 25, 2013 2:02 PM
    Moderator
  • Hi Jason,

    Thanks for the link.
    It brings up a new question about BIDs.
    Maybe you can help with this too?

    I am using OnLine Dynamics CRM so nothing is installed locally.

    It looks like I want Business Intelligence Development Studio (BIDS) but I'm not sure how.
    I've been reading conflicting and/or old posts.
    For example:
    - Use "Visual Studio 2008" advanced install
    - Use "MS SQL 2008 Express" advanced install
    - And unclear about the 2012 versions of Visual Studio and MS SQL Express.

    Currently I run Visual Studio 2010 and when I write reports I'm using Crystal 2008 for the most part.
    I do have MS SQL Server version 2000 running, I know its old but I haven't had any real need to upgrade.



    Thanks,

    Bruce
    Saturday, January 26, 2013 8:09 PM
  • If you have access to SQL Server 2008 media - you can install it from there. If you don't, SQL 2008 with Advanced Services also has the correct version of BIDS. When installing you'll come to a screen with features - you only need to check the BIDS option. 

    You will also need to install the Microsoft Dynamics CRM 2011 Report Authoring Extension in order to create the FetchXML based queries required for CRM online.


    Jason Lattimer
    My Blog -  Follow me on Twitter -  LinkedIn

    Sunday, January 27, 2013 6:37 AM
    Moderator