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.