Populating drop down menu with iniitials if checkbox is checked in database

Report · Feb 09, 2011

I am trying to populate a drop down menu with a user's initials that I have in my database, but it has to depend if the "Received_By_Initials" column is checked. I tried putting an If statement in the loop query on the drop down, but it doesn't seem to work. This code I'm using is on an edit page, so that's why I have the selected code on here too because I would like to have the initials display in the drop down if they are already chosen from before. The reason I want to do this is because I have a lot of different drop downs I'm going to use these initials for and I don't want to create multiple tables for Received By, Assigned By, RFQ Leader, etc. Does anyone know how to fix this so this will work? Here's the code:

<CFQUERY NAME="ShowReceivedByInitials" Datasource="#application.DataSource#">
SELECT Initials, Received_By_Initials
From Users
Order by Initials
</CFQUERY>

<select name="Initials">
<option value="">Select Initials</option>
<cfloop query="ShowReceivedByInitials">
<option value="#Initials#"
<cfif #ShowReceivedByInitials.Received_By_Initials# EQ "1">selected</cfif>>#Initials#
</cfloop>
</select>

Thanks.

Andy

Report · Feb 09, 2011

<cfoutput query="ShowReceivedByInitials">
    <cfif ShowReceivedByInitials.Received_By_Initials EQ 1>
    <option value="#ShowReceivedByInitials.Initials#">#ShowReceivedByInitials.Initials#</option>
    </cfif>
</cfoutput>

However, you should realize your design could be troublesome. People could share the same initials.

Report · Feb 09, 2011

Thank you. I had to change a few things in the code to get it to work, but I still don't know how to get the drop down to have the correct initials be selected when the page loads. Here's how the code is working with just getting the initials to be populated correctly. For the other response I received, what I'm talking about with something being checked is I have a Users table with the user's name, iniitials, etc. There are also columns that are check boxes for if the user belongs in certain groups for certain drop down menus or not such as Received By, Assigned By, or RFQ Leader.

<select name="Initials">
<cfloop query="ShowReceivedByInitials">
<cfif ShowReceivedByInitials.Received_By_Initials EQ 1>
<option value="#ShowReceivedByInitials.Initials#">#ShowReceivedByInitials.Initials# </option>
</cfif>#Initials#
</cfloop>
</select>

How do I fix this now so that the correct initials are selected and displayed in the drop down when the page loads? I tried this, but it doesn't select the correct initials:

<select name="Initials">
<cfloop query="ShowReceivedByInitials">
<cfif ShowReceivedByInitials.Received_By_Initials EQ 1>
<option value="#Initials#"></cfif><cfif #ShowReceivedByInitials.Initials# EQ #ShowItem.Received_By#>selected</cfif>#Initials#</option>
</cfloop>
</select>

Thanks.

Andy

Report · Feb 09, 2011

I usually do what you seem to be describing like this:

cfquery name = "bigquery"

select all possible records

cfquery name = "littlequery"

select just the record I want

<cfselect

query="bigquery"

value="something from bigquery"

display="something else from bigquery"

selected = "something from littlequery">

Which leads to, what is ShowItem.Received_By?

Report · Feb 09, 2011

Oh yes. Sorry about that. The ShowItem.Received_By is from this query:

<CFQUERY NAME="ShowItem" Datasource="#application.DataSource#">
SELECT Company, Company_Name, First_Name, Middle, Last_Name,
ContactID, Contact_Num, ItemID, RFQID, RFQID_SPEC, M2M_Quote_Number, Rev, Standard_Part_Number, Generic_Description, GHz_Socket, Received_By, Assigned_By, RFQ_Leader, Jsquad_Coordinator, Entry_Date, New_Custom_PO_Received_Date, Complete_Date, Highest_Quantity_Request, Sales_Order_Number, Order_Request, Customer_Reason_Rejecting, Budgetary_Quote, RFQ_Expected_Value, Possible_Status, Sales_Status, Additional_Comments, End_Customer_Name, Follow_UpID, Follow_Up_ItemID

FROM
((((Contacts LEFT JOIN RFQ_Numbers ON Contacts.ContactID = RFQ_Numbers.Contact_Num)
LEFT JOIN Companies ON Contacts.Company = Companies.Company_Name)
LEFT JOIN RFQ_SPEC ON RFQ_Numbers.RFQID = RFQ_SPEC.RFQID_SPEC)
LEFT JOIN Follow_Up_Notes ON RFQ_SPEC.ItemID = Follow_Up_Notes.Follow_Up_ItemID)

Where ItemID=#url.ItemID#

ORDER BY ItemID
</CFQUERY>

This query relates all of my tables together. I have a table called RFQ_SPEC with all the part info. and user's initials in this table. The User's table field Initials equals the Received_By field in the RFQ_SPEC table.

Andy

Report · Feb 09, 2011

Dan,

I don't understand how to do the query you said to do. I am able to get the correct initials selected, but then the list of all the other initials do not display in the drop down menu. I want them to display in case someone needs to update the initials to someone else. Here's what I have:

<select name="Initials">
<cfloop query="ShowReceivedByInitials">
<cfif #ShowReceivedByInitials.Initials# EQ #ShowItem.Received_By# and ShowReceivedByInitials.Received_By_Initials EQ 1>
<option value="#ShowReceivedByInitials.Initials#">#ShowReceivedByInitials.Initials# </option>
</cfif>
</cfloop>
</select>

Andy

Report · Feb 09, 2011

I was finally able to figure out how to have the user's initials selected and having the drop down menu only display the initials that I have checked off depending on what group they belong to. Here's the correct code below. Thanks everyone for your help.

<select name="Initials">
<cfloop query="ShowReceivedByInitials">
<cfif ShowReceivedByInitials.Received_By_Initials EQ 1>
<option value="#ShowReceivedByInitials.Initials#"
<cfif #ShowReceivedByInitials.Initials# EQ #ShowItem.Received_By#>selected</cfif>>#ShowReceivedByInitials.Initials#</option>
</cfif>
</cfloop>
</select>

Andy

Report · Feb 09, 2011

What you appear to have so far is that the last record where the user's initials are "1" will cause the item to be selected. That's probably not what you want.

Something that is not clear to me is the concept of something being checked in a database.