A customer wanted to be able to access groups from an external system. I have written about the way in which groups are defined in Meta Data in the article "Reports, Saved Searches and Groups" and this may be useful as background reading.

Groups can be either Static or Dynamic. Static groups are tied into data held in the Key Attribute table and can be thought of as a collection of 'hand picked' records. A Dynamic group is a result set produced by a SQL query. If you are considering using Groups as a data source then Dynamic groups would be much easier to use. This is the reasoning behind Dynamic Groups and NOT Static groups being able to be used as a data source in List gadgets in the Interactive Dashboard.

But Consider this...

I logged on to Sage CRM as 'Susan Maye' and navigated to the Search screens. I searched for Persons who were in the city 'New York'. I then created a dynamic group based on this search.

This group was then available under the My CRM, Groups tab.

The Group was saved with the name 'New York' into the custom_reports table. This table is used to store not just report definitions but also Saved Searches and Groups.

[code language="sql"]
select * from custom_reports where repo_name = 'New York'
[/code]

In this example the Dynamic Group had the repo_reportid of 167.

The SQL used by the Dyanmic Group to return the data is held in the Custom_reportsearches table and the repo_reportid could be used to retrieve the SQL used.

[code language="sql"]
select * from custom_reportsearches where rese_reportid = 167
[/code]

The SQL is held in the rese_SQLText and in this example was;

[code language="sql"]
SELECT DISTINCT Pers_PersonID, Pers_LastName, Pers_FirstName, Comp_Name, Pers_PhoneFullNumber, Pers_EmailAddress, pers_secterr, Pers_AccountId, UPPER(Pers_LastName) FROM vSearchListPerson WHERE addr_city LIKE N'New York%' ESCAPE '|' AND Pers_PersonID IS NOT NULL ORDER BY UPPER(Pers_LastName)
[/code]

If I wanted to use the group definition from an external application I would have to

1. Expose the Custom_Reports and Custom_ReportSearches table to webservices
2. Retrieve the SQL from the rese_SQLText field
3. Parse the SQL and convert it either into parameters usable by the SOAP web services or by the ReadOnly SData feeds.

But as we can see SQL above the Group actually uses a view to retrieve its data so that view would also have to be exposed to web services. This can be done but we would end up having to do quite a bit of work to expose the tables and views to web services.

And then there are a number of questions about Groups availablity you would have to considered too....

• Is this a private Group?
• Should all people have access to it? (Or only those people who have Info Manager or Admin rights)

And this leads us to the point of this article.

What advantage does a Dynamic Group provide over direct access to a View?

Groups are based ultimately on Views. Dynamic groups store the search criteria that are used to restrict the rows returned by the view but a view can be made directly available as a SData source and we can pass query string information to the SData view to return the data.

Territory Security, Security Profiles and Field Level Security are applied for both SOAP and SData (REST) web services. A user once authenticated should not be able to see data or perform actions against data that they are not authorised to perform.

The view used in the example discussed in this article is 'vSearchListPerson'

This view is a system view and is not available to be exposed to SData but we can create our own version of this view.

The view once exposed to SData can be accessed. Below is a request for its schema

http://[servername]/sdata/[installname]j/sagecrm/-/vsdatapersonsearch/$schema

The SData syntax allows an external application to form a request that limits rows returned. If the SQL needs to be

[code language="sql"]
SELECT DISTINCT Pers_PersonID, Pers_LastName, Pers_FirstName, Comp_Name, Pers_PhoneFullNumber, Pers_EmailAddress, pers_secterr, Pers_AccountId, UPPER(Pers_LastName) FROM vsdatapersonsearch WHERE addr_city LIKE N'New York%' ESCAPE '|' AND Pers_PersonID IS NOT NULL ORDER BY UPPER(Pers_LastName)
[/code]

then the equivalent SData request would be

http://[servername]/sdata/[installname]j/sagecrm/-/vsdatapersonsearch?where=addr_city eq 'New York' &orderBy=Pers_LastName

The authenicated user retrieving the SData feed can never see data that they are not allowed to see and it would be much easier to just use an SData available view and than create the 'Grouping' where clause in the 3rd party application rather than have to program for interrogating 'Groups' in Sage CRM.

If a user without sufficient rights does attempt to access the same SData feed then they will be disappointed by the results as the SData payload will be empty! Or rather they will only know how many records they are not allowed to see!

The SData web service provides a very easy way of allowing a 3rd Party applications access to 'Group-like' data while at the same time ensuring strict security.