If the client wants a distinct on the practice for the search, why are you worrying about bringing over the staff records in the view used for the search? As soon as you do that, any DISTINCT would be lost, as the rows would be distinct with those records in them.

The options that I can think of for you are:

1. Create a 'primary' staff member relationship like the standard person to company, and bring over that one.

2. Do not join to the staff table in the view that is used for the view.

3. In your view, use a derived table on the staff table to show you the minimum staff ID for each company, and join to that, so you are only showing one staff member per practice.

Hi Toby,

Thanks for your reply. That certainly would be the easy answer wouldn't it :-) However, I'm sure you know how customers don't like to make things easy. They want to be able to search by staff name to show the practice(s) where that staff works, but they just want to bring back one practice :-) That's why I was hoping that the SelectSql property would work where I can set it to 'SELECT DISTINCT prac_name, etc... FROM vwPracticeStaff' instead of using SELECT *. But unfortunately it's not working that way. Bug perhaps?? Anyway thanks again for your reply :-)

Using the example Sage give here:

community.sagecrm.com/.../GS_CreateCustomQueries.htm

I created a page with code:

<%
NewList = CRM.GetBlock("list");
NewList.SelectSql= "SELECT DISTINCT Comp_name,pers_firstname,Pers_LastName FROM Company INNER JOIN Person ON Pers_CompanyId = Comp_CompanyId WHERE
pers_deleted IS NULL AND comp_deleted IS NULL AND comp_name = 'Magnetic Software Ltd.'";
NewList.AddGridCol("Comp_Name");
NewList.AddGridCol("Pers_firstname");
NewList.AddGridCol("Pers_lastname");
CRM.AddContent(NewList.Execute());
Response.Write(CRM.GetPage());
%>

And it works as expected, so it appears you can use a distinct for the SelectSql property. For reference, I tested this in 7.3b

Can you post up the full SQL error you are getting?

However, that said, I am still not confident it will do what you are looking for it to do. If you are including Staff records on the distinct, the rows will be distinct and will therefore will return a record for each staff member. Take the below as an example:

As I am bringing back the company and person records, it will return a record for each row that is distinct, and as the two person names are different, it will return two rows.

Hey Toby,

So the query I would use instead of the above is...

So you could still use the where clause because it's in the underlying source but I can just select the distinct company records. Interesting that you were able to get that to work, but you are using a custom list so maybe that's the issue. I'm using a Search Box attached to an existing list that's already in the metadata. So my asp page looks like this..

<%

CRM.SetContext("Find");

if (CRM.Mode

var sURL=new String( Request.ServerVariables("URL")() + "?" + Request.QueryString );

searchEntry=CRM.GetBlock("PracticeSearchBox");
searchEntry.Title=CRM.GetTrans("Tabnames","Search");
searchEntry.ShowSavedSearch=true;
searchEntry.UseKeyWordSearch=true;

searchList=CRM.GetBlock("PracticeGrid");

searchList.SelectSQL = "SELECT DISTINCT prac_name, prac_city, prac_state, prac_org FROM vwPracticeStaff";

searchContainer=CRM.GetBlock("container");

searchContainer.ButtonTitle="Search";
searchContainer.ButtonImage="Search.gif";

searchContainer.AddBlock(searchEntry);
if( CRM.Mode != 6)
searchContainer.AddBlock(searchList);

searchContainer.AddButton(CRM.Button("Clear", "clear.gif", "javascript:document.EntryForm.em.value='6';document.EntryForm.submit();"));

searchList.prevURL=sURL;

CRM.AddContent(searchContainer.Execute(searchEntry));

Response.Write(CRM.GetPage());

%>

But when I do a search i get the following error in the sql logs.

Aug 25 2015 8:48:24.056 12100 12484 1 fselectsql,time,sql,errormsg 0  WHERE prac_city LIKE N'kingwood%' ESCAPE '|' Incorrect syntax near the keyword 'WHERE'

Aug 25 2015 8:46:11.573 12100 12484 1 SQL Error : Incorrect syntax near the keyword 'WHERE' ( WHERE prac_city LIKE N'kingwood%' ESCAPE '|')

It's ignoring the SelectSQL clause completely. It's not even putting the select clause in when it's building the statement.

I think it is ignoring the SelectSQL because it already has a definition for the list because it is based on a meta data defined list 'PracticeGrid'.

Hi Jeff. Thanks for you response. I did actually think of that so I built a grid within the asp page instead and unfortunately it still did not work. I could finally use the SelectSql and bring back the columns I wanted using the custom grid, however as soon as I put in the word DISTINCT it went all crazy on me :-(