I have a checkbox dynamic field that is defaulted to checked and this field is tied to a completion event that adds the user to a role. 

I would like that if the user unchecks the box that they would be added to a different role I specify. I have another completion event tied to this same dynamic field but speficifies that the box is unchecked and when this is the case the user is added to another role.

In testing, when the box is checked, the user gets added to the proper role as they should.  When unchecked, the user still gets added to the role asociated with the checked box completion event.

Is there another way to do this or should this be working?

Thanks,

Paul

Hi Chad,

Yes, the user gets added to the security role regardless of their choice related to the completion event.  I am sure I have the configuration right.  Not sure what is going on.

A couple of things I thought of and tried but not sure if they're related:

1) I have the opt-in email module installed with a completion event setup on the registration page that subscribes them to our school newsletter.  Is there somewhere in the opt-in email module configuration that I could have tied this completion event to a security role?  It doesn't seem the case as I tested the registration and unchecked both completion events and the user still got added to the security role.

2) In dynamic registration's module configuration and more specfically the opt-in email settings, are the settings that you choose supposed to remain there after you update the opt-in dynamic field and update settings or do they get cleared?  Everytime I return to the settings they are back to the default.

Any guidance appreciated. I will continue to test.

Thanks,

Paul

I have reduced the problem of the user getting added to the security role no matter what their response was to a corrupted security role.  I deleted the role and recreated it in dnn and now when the user checks the box, they get added properly.  If the user doesn't check the box, they are not added to the role although I do have a completion event that says if the user does not check the box, add them to another role that I have specified.  This part is still not working and I need some help if I'm going to need an SQL completion event to accomplish what I'm after which is if the user checks the box add to role A, if the user doesn't check the box add them to role B.

More specifically, I need an example of the code (stored proc) I would need to accomplish this.

Thanks,

Paul

Paul,

Hi. Ok, I see what you are saying... I actually think that you should be able to create a role completion event for both checked/not checked and add the user to seperate security roles but ill review this. In the mean time, I felt that we need a good thread related to a stored procedure which adds the user to a security role anyway so I decided to create one. This particular example is a stored procedure where you will pass it a PortalID, the UserID, the Role Name (so you don't have to have the RoleID), and the results from a checkbox. You could easily change this if you review the stored procedure (for example to pass it a value such as a combo box where the response is a specific response etc...)

So... Please follow these steps:

Step 1: Go to Host, SQL and execute this query... Go ahead and check the box that this is a SQL query.

Create Procedure {databaseOwner}[{objectQualifier}DataSprings_AddToSecurityRoleIfNotChecked]
@PortalID as Integer,
@UserID as Integer,
@RoleName as nvarchar(200),
@IsChecked as varchar(10)

AS


DECLARE @RoleID as integer

IF @IsChecked = ''
BEGIN
      Set @RoleID = (Select RoleID from {databaseOwner}[{objectQualifier}Roles] Where RoleName = @RoleName AND PortalID = @PortalID)
      Insert Into {databaseOwner}[{objectQualifier}UserRoles](UserID, RoleID, ExpiryDate, IsTrialUsed, EffectiveDate)
      Values(@UserID, @RoleID, Null, Null, Null)
END

The stored procedure has been created. You can now reference this stored procedure, passing it the portalID, UserID, role name, and value of the checkbox. Keep in mind that the value of the checkbox is simply blank (or '') if its not checked and if its checked its 'Yes'. So within the stored procedure you could change the @isChecked = '' to be @IsChecked = 'Yes' if you wanted them to be added to the security role if they checked the box. In this case though you mentioned you want them to be added if they do not check the box, so I left it at ''

Step 2: Create a SQL Event. Technically you could set the even to be fired for Any Response as you are going to pass it the value of the checkbox and if the user checked it as 'Yes' then the stored procedure is not going to do anything.
So... Lets assume that my portalID is 0 and my role name is 'Test Role One'. Lets also assume that the short field name for my checkbox is 'MyCheckBox'. If this was the case the SQL Event should look like this:

DataSprings_AddToSecurityRoleIfNotChecked 0, $(UserID), 'Test Role One', '$(MyCheckBox)'

So could technically test this without the SQL event as well if you went to Host SQL and entered something like:


DataSprings_AddToSecurityRoleIfNotChecked 0, 55, 'Test Role One', ''

The above SQL should add the user with userID 55 to the role Test Role One because it was passed ''


Or


DataSprings_AddToSecurityRoleIfNotChecked 0, 55, 'Test Role One', 'Yes'

The above SQL should not add the user with userID 55  to the role Test Role One because it was passed 'Yes'


Step 3: Test!

Ummmmmm... So you can use this stored procedure or tweak it for other users.  It wouldn't be hard to change the SQL within the stored procedure to also delete from a security role etc... You could also change the SQL to be if an if / else statement where it would maybe add them to a role if it was '' or delete them from a role if it was 'Yes' etc...


-Chad

Oh man...that worked like a charm!  I'm all giddy now:-)  I set the SQL completion event to any field response and set my DNN Role completion event to the checkbox field with the user response being a check.  Now when the user checks the box, they are added to the role called Parents and when they don't check the box, they are added to a role called Parents ND (No Directory).  Now I can integrate the User Directory module and only show those parents who chose to be included in the directory!

When I tried to accomplish this with 2 role completion events mapped to the same dynamic field, one with the box checked, the other response not checked, I never could get this result so I'm not sure what the problem was although it seems like that is the more intuitive way and should work.

Dynamic Registration, Opt-in Email, and the User Directory modules are 3 great products and with a little work on the configuration side, the three together have brought great value to my portal.

Great modules and great support.  Thanks for all your help.

Paul

I think I feature request would solve this without custom sql things, with "parent question answer condition rule”.

If answer in question 1 is xxxx then set this answer to yyyy

And this could be extended to a checkbox question like, if this question answer is “checked” then “uncheck” this question answer.

With this function the role add/remove role event could be set based on hidden answers where a checkbox is on/off in parent conditions.

"Answer condition rule" feature would be so nice...