Todd Renier
going with the flow
Posts:34
 |
| 09-22-2009 12:45 PM |
|
I would like to bind the selected value to use in the where clause of the second field. In summary, I am displaying the email address based on the selection of the user from the first combo box. |
|
|
|
|
David To
river guide
Posts:2719
|
| 09-22-2009 01:58 PM |
|
Hi, you can use SQL question event default option.
1) combobox is the user picked
2) create a textbox for emailaddress
3) create a question event and choose option to "Set question default based on another questions response"
Your Initiation / Postback Question would be the combobox
Your Affected question would be the emailaddress textbox
4) your SQL would be: select email from users where userid = '$(UserID)'
where UserID is the shortname of your combobox. Try this out and see if it works for you. Any problems, feel free to reply. -- David |
|
|
|
|
Todd Renier
going with the flow
Posts:34
|
| 09-22-2009 02:34 PM |
|
Thanks David..
I setup the question event as you indicated with the initially affected question the combo box . When I select the drop down based on the sql below, the drop down goes back to the first row from the results and does not populate the email address in the second text box.
Combo Box SQL:
SELECT DISTINCT(UR.UserID) as OptionValue,U.DisplayName as QuestionOption, U.FirstName, U.LastName, R.RoleName, ExpiryDate
FROM USERROLES UR
INNER JOIN USERS U ON UR.UserID = U.UserID
INNER JOIN ROLES R ON UR.RoleID = R.RoleID
WHERE R.RoleName = 'PD - Police Supervisors'
Order by U.DisplayName
SQL from email address field
SELECT email FROM Users
WHERE displayname = $(SupervisorApproval)
|
|
|
|
|
Todd Renier
going with the flow
Posts:34
|
| 09-22-2009 03:58 PM |
|
Okay... if I supply a static value in the sql statement in the question event, then it populates the email address. So the only thing that is not working is the binding on the combo box to get the value
Here is my sql for the question event to get the value
Select email as DefaultValue from users
WHERE displayname = '$(Approval)'
If I supply a static value in place of '$(Approval)', it works and populates the field. So the issue is with the Combo Box..
Todd |
|
|
|
|
David To
river guide
Posts:2719
|
| 09-23-2009 06:37 AM |
|
Hi, I believe your SQL statement for combobox $(SupervisorApproval) is incorrect. You can only return questionoption and questionoptionvalue for combobox, not anything else. Also, the questionoptionvalue is the one that is being stored and compared to. Try something like:
select distinct(UR.UserID) as questionoption, u.displayname as questionoptionvalue from FROM USERROLES UR
INNER JOIN USERS U ON UR.UserID = U.UserID
INNER JOIN ROLES R ON UR.RoleID = R.RoleID
WHERE R.RoleName = 'PD - Police Supervisors'
Order by U.DisplayName
Now, in your question event, use this:
select email as defaultvalue from users where displayname = $(SupervisorApproval)
-- See if this works out for you and if not, please reply. -- David
|
|
|
|
|
Todd Renier
going with the flow
Posts:34
|
| 09-23-2009 07:53 AM |
|
Dave,
Thanks for your help. When I use this SQL, the combo box populates the box with the question option. Here is the sql I am using
select UR.UserID as questionoption ,u.displayname as questionoptionvalue FROM USERROLES UR INNER JOIN USERS U ON UR.UserID = U.UserID INNER JOIN ROLES R ON UR.RoleID = R.RoleID WHERE R.RoleName = 'PD - Police Supervisors'
Order by u.displayname
Todd
|
|
|
|
|
David To
river guide
Posts:2719
|
| 09-23-2009 08:06 AM |
|
HI, glad you got it working. -- David |
|
|
|
|
Todd Renier
going with the flow
Posts:34
|
|
Todd Renier
going with the flow
Posts:34
|
| 09-23-2009 08:17 AM |
|
Any help of getting this resolved is appreciated. I need this working for a process that goes live tomorrow.
Kind Regards,
Todd
|
|
|
|
|
Todd Renier
going with the flow
Posts:34
|
| 09-23-2009 08:22 AM |
|
By the way, we are on DNN 4.90 at this time. I planned on upgrading soon. I did not know if this had anything to do with the problem.
Todd |
|
|
|
|
David To
river guide
Posts:2719
|
| 09-23-2009 08:48 AM |
|
Is this a site I can get host access to so I can take a look at the SQL statements, etc? If so, I can shoot you an email and you can send me the login info. -- David |
|
|
|
|
Todd Renier
going with the flow
Posts:34
|
| 09-23-2009 10:29 AM |
|
This is behind a firewall but if you have remote access software, I can have you remote into my computer.
Any other ideas?
Thanks for your quick response!! |
|
|
|
|
David To
river guide
Posts:2719
|
| 09-23-2009 10:37 AM |
|
Quick question. What version of Dynamic Forms are you running? -- David |
|
|
|
|
Todd Renier
going with the flow
Posts:34
|
| 09-23-2009 10:53 AM |
|
I just uploaded the update from the beginning of Sept. Sept. 9 I believe |
|
|
|
|
David To
river guide
Posts:2719
|
| 09-24-2009 07:08 AM |
|
HI, I've been emailing Todd offline and have resolved his issue. He needed to modify his question event "FillEmailAddress", make the EmailAddress not read only and change his SQL query for SupervisorApproval. -- David |
|
|
|
|