asked on

Select Query in Update form, current value selected

I have a query that gathers a bunch of facilities and the owner of those facilities. The query is grouped by the owner. All owners and facilities are selectable. Users in the application are assigned to one of these owners or facilities. My problem is, I am creating an edit form for the user and I can't get the cfselect to populate the current owner/facility the particular user is assigned.

This is my facility query:
Note: All Owners and Facilities are in one table, the _afl table is the affiliation table that shows a faclity affiliated to an owner.
<CFQUERY NAME="qryFacilities" DATASOURCE="myDS" USERNAME="user" PASSWORD="pass">
select f.hcf_id as FacilityID, f.hcf_nm as Facility, o.hcf_id as OwnerID, o.hcf_nm as Owner
from rfd_hcf_afl a
,RFD_HCF o
,RFD_HCF f
where a.afl_to_hcf_id = o.hcf_id
and a.afl_frm_hcf_id = f.hcf_id
order by Owner
</CFQUERY>

This is my query in my form:

<CFSELECT NAME="edtFacID" CLASS="form_combo_normal" STYLE="width:184px;">
<cfif #qryFacilities.FacilityID# EQ #qryUsers.hcf_id#>
<cfoutput query="qryFacilities" group="Owner">
<option value="#OwnerID#" style="font-weight:bold; color:##34495F;" SELECTED >#Owner#</option>
<cfoutput>
<option value="#FacilityID#" SELECTED >#repeatString(" ", 5)#- #Facility#</option>
</cfoutput>
</cfoutput>
<cfelse>
<cfoutput query="qryFacilities" group="Owner">
<option value="#OwnerID#" style="font-weight:bold; color:##34495F;" >#Owner#</option>
<cfoutput>
<option value="#FacilityID#" >#repeatString(" ", 5)#- #Facility#</option>
</cfoutput>
</cfoutput>
</cfif>
</CFSELECT>

Coast Line

do it like this :

<CFSELECT NAME="edtFacID" CLASS="form_combo_normal" STYLE="width:184px;">
<cfif #qryFacilities.FacilityID# EQ #qryUsers.hcf_id#>
  <cfoutput query="qryFacilities" group="Owner">
      <option value="#OwnerID#" <cfif qryFacilities.ownerID EQ yourmainqueryatTop.ownerID>selected</cfif> style="font-weight:bold; color:##34495F;">#Owner#</option>
      <option value="#FacilityID#" <cfif qryFacilities.FacilityID EQ yourmainqueryatTop.FacilityID>selected</cfif>>#repeatString("&nbsp;", 5)#- #Facility#</option>
  </cfoutput> 	
  <cfelse>
  <cfoutput query="qryFacilities" group="Owner">
    <option value="#OwnerID#" <cfif qryFacilities.ownerID EQ yourmainqueryatTop.ownerID>selected</cfif>style="font-weight:bold; color:##34495F;" >#Owner#</option>
      <option value="#FacilityID#" <cfif qryFacilities.FacilityID EQ yourmainqueryatTop.FacilityID>selected</cfif>>#repeatString("&nbsp;", 5)#- #Facility#</option>
  </cfoutput>
</cfif>
</CFSELECT>

Open in new window

Lee R Liddick Jr

ASKER

I will try this when I get back to the office but I'm not seeing how this is going to do anything?

_agx_

Users in the application are assigned to one of these owners or facilities.
... get the cfselect to populate the current owner/facility the particular user is assigned.

Can you post the query showing how you determine the assigned owner/facility? Also, can the current user be assigned to ONLY one owner/facility?

Lee R Liddick Jr

ASKER

Hey agx...not in the office right now, but the user query just collects information from the user account table (login info), user resource table (contact info, email etc), and the facility affiliation table (with just user id and facility id). So basically something like this:
<cfquery name="qryUsers">
select a.usr_acct_id, r.rsc_lname, r.rsc_fname, f.hcf_id
from rfw_usr_account a
, rfs_resource r
, rfd_hcfc_affil f
where a.usr_acct_id = r.blindkey
and a.usr_acct_id = f.blindkey
</cfquery>

At this time, one user is assigned to just one facility, this can change in future, but for now, the hcfc_affil table is a 1-1 relationship with the usr_account.

I think CF10 would make this easier for me as it allows for loops with groups, but right now we are not on CF10. Plans for moving there in the near future, but not there yet.

Hope this helps and hop you are doing well...

_agx_

(Yeah, it's been a while. So far the new year is busy, but good. Hope things are good with you too :)

Hm.. I'm not sure if I'm on the right track, but here's 2 possibilities:

1) If you *only* want to return the facilities assigned to the current user then alter qryFacilities to join to the user info tables too. Then qryFacilities will only include those records.

2) If you're trying to display all facilities, but *pre-select* the ones assigned to the current user it'd be simpler to keep the 2 queries. Then put the matching hcf_id values into a list.

<cfset facilitiesForCurrentUser = valueList(qryUsers.hcf_id)>

Then as you output the query, use listFind to determine if the current factilityID is in the list. If it it's found, mark the option as selected. Then you can use one query for everything.

<cfoutput query="qryFacilities" group="Owner">
<cfset selectedValue = "">
<cfif listFind(facilitiesForCurrentUser, FacilityID)>
<cfset selectedValue = "selected">
</cfif>
....
<option value="#OwnerID#" #selectedValue#>#Owner#</option>
<cfoutput>
<option value="#FacilityID#" #selectedValue">...#Facility#</option>
</cfoutput>
.....
</cfoutput>

Lee R Liddick Jr

ASKER

Option 2 is what I'm looking for, with keeping my groups in the drop down. I will punch this in when I get back to my office.

(things going quite well so far...got laid off from AT&T beginning of last year and got involved in a SaaS endeavor...which is getting launched very soon...thanks again agx!)

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lee R Liddick Jr

ASKER

I'm coming back to this in the next few days. I got delayed.

Lee R Liddick Jr

ASKER

I'm still working on this. Still delayed.

Lee R Liddick Jr

ASKER

Did finally get this to work with your example and explanation...thank you!