Creating a view for SData access

SData (Sage Data) is a Sage Standard which enables desktop, server, and web-based Sage applications to communicate with each other as well as third-party applications and the Web. SData is built on top of leading industry standards including HTTP, XML, REST, and Atom/RSS.

In Sage CRM SData "feeds" can be consumed in a read-only format by Sage CRM via the Interactive Dashboard and by third-party applications.

The view in this example includes a derived field, oppo_daysopen, to show how long an opportunity has been open. When created as an SData view, this can easily be made available to the users via the Interactive Dashboard. The SData view could also be used by a third-party application which supports SData feeds.

SData views are not affected by the SData setting on the External Access tab. In this example, the Read-only SData setting on the <My Profile> | Administration | Customization | Primary Entities | Opportunity | External Access tab can be set to Yes or No. The view is still available for access.
  1. Click <My Profile> | Administration | Customization | Primary Entities | Opportunity | Views.
  2. Click New.
  3. Enter the View Name. For example vOppoDatesOpen.
  4. Select SData View.
  5. Enter a description and translation.
  6. Enter SQL for the new view. For example, change the existing:
    Copy
    CREATE VIEW vOppoDatesOpen AS SELECT * FROM OPPORTUNITY

    to:

    Copy
    CREATE VIEW vOppoDatesOpen AS SELECT RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) AS Pers_FullName, Pers_PersonId, Pers_CreatedBy, Pers_SecTerr, Pers_PrimaryUserId, Pers_ChannelID, Pers_EmailAddress, Comp_Name, Comp_CompanyId, Comp_CreatedBy, Comp_SecTerr, Comp_PrimaryUserId, Comp_ChannelID, Chan_ChannelId, Chan_Description, Comp_EmailAddress, datediff(day, getdate(),oppo_opened) as oppo_daysopen, Opportunity.*, (COALESCE(Oppo_Forecast, 0) * COALESCE(Oppo_Certainty, 0)) / 100 AS Oppo_Weighted FROM Opportunity LEFT JOIN Person ON Pers_PersonId = Oppo_PrimaryPersonId LEFT JOIN Company ON Comp_CompanyId = Oppo_PrimaryCompanyId LEFT JOIN Channel ON Oppo_ChannelId = Chan_ChannelId WHERE Oppo_Deleted IS NULL
  7. Click Save.
  8. To test the view on the Interactive Dashboard, click My CRM | Dashboard and open a dashboard.
  9. Click Modify Dashboard | Add New Gadget | SData Feed.
  10. Select CRM SData Provider and click Next.
  11. Select the new view you created, and click Next.
  12. Select the columns to display on the gadget and complete the gadget wizard steps.

The SData view you created is represented in a new gadget on the user's landing page. The URL to make the view available to another application would take the following format:

http://myserver/sdata/[installname]j/sagecrm/-/vOppoDatesOpen