locked
Advanced Find with external data RRS feed

  • Question

  • I have my clients inside Dynamics CRM, and track their invoices & sales (let's call this InventorySales ) in a home grown solution which keeps the data in it's own database.  I need the ability to search in the CRM against the InventorySales data.

    The solution I'd like to use is this: setup Advanced Find to query a view in the CRM, and have that view select from the database that houses the InventorySales data.  The view is ideal since it will always pull the latest data.

    Without a 3rd party integration tool, how can I achieve this behavior?  I know I can use iFrames and make my own search, but I'm not looking for that.  The solution must enable the user to utilize Advanced Find, and the data must originate from the external table.

    Thanks!


    -jeff
    Thursday, February 3, 2011 5:05 AM

Answers

  • Hi Jeff, the CRM Advanced Find feature is limited to querying the CRM entities. There's no easy way to query another database without:

    1. Writing your own Advanced Find feature to replace the standard feature (definitely not recommended unless your unlimited budget is burning a hole in your pocket!)
    2. Integrating the data from your InventorySales database with CRM through a manual, batch or real-time synchronisation (Scribe is a popular middleware solution for this, but SQL Server Integration Services is often sufficient).
    3. Using SQL Server Reporting Services to pull the information from two different data sources into a report.
    4. Using a third-party ad-hoc query tool to query the information from two different data sources (QlikView is popular for this type of ad-hoc querying, but with sufficient effort and expertise Excel 2010 should be sufficient).

    Options 2 and 4 are the most popular among the Dynamics CRM customers I've worked with.


    Neil Benson, CRM Addict and MVP at Customery Ltd. You can reach me on LinkedIn or Twitter. Join over 10,000 other CRM professionals on the Microsoft Dynamics CRM group on LinkedIn.

    Thursday, February 3, 2011 9:43 AM
    Moderator
  • Hi Jeff, your suggested solution involves direct database integration using triggers which isn't a supported method. While some unsupported methods are worth the risk (like some JavaScript form UI methods), I wouldn't take the risk on this one if I were you. There's a real chance of screwing with CRM's database integrity -- not a massive risk since the tables involved are custom tables with data mastered in another system, but it's not an approach I'd recommend.

    Neil Benson, CRM Addict and MVP at Customery Ltd. You can reach me on LinkedIn or Twitter. Join over 10,000 other CRM professionals on the Microsoft Dynamics CRM group on LinkedIn.

    • Proposed as answer by Jim Glass Jr Thursday, February 3, 2011 8:03 PM
    • Marked as answer by Jim Glass Jr Thursday, February 3, 2011 8:03 PM
    Thursday, February 3, 2011 2:53 PM
    Moderator

All replies

  • Hi,

    why dont you consider, creating an entity( or entities) in crm, and populate it tthrough back end( some scheduled job) with the Inventorysales data.

    Then you can use advanced find functionality itself without trying to alter the default CRM views.

    Regards,

    Soumya.

     


    soumya
    Thursday, February 3, 2011 5:39 AM
  • Hi Jeff, the CRM Advanced Find feature is limited to querying the CRM entities. There's no easy way to query another database without:

    1. Writing your own Advanced Find feature to replace the standard feature (definitely not recommended unless your unlimited budget is burning a hole in your pocket!)
    2. Integrating the data from your InventorySales database with CRM through a manual, batch or real-time synchronisation (Scribe is a popular middleware solution for this, but SQL Server Integration Services is often sufficient).
    3. Using SQL Server Reporting Services to pull the information from two different data sources into a report.
    4. Using a third-party ad-hoc query tool to query the information from two different data sources (QlikView is popular for this type of ad-hoc querying, but with sufficient effort and expertise Excel 2010 should be sufficient).

    Options 2 and 4 are the most popular among the Dynamics CRM customers I've worked with.


    Neil Benson, CRM Addict and MVP at Customery Ltd. You can reach me on LinkedIn or Twitter. Join over 10,000 other CRM professionals on the Microsoft Dynamics CRM group on LinkedIn.

    Thursday, February 3, 2011 9:43 AM
    Moderator
  • You could use a report with CRM filter. That way you would be able to use advanced find as well as query the legacy system using SSRS

    HTH


    Mamta
    Thursday, February 3, 2011 11:00 AM
  • You can do Follwing but it's tricky

    1) Create custom entity

    2) Write Plug in on retrive multiple which will pull latest data from you inventory (Integration).

    not exist in the system

    I thought it will help

    Note : I am consider that your invoices data never get updated (it will be revised only)


    Nikkas MIcrosoft Dynamics Crm 4.0 Technology Specialist
    Thursday, February 3, 2011 11:19 AM
  • Yeah, we're not looking for a report, but a usable grid result that users can double-click to go to the relevant record.

     

    What about this:

    1. Create the custom entities that I need for the CRM to access
    2. Setup replication on the InventorySales tables from our external source to do a real-time replication into the CRM database (using the same table & field names).
    3. Adding triggers on the replicated tables that populates the custom entities with the appropriate records (or removing the appropriate records, depending on the trigger)
    We're trying to achieve real-time changes, with minimal impact on the end user and their standard CRM use.
    -jeff
    Thursday, February 3, 2011 2:43 PM
  • Hi Jeff, your suggested solution involves direct database integration using triggers which isn't a supported method. While some unsupported methods are worth the risk (like some JavaScript form UI methods), I wouldn't take the risk on this one if I were you. There's a real chance of screwing with CRM's database integrity -- not a massive risk since the tables involved are custom tables with data mastered in another system, but it's not an approach I'd recommend.

    Neil Benson, CRM Addict and MVP at Customery Ltd. You can reach me on LinkedIn or Twitter. Join over 10,000 other CRM professionals on the Microsoft Dynamics CRM group on LinkedIn.

    • Proposed as answer by Jim Glass Jr Thursday, February 3, 2011 8:03 PM
    • Marked as answer by Jim Glass Jr Thursday, February 3, 2011 8:03 PM
    Thursday, February 3, 2011 2:53 PM
    Moderator