Displaying fields in a form

[tdecoste]

I have a form that lists various departments. You can select multiple departments hit update and it updates a particular individual to have authorization over those departments. This all works fine! My issue is, when listing the departments in this form, how do I show which departments the user alreday has authorization over. That is how do I display them in the list as already selected.

[DCwebGuy]

Under your <cfoutput query="rsDepartments"> line you would do a cfloop query on the "employee" table comparing #rsDepartments.DeptID# to #employee.auth#. If the deptID's match then you add the "selected" parameter to the option tag. This will highlight, or select, that dept in the select list for that employee. Hope this helps.

[tdecoste]

I cannot seem to get this loop correct. I have tried different variation with no success.

[Newsgroup_User]

tdecoste wrote:
> <cfloop query="rsEmp" condition="#rsDepartments.DeptID#" item="rsEmp.auth">

This line does not make sense. You are mixing three entirely separate
forms of the <cfloop...> tag. The query form, the condition (while)
form and the collection (item) form. Which one do you mean to use?

[tdecoste]

It doesn't make sense to me either, hence my reason for asking for help. I am just trying to get the list to show what the user is already authorized over. The first code listing works for listing all the different categories and updates perfectly, but I am not able to see what user in question already has authority over. The second bit of code is obviously generating errors. I tried to employ the advice from an earlier response and made a mess of it...

[Newsgroup_User · Accepted solution]

tdecoste wrote:
> It doesn't make sense to me either, hence my reason for asking for help. I am
> just trying to get the list to show what the user is already authorized over.
> The first code listing works for listing all the different categories and
> updates perfectly, but I am not able to see what user in question already has
> authority over. The second bit of code is obviously generating errors. I
> tried to employ the advice from an earlier response and made a mess of it...
>

You have some unpleasant code there. First and for most it looks like
you have a de-normalized database design. By looking at the UPDATE SQL
statement you have 'SET auth = '#FROM.deptid#' with 'deptid' being a
comma list from the multiple select control of your form. This is going
to cause you problems in the future. You would be well served by
getting some understanding of 'relationships' in relational database
design and how this kind of data would be normalized.

But to your present requirement. The basic process is you have a set of
data of what departments are currently authorized and you want to
highlight those in your select control. One easy way, of many, to do
this is to compare each <option...> control to the list of currently
assigned values and if there is a match add the select="selected"
parameter to the <option...> control.

<cfquery name="authValues"...>
SELECT auth
FROM employee
WHERE empID = <cfqueryparam value="#form.empID#"
cfsqltype="cf_sql_integer">
</cfquery>

...

<cfoutput query="rsDepartments">
<option value="#rsDepartments.DeptID#"
<cfif ListContains(authValues.auth,rsDepartments.DeptID)>
selected="selected"
</cfif>>
#rsDepartments.Dept#
</option>
</cfoutput>

OR my preferred method, but some do not like the IIF() function:

<cfoutput query="rsDepartments">
<option value="#rsDepartments.DeptID#"
#IIF(listContains(authValues.auth,rsDepartments.DeptID,DE('
selected="selected"'),DE(''))#>#rsDepartments.Dept#</option>
</cfoutput>

Of course some of this logic would be slightly modified if you properly
normalized your database design to NOT store a list of values in a
single employee auth field.

[Dan_Bracuk]

Another way is to use cfform/cfselect which has a selected attribute.

[tdecoste]

I agree with your comments "unpleasant code" and regarding normalization. However, you were a tremendous help. Thank you.