Making an External Table Available as an SData Gadget in the Interactive Dashboard

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Making an External Table Available as an SData Gadget in the Interactive Dashboard

  • Comments 6
  • Likes

Sage CRM has the ability to link to external databases and then allow the System Administrator or Implementor to build screens that look and behave very like system screens.

I have written about this before in several articles

Linking to 3rd Party Databases

Connecting to an External ODBC database e.g. Sage ERP MAS 90

Once a connection to an external database table has been made then this can be used either to build customizations using ASP pages, the .NET API or even a simple use of RunBlock.

Creating Lists using RunBlock

But a customer may want to make external database information available in the Interactive Dashboard.  But List gadgets can only use data provided by Reports, Groups, Saved Searches, and saved Advanced Find definitions.  By default it is not immediately possible to define any of those structures on external tables.  It may seem that you can't have external data available in the Interactive Dashboard but in fact you can by using the SData gadget.

You can prove this yourself by following these steps

1.  Create the connection to an external database. E.g. Northwind. 

2. Create the connection to the Suppliers table.

3. Edit the field of the Supplier table to confirm the entrytypes and translations.

Administration -> Customization -> Suppliers

4. Edit the External Access to allow SData access.

5. Then a user can create a new Interactive Dashboard

6. A new SData gadget can be added.  And use the CRM SData provider.

7. The Suppliers table should then be listed

8. The fields from the Suppliers table can then be selected.

9. The Gadget then needs to be given a name. E.g. Northwind Suppliers

10: The data from the Suppliers table will then appear in the Interactive Dashboard.

 

Comments
  • Hi, Jeff

    We've been working on a v7.1a CRM, trying to customize some gadgets base on external tables from SAGE Murano ERP.

    We've followed your instructions to link two tables and we've seen that one of them works properly but the other doesn't: after the last step, the next message is shown into the gadget:

    "An error occurred when attempting to process the gadget"

    Could you mind giving us some address on where we could search for the problem?

    Thanks in advance for your support.

    Best regards,

    Jose

  • Have you looked inside the scrm logs?

  • I think we've got it!

    Thanks for your quick support.

    Best regards

    José

  • Hi Jeff - We are trying to do this exact thing in 7.2.  Everything goes well to the point where we want to select the fields to display in the gadget and we are given no fields to choose from.  Any idea of what might be the problem?

    Regards

    John

  • Do the columns show in the SData feed?

    e.g. http://localhost/sdata/crmj/sagecrm/-/customentity/$schema

  • Jeff/John,

    We have the same issue in 7.2d with no fields to choose from when setting up the SData List gadget and the columns don't show in the schema xml.  

    Was a resolution found by either of you?