Display data from external datasource (same SQL server)

(0) Share

Report

Posted on by Community Member

Hello all,

I have an on premise CRM 365 version 8.2 and a custom database on the same SQL server where CRM's db resides.

Each row in the custom table has a GUID or a unique value which is also stored in the lead record to tie them together. I need to display the related data stored in the custom table on the lead form in the simplest possible way. It should NOT be a grid as it's effectively about 50 columns of data for 1 record and I am only showing those 50 related to the currently opened 1 record.

I understand I can create an HTML web resource, but I cannot find a walkthrough on the exact same problem or at least similar enough where the page will take the GUID parameter and display the relevant info.

Thanks for your help

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (1)

Sort by

David Jennaway 14,065 on at

Like (0)

Report
Copy link

Link copied!
Currently there are 2 main approaches you could take:

Create dummy fields on the lead entity and form for each field you want to display from the custom database. Write a plugin on the Retrieve message for the lead entity that reads the data from the custom database and populates these fields

Create a web resource to query the data from the custom database and display it. As the data has to come from the server, you could create an ASPX page to do this. Or, you could create a reporting services report and render that in the web resource

Once CRM v9 is available On Premise, the third option would be to use a virtual entity

Was this reply helpful? Yes No
Verified answer

Mahendar Pal 45,095 on at

Like (0)

Report
Copy link

Link copied!

Hi,

Some option could be to create a SSRS report and have button on the lead entity form to show that report.

Another options chould be to have a REST based webservice to return your custom table data and further use Js code like this us.hitachi-solutions.com/.../crm-2011creating-a-3rd-party-dashboard-widget to show that data entity forms

Hope it will help you.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi David,

thanks for your response.

Option 1 is not really an option for me - I can easily copy the data over to CRM without the need of plugins. I am trying to avoid placing the data, even if it's just temporarily, inside CRM.

Option 2 is what I am after - I was looking for a walkthrough on how to achieve this. Also, remember I am not looking to present the data in a grid - it's over 50 data fields and won't be useful on a single line. Custom SSRS report won't look good for the same reason.

Do you have a good walkthrough of creating an HTML page, reading data directly from SQL where the select statement will have a parameter passed by JS onload of the lead form??

Thanks

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Mahen,

SSRS doesn't work as I have too many data fields and only 1 row to present these, so I don't want the user to be scrolling to the right of the page endlessly.

The other example you have sent is NOT retrieving the data from a SQL server directly, so it's not appropriate either.

Ideally, it should be something as simple and clean as the below example. It will need to retrieve the appropriate data based on a parameter on the lead.

DataItem1: info about item DataItem2: info about item

DataItem3: info about item DataItem4: info about item

DataItem5: info about item DataItem6: info about item

DataItem7: info about item DataItem8: info about item

DataItem9: info about item DataItem10: info about item

Was this reply helpful? Yes No