Filtering data in a grid based UserID and multiple fields in a MSSQL and administrators to see all records

[Dolphin]

Please help me if you can with the following (I have struggled for over a week with this now):
1. I have a grid for contacts that is based on a MSSQL table with several other child tables linked to it.
2. I have a login processing page where I capture all the necessary session variables as per many videos on the subject.
3. I am able to to filter records in the contacts grid based on the logged in user successfully.
4. I have other fields (columns) in the contacts grid (based on the contacts table) that I want to base filtering on. These include the following: PrivateFlag that is a bit type that is handled as a logical field that saves a True or False value in MSSQL. I also have a SharedFlag with the same setup.
5. I have a another table linked to the Contacts table that is named SharedWith to specify that a record is shared with all users in the security or with certain users in the security.
6. I have setup the web security tables in MSSQL as per videos and it works.
7. I use a tabbed UI for all grids etc.

I need to do the following but do not seem to get any examples in the videos from Alpha or samples on this forum to do this type of complex filtering:

1. I need to be able to filter all entries in the contacts grid by the Logged in User. My current RegUserID field is numeric (bigint and a primary key). Filtering works when I setup it up in the Grid Query Section where my SQL Statement has a where clause that filters on the RegUserID = :RegUserID.
2. I also need any of the administrators to see all records when logged in. Do not seem to get this to work with samples given.
3. I need to have a complex filter to do the following. Logged in User sees only his records and those that he marked as private and those that he shared. The User must be able to choose who he can share records with based on a dropdown in the grid based on all the user in the web security or only specific users in the security. He must also only be able to see the full names of the users and not their usernames.
4. I need the same situation for administrators but they must not have the ability to see the records that are marked private by other users as these records are private to that user. The administrators must however be able to see their own private records and the records that are shared. They must also see who they are shared with. This means that all the users in the web security must be pulled into a drop down box or lookup. (and their full names must show)
5. I also need to ensure that there are no duplicate records in the contacts table as any specific records can be shared.
6. This is then a pattern that will be used throughout the rest of my application on other tables.

Hope that there is someone that can help me with this.
Thank you.

[RichCPT]

Re: Filtering data in a grid based UserID and multiple fields in a MSSQL and administrators to see all records

Breaking your post up into separate smaller issues will probably get you more feedback. Let me start with the first two things for you:

1. I need to be able to filter all entries in the contacts grid by the Logged in User. My current RegUserID field is numeric (bigint and a primary key). Filtering works when I setup it up in the Grid Query Section where my SQL Statement has a where clause that filters on the RegUserID = :RegUserID.
2. I also need any of the administrators to see all records when logged in. Do not seem to get this to work with samples given.

Do you have a session variable (like session.scUserIsAdmin) that you pass around to indicate if the current user is an "administrator"? If so, then I'd start the SQL "WHERE" clause with:

RegUserID = :RegUserID OR session.scUserIsAdmin = 'Y'

If you are having trouble with the grid's base filter working the second time the grid is called then you are not alone. There have been recent posts about that, search the newsgroup for "base filter".

[Dolphin]

Re: Filtering data in a grid based UserID and multiple fields in a MSSQL and administrators to see all records

Thanks Rich,

Will try your suggestions. I do have a session variable for Administrator. I just though there are some good videos maybe showing how to do some complex filtering of records.

[suresh]

Re: Filtering data in a grid based UserID and multiple fields in a MSSQL and administrators to see all records

Hi David,

I have the similar scenario, if you can share your solution on solving this would be much helpful.

thanks

[Steve Workings]

Re: Filtering data in a grid based UserID and multiple fields in a MSSQL and administrators to see all records

Here's one way that I use a lot:

1. Establish a session variable that you can use to make the decision you need. One that I keep is session.__UserGroup.

2. You can use the grid's OnGridExecute event to change the grid's filter by changing the e.tmpl.CS.SQL. In general, let's suppose you have a grid for non-administrators that has a filter for MyTable. In the event code you do this or whatever Select statement is appropriate:

if "Administrator" $ session.__UserGroup
	e.tmpl.CS.SQL = <<%a%
	SELECT * from myTable order by MySortField
	%a% 
end if

[RichCPT]

Re: Filtering data in a grid based UserID and multiple fields in a MSSQL and administrators to see all records

I have had difficulty modifying the SQL like that. If there is a Search part in the grid then one must be careful to include the other criteria the system administrator wants to filter upon. Also, I'm not sure OnGridExecute is the server-side event I'd modify. I'd probably give "onSQLSelectQuery" and "onSQLCountQuery" a try.

What I'd really like to see from Alpha here is the query statement broken apart into separate strings for the SELECT fields, JOINs, WHERE, SORT, HAVING, etc. so it would be so much easier to append a filter expression. Or, maybe even an XML representation that can be manipulated. I think I have a code somewhere in one of my grids that has to search for the WHERE clause and carefully insert the additional condition in the correct spot.