Unique Records

[jacsolutions]

A frustrating problem!

We have two tables, one containing names and addresses and the other containing the role that they have on the system (Member etc). A person can have multiple roles. I have a search grid which allows the users to choose the roles (multiple) and then display the records. Because I am including the role field in the SQL view (we use SQL 2005), it shows duplicate entries for the people. I need t osend these people an email or letter, so only want to include them once.

If I use the select unique feature in SQL, it still displays the records as the role field is included for searching. We are from an access background, so we would use the query and then hide the field and use group by to show unique records or use the unique records feature in the query writer.

I thought about attaching a grid to my results grid which linked to another view which did not have the role field and was set to DISTINCT records, but using the CurrentGridFilter() did not work, I just get an error message on the screen telling me it cannot get the count of the records.

I am obvioulsy missing something simple!

Stuck on this for a while now.

Any ideas please?

Thanks

Alan

[lvasic]

Re: Unique Records

This is a one to many pattern. I have several of these patterns where the form has gird that shows the roles. If you have a small number of roles you can do a sub-query in SQL. I have a pattern where they can be 1 to 3 types. The sub-query works well in this case.

[askoog66]

Re: Unique Records

Did you ever come up with a solution to your problem Alan?

I am trying to do something similar pulling from a View that has already combined the two tables. I am trying to create a grid, but SQL builder apparently does not allow select statement with GROUP BY that would eliminate the duplicates from the VIEW. As in your situation, cannot use a SELECT DISTINCT because I need the classifications/roles to filter from.

[TheSmitchell]

Re: Unique Records

Did you ever come up with a solution to your problem Alan?

I am trying to do something similar pulling from a View that has already combined the two tables. I am trying to create a grid, but SQL builder apparently does not allow select statement with GROUP BY that would eliminate the duplicates from the VIEW. As in your situation, cannot use a SELECT DISTINCT because I need the classifications/roles to filter from.

I have a grid where I did something like this. Except, instead of roles, it was members. We wanted to be able to pull up the groups to which a member belonged - since they could be in multiples. A linked grid was used to list all the related members. I included a left outer join on the table with all the members, but I don't include any of the fields from that join in my results. EG:

SELECT group.*,address.*,'' AS NAME
FROM group group
    LEFT OUTER JOIN address address
        ON group.ADDRESSID = address.ADDRESSID
    LEFT OUTER JOIN members members
        ON group.GROUPID = members.GROUPID
GROUP BY group.GROUPID
ORDER BY group.NAME

For searching purposes, I added an extra field, NAME, defined as an empty string. I inserted NAME into my search fields and then modified my OnSearchPartFilterCompute so I could search all the members by including that joined table in my search criteria:

function  OnSearchPartFilterCompute as v (e as p)

e.rtc.Filter_SearchPart = "(members.NAME_FIRST LIKE :search) OR (members.NAME_LAST LIKE :search)"

dim parameters as c
parameters = "%" + e.SearchDataSubmitted.NAME + "%|||C|search"

e.rtc.Parameters_SearchPart = parameters
end function

As an alternative to doing this, you can use GROUP_CONCAT(ROLE) as ROLES in your SQL select statement and include a GROUP BY clause that includes the ID for the duplicated records. GROUP_CONCAT will concatenate all the roles into a comma delimited string. Great for display purposes. Not so great if you're planning on modifying roles in the grid. The only reason that you see duplicates is that one of your columns has multiple entries for the same table. If that column is absent from your SELECT results, then you only get 1 record per ID.

Hope you find this helpful.
---
Sarah