For a new registration we have a radio button question with text/values for each of the available membership levels. On a successful registration we fire some SQL scripts to add the user to the appropriate role.

Using the same form for renewal how can we default the answer to the one matching the expiring role?

Any ideas?

TIA

Antony

First of all, when you're adding the user to the appropriate role, are you also adding an expiration date or is it left blank?
If you are using the same form (moduleID), and fields (radio button), then you might consider changing your SQL script to a store procedure and on renewal, grab all the roles based on expirydate compared to the current getdate(). -- David

For the roles in question we do set an expiry date.

However, we are using a different form for renewal (a copy of the orginal).

Have you got any examples of the SQL to set the default answer for the question?  Bearing in mind the user could be a member of other roles not related to thier annual 'club' membership

Antony

Will there be only one expiring role (annual club membership) or multiple expiring roles (differing annual club membership) roles?

If there's only one, then you might consider using a textbox. If multiple, then combobox. Anyhow, the SQL statement might be something similar to this:
select roles.rolename as questionoption, roles.roleid as questionoptionvalue from roles inner join userroles on roles.roleid = userroles.roleid where userroles.expirydate <= getdate()

You may need to tweak the SQL a little bit if this doesn't exactly work out but it gives you a basic idea. -- David

David,

What I am trying to do is show all the available membership levels at time of renewal, and default the answer to the expiring membership level of the user.

The user will only be a member of one of the available roles.

By my reading your suggestion would just put all the roles the current user is a member of.

Can you use 'as questionoptionselected' to set which of the values is selected?

TIA

Antony

Just looking at the configuration options and where would you put the SQL query?

To set the default value using SQL I thought you could only return 1 field which had to be called 'DefaultValue' and you can't pass a variable (eg $UserID).

Antony

Well, I know you can set a default value on combobox but the entries would have to be something you enter hard-coded. I don't believe there is a way to set a default value when your entries are pulled from an SQL statement since this gets pulled on page load from reading the SQL table so there's no way to know which values are available to choose as a default value before hand under "Advanced Field Options".

What you can do is have a combobox that shows all the available membership levels and a separate textbox field that pulls the default expiring membership role of the user using an SQL statement.

Also add in client side javascript on the combobox to set: $(textbox) = $(combobox). That way, if a user picks something else on the combobox, it will also appear on the textbox and overwrite the default there.

Then use the textbox as the user choice for renewal. -- David

David

I am trying your suggestion.  I have created a TextBox field and am trying to set the default of this using the following SQL Query:

SELECT TOP (1) Roles.RoleName AS DefaultValue FROM Roles INNER JOIN UserRoles ON Roles.RoleID = UserRoles.RoleID INNER JOIN Users ON UserRoles.UserID = Users.UserID WHERE (Users.UserID = $(UsersID)) AND ((Roles.RoleID = 29) OR (Roles.RoleID = 30) OR (Roles.RoleID = 31) OR (Roles.RoleID = 33) OR (Roles.RoleID = 32))ORDER BY UserRoles.ExpiryDate DESC

However, whenever I 'update' the question it just reloads the page displaying the question options (instead of just displaying the dropdown box to select the question you want to change) AND the "question" field (above the short name) is cleared of the text that was there.

If I replace the above SQL statement with the example one (Select count(*) As DefaultValue from users) under the SQL box everything saves OK but when you look at the field as a normal user nothing is displayed.

I am using DynReg 3.2.7 on DNN 4.9.4

Any ideas on how to get the SQL to work?

TIA

Antony

Perhaps problem is that the SQL statement can only be 200 characters max. Yours is 350 which is why it's not saving. You will need to create a stored procedure and call the stored procedure instead:

create procedure usp_GetDefaultRole (@UserID int)
as begin
SELECT TOP (1) Roles.RoleName AS DefaultValue FROM Roles INNER JOIN UserRoles ON Roles.RoleID = UserRoles.RoleID INNER JOIN Users ON UserRoles.UserID = Users.UserID WHERE Users.UserID = @UserID AND Roles.RoleID in (29,30,31,33,32)
order by Roles.ExpiryDate DESC
end

in the HOST / SQL (be sure to click on execute as script).
In your question field, call the stored procedure as:
exec usp_GetDefaultRole $(UserID)