Solved

Select Query in Update form, current value selected

Posted on 2013-01-29
10
217 Views
Last Modified: 2014-03-03
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("&nbsp;", 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("&nbsp;", 5)#- #Facility#</option>
</cfoutput>
</cfoutput>
</cfif>
</CFSELECT>
0
Comment
Question by:Lee R Liddick Jr
  • 6
  • 3
10 Comments
 
LVL 15

Expert Comment

by:Gurpreet Singh Randhawa
ID: 38833867
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

0
 

Author Comment

by:Lee R Liddick Jr
ID: 38835103
I will try this when I get back to the office but I'm not seeing how this is going to do anything?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38835179
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?
0
 

Author Comment

by:Lee R Liddick Jr
ID: 38835253
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...
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38835634
(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>
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Lee R Liddick Jr
ID: 38835684
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!)
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 38836237
> <option value="#FacilityID#" #selectedValue">

Oops, small typo. Obviously add the closing # sign after selectedValue.

Btw, you could actually do option 2 in a single query using OUTER joins too.  The logic would be in the SQL rather than CF code.  The general idea is to return all facility records, but use the OUTER join to "flag" ones matching the current user.  Conceptually it is would be like this (just pretend for the sake of the example there's only two tables):

               SELECT  f.FacilityName
                              , f.Owner
                              , CASE WHEN u.UserID IS NULL THEN 0 ELSE 1 END AS IsAssignedToUser
               FROM    Facility f LEFT JOIN UserFacility u
                                          ON    u.FacilityID = f.FacilityID
                                          AND  u.UserID = <cfqueryparam value="#currentUserID#" ...>

So say this was the table data:

            FacilityID | FacilityName  | Owner
            2   | Facility A          | Owner 11
            3   | Facility B          | Owner 22
            4   | Facility C          | Owner 33
            5   | Facility D          | Owner 44

            FacilityID | UserID
            2   | 35        <=== ie "John"
            4   | 35       <=== ie "John"

The results of the JOIN would be:

            FacilityName | Owner | IsAssignedToUser
            1| Facility A          | Owner 11  | 0
            2| Facility B          | Owner 22  | 1                 <=== true
            3| Facility C          | Owner 33  | 0
            4| Facility D          | Owner 44  | 1                 <=== true

Then you could use the "IsAssignedToUser" value like a boolean flag to determine which options should be selected:

   <cfoutput query="qryFacilities" group="Owner">
       <option value="#OwnerID#" <cfif IsAssignedToUser>selected</cfif>>....</option>
       <cfoutput>
              <option value="#FacilityID#" <cfif IsAssignedToUser>selected</cfif>>...</option>
       </cfoutput>        
 </cfoutput>

The original approach w/listFind does the same thing.  It's just a matter of where you want to put the logic.

(BTW, I'm glad to hear you found another opportunity - and it sounds like an interesting one!  )
0
 

Author Comment

by:Lee R Liddick Jr
ID: 38853744
I'm coming back to this in the next few days.  I got delayed.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 39263466
I'm still working on this.  Still delayed.
0
 

Author Closing Comment

by:Lee R Liddick Jr
ID: 39900287
Did finally get this to work with your example and explanation...thank you!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
cfchart issue with html 6 106
expandPath erroring 15 57
How to resolve "Resouce Busy" error generated by Coldfunsion function 20 71
CFFILE upload help 98 138
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now