Hi

I'm hopping Paul or someone else familiar with SQLGridSelectedView can help me out.

Using SQLGridSelectedView I have a query that displays data from the UserProfile table for several added fields.
My Query works just fine until I try using a filter.
The goal is to filter by "Grade".

Below is what I have tried. The grid shows up with all records just fine, when I try to use the filter I get the following error:
"Error on Primary Select: The multi-part identifier "UserProfile.PropertyValue" could not be bound."

Not sure if this has to do with the inner join, or maybe something in the clause.
Can you help me???


Thanks,
Robert


My Query (Run's fine by itself) ********************************
select DisplayName,
(Select PropertyValue from userProfile where UserID = Users.UserID and PropertyDefinitionID = 63) as [Child Last Name],
(Select PropertyValue from userProfile where UserID = Users.UserID and PropertyDefinitionID = 62) as [Child First Name],
(Select PropertyValue from userProfile where UserID = Users.UserID and PropertyDefinitionID = 44) as [Grade],
(Select PropertyValue from userProfile where UserID = Users.UserID and PropertyDefinitionID = 45) as [Sport],
(Select PropertyValue from userProfile where UserID = Users.UserID and PropertyDefinitionID = 82) as [2nd Child First Name],
(Select PropertyValue from userProfile where UserID = Users.UserID and PropertyDefinitionID = 87) as [2nd Grade],
(Select PropertyValue from userProfile where UserID = Users.UserID and PropertyDefinitionID = 88) as [2nd Sport]
from users
inner join UserRoles on users.userid = userroles.userid
where userroles.RoleID = 1
ORDER by [Child Last Name], [Child First Name]

The Filter ***************************
Parm Name: Grade
Prompt: Select Grade
Select: Select Distinct PropertyValue as Grade from UserProfile where PropertyDefinitionID = 44
Clause: UserProfile.PropertyValue = '[Parm:Grade]'

Required: Yes
Allowed: Yes

Hi Paul,

That worked great!!! Thanks for the help. I tried to email you at "sqlgridselectedview@tressleworks.ca" but got a delivery notification error. Is their another email address I can send to?

Since that was so easy... :-)

You up for a tougher one?

You saw in my query where I am pulling "Child First Name", "Grade", "Sport", and "2nd Child First Name", "2nd Grade", "2nd Sport".

Is there away to combine the columns and data?

"Child First Name" with "2nd Child First Name"
"Grade" with "2nd Grade"
"Sport" with "2nd Sport"

Again, Thanks for the prompt excellent reply.

Robert