Jeff Clayton
wading in the water
Posts:27
 |
| 06-18-2009 05:39 AM |
|
An example (but not the real scenario) would be…
Let’s say I have a form to enter new employees. I’m a division manager overseeing 3 of the 20 departments in an organization. When I add the employee, I should be able to select which department they belong to. My list should be limited to only my 3 departments based on my division. In contrast, when Human Resources goes to add/edit an employee, they should be able to select from all 20 departments.
So my thought was that if I can set a “division” session variable prior to loading the page with the Dynamic Form, I could use dynamic SQL “…WHERE DivisionID=$(division)” to limit division managers to a subset of the Departments within the combo box list values. I would have a different WHERE clause to accommodate H.R. nulls where a null value could see all departments, but that’s the gist of what I’m trying to do.
I’ve tried SQL using a session variable surrounded by $() but have had no luck so I don’t think it’s an option. I’m looking for another way to achieve the same results. As long as it will get me a drop down list that is dependent on a value passed into the form as a variable, but isn’t specified by the form user and cannot be extracted via a DNN token like PortalID or UserID.
“Division” is one example but it could be anything. Let’s say you wanted a user’s combo box list values to be limited based on their subscription level. You can easily set a session variable to identify their subscription level, but limiting the list values is a challenge.
I was thinking of maybe linking hidden fields to question events and following that up with some JavaScript (not my specialty) might do the trick, maybe?
I have tried sending a value to a hidden field and then using the hidden fieldname surrounded by $() in the SQL statement with no luck. Its seems like the list values are already created before the session variable is passed to the hidden field.
Any solutions? |
|
|
|
|
David To
river guide
Posts:2719
|
| 06-18-2009 07:04 AM |
|
Well, if you have the DivisionTable as an SQL table, and you also can obtain the $(UserID) token value based on the user logged in. How about roles, are there roles set up (manager / human resource roles in DNN) which users are assigned to? Then in your combobox field, you can "exec usp_filterdepartment $(UserID)" statement calling a stored procedure usp_filterdepartment. In the stored procedure, you can do something like:
create procedure usp_filterdepartment(@UserID int)
as
begin
declare rolemanagerID int
declare rolehumanresourceID int
set rolemanagerID = select roleid from roles where rolename = 'Manager'
set rolehumanresourceID = select roleid from roles where rolename = 'Human Resource'
declare manager int
declare humanresource int
set manager = (select count(*) from userroles where userid = @UserID and roleid roleid = rolemanagerID)
set humanresource = (select count(*) from userroles where userid = @UserID and roleid = rolehumanresourceID)
if (manager > 0) then
select Division as question, Division as questionoptionvalue from DivisionTable where Division in (department1,department2,department3)
if (humanresouce > 0) then
select Division as question, Division as questionoptionvalue from DivsionTable
end
-- David |
|
|
|
|
Adam
river guide
Posts:116
|
| 05-25-2010 01:36 AM |
|
I'm trying to do the same thing, I want to filter a dropdown based on the answer to another question, on a previous form or not. Tried using session vars etc with out any joy.
Is there any plans to make this option avaliable? It would also make sense that a Dynamic view could be filtered in the custom SQL using a session var.
Thoughts please? |
|
|
|
|
David To
river guide
Posts:2719
|
| 05-25-2010 02:59 PM |
|
HI, if you are using session variables, try naming the shortname of those field variables as DSSession1,DSSession2,DSSession3 whereas for querystring, they would be DSParam1,DSParam2,DSParam3. -- David |
|
|
|
|
Adam
river guide
Posts:116
|
| 05-25-2010 11:57 PM |
|
I'm impressed :o) Works like a charm, is this documented in the user manual? Thanks for the great support. |
|
|
|
|
Adam
river guide
Posts:116
|
| 05-26-2010 12:01 AM |
|
Unfortunatley this doesn't work in the Dyanmic Views SQL :o(
Query Details:SELECT [UserID] ,[ACName] ,[DisplayName] ,[FirstName] ,[LastName] ,[Username] FROM [i2iPortals].[dbo].[ai2i_GroupMembership] WHERE [MemID] = 1 AND [UserID] <> 1 AND [Username] = '$(DSParam1)'
Sort Field:UserID
Sort Order:ASC
|
|
|
|
|
Chad Nash
Posts:5260
|
| 05-26-2010 03:32 AM |
|
Adam,
I think we just added this into the most recent patch of Dynamic Views... If not I can duplicate it and check because it should be. What specific version are you on? It should be 01.00.50.
Thanks,
Chad
|
|
|
|
|
Adam
river guide
Posts:116
|
| 05-26-2010 03:38 AM |
|
Thanks for the quick response, I'm on version 1.0.3.29495 / 5.3.1 so I think I need to get the latest download, is it avaliable in snowcovered? |
|
|
|
|
Candace
river guide
Posts:2431
|
| 05-26-2010 12:08 PM |
|
Hi Adam,
Yes, this is available in Patches and Hotfixes or, if you have the Collection 3.0, you can get it from MyDownloads. Thanks!
Candace |
|
|
|
|