CF - Display data based on field value

Question:
I am trying to get a value to display based on the value in another field.  For example if the supervisor ID is 21 I want the name from the #first_name# and #last_name# field to display - if the ID is 22 I want that name to display and so on.  As it is now it displays everyones name but does not reflect what the supervisor ID is.

Suggestion:

<cfset myval=#supervisor_ID#>

<Cfif myval = "expected value">
<cfoutput>#myval -  #first_name# and #last_name# </cfoutput>
</cfif>

Select allOpen in new window

The idea being you simply want to use an if then else statement to validate the supervisor id is what you expect and to display the data is that output row to match the supervisor id.

The above is not meant to be the final solution but to give you an idea of how to go about it.

Show us the query getEmployees it might be as simple as putting a Where statement in the quote for example

Where supervisor_ID = 21

if supervisor_ID is a text field

it Where supervisor_ID = '21'

Actually there are several codes that have supervisor name associated -
21
263
291
30
406
418
469
47
501
58

<cfquery name="getEmployees" datasource="#datasource#">
      SELECT *
      FROM employee_dataTest
    order by Last_Name
</cfquery>

If I am reading this correctly:
             <select name="SuperVisorID">
             <option value="#supervisor_ID#">#last_name#,#first_name#</option>
             </select>

That output will have values for everyone including supervisors.

You want <option value="#supervisor_ID#"> to be populated with the supervisor ID and it isn't being populated  ?

It is displaying the correct supervisor ID but like you said it is just displaying the employees name and not the supervisor name.  Where we have employee Jane Doe I want the supervisor name of John Doe to display in the supervisor field - the supervisor for Jane is ID 21 for example.

I am lost I do not understand what you are trying to do.

This query
<cfquery name="getEmployees" datasource="#datasource#">
      SELECT *
      FROM employee_dataTest
    order by Last_Name
</cfquery>

Does it have the regular employees and the supervisors?

Yes - all in one table

Ok now I get you.


So since the DB has the supervisor ID does it also map the supervisor ID to the supervisor name  ?

It sounds to me as though that is all you need to do is to map the supervisor ID to the correct name or simply display the First and last name of the supervisor if there is some other field that maps...
e.g.  i am assuming the supervisors are also employees in this table, so how do you identify a supervisor in this table  ?

You code is doing what it is supposed to by displaying the employee name - you now need to nest a query to get then display the supervisor details.

The supervisor ID is also the same as the employee ID  - For example employee ID 47 is also the supervisor of employee 404 - Employee ID 404's record has a supervisor ID field - the value of that field is 47

A supervisor is an employee and some are supervisors - when I tried two queries I ran into the trouble of a query within a query

If you are lucky and the supervisor id is the same as the supervisor's employeeid, then you are home free.

select * from employee_dataTest where employeeid = #supervisor_ID#

Try this:

<cfoutput query="getEmployees">
<cfform method="post" style="margin:0px" action="Admin.cfm">
	<cfinput type="hidden" name="ID" value="#ID#">
<tr>
<td><cfinput type="text" name="first_name" value="#first_name#" size="10"></td>
     	  <td><cfinput type="text" name="middle_name"  value="#middle_name#"   size="1"/></td>
          <td><cfinput type="text" name="last_name" value="#last_name#"   size="15"/></td>
          <td><cfinput type="text" name="main_dept"  value="#main_dept#"   size="2"/></td>
          <td><cfinput type="text" name="job_title"  value="#job_title#"   size="30"/></td>  
          <td><cfinput type="text" name="phone_ext"  value="#phone_ext#"   size="3"/></td> 
          <td><cfinput type="text" name="email_name"  value="#email_name#"   size="15"/></td> 
          <td><cfinput type="text" name="birth_month"  value="#birth_month#"   size="2"/></td> 
          <td><cfinput type="text" name="birth_date"  value="#birth_date#"   size="2"/></td> 
          <td><cfinput type="text" name="payroll_num"  value="#payroll_num#"   size="6"/></td> 
          <td>
<cfquery name="getsup" datasource="#datasource#">
      SELECT *
      FROM employee_dataTest 
	where employeeid = #supervisor_ID#
</cfquery>

       <CFLOOP QUERY="Getsup">
             <select name="SuperVisorID">
             <option value="supervisor_ID#">#Getsup.last_name#,#Getsup.first_name#</option>
             </select>
      </cfloop>  

             </td>		
           <td>
        <cfinput type="submit" name="editing"
			value="EDIT" style="font-size:xx-small"> 
       	  </td>
           <td>
		<cfinput type="submit" name="deleting"
			value="Inactivate" style="font-size:xx-small">
		</td>
		</tr>
</cfform>
</cfoutput>

Select allOpen in new window

Looks like some are not the same - i guess I will have to create a supervisor table with a relationship

Can you not just add a field call Supervisor and either put a Y or a N in it or a 1 or a 0? A separate table I would thing would be better.

Not sure I fully understand the form, but I'm almost certain you don't need another table or column.  To provide the correct answer I need to know:

1) Does your "getEmployees" contain multiple employee records OR just one?
2) Should <select name="SuperVisorID"> contain ALL employees OR just the current supervisor?

Hi agx,

getEmployees gets all the employees - <select name="SuperVisorID">  should display all the supervisors allowing one to be selected depending on who the new employee is going to be under.

getEmployees brings one record for each employee - I did not read the comment right

example.jpg

Let me back up since I think this is confusing.

Employee Jane Doe with and employee ID of 10

Employee John Doe with an ID and a Supervisor ID 0f 20

Employee Jane Doe has in her record a value of 20 in her Supervisor ID field

I want the name John Doe to appear on the screen when someone is looking at her record and not the number 20

Ok so what I indicated above should work easily if you can do a 1 - 1 mapping in your table for persons who are employees and are also supervisors.

You now can simply ensure:
1. A person's employee ID is the same as supervisor ID (For a supervisor that is)
2. Add a new column where if an employee is a supervisor (The ID assigned to him as a supervisor is in that column)

e.g.  John doe has emp ID 427  - He is a supervisor and his Supervisor ID is 17
The column should look like
Fname - Lname - Field1 - Field2 - EmployeeID - SupervisorID - IDassignedassupervisor ...

If you get the idea I am going with..
Once you do this your second query is easy it will simply be select * from employees where IDassignedassupervisor = #SupervisorID#

Then do as indicated in the cfloop above.

The only fields are ID and supervisor_ID

John Doe ID 427 has 17 in the supervisor_ID field

I want the name of his supervisor (who is another employee)  and not the number 17 to display on the screen - It's looking like I will have to make a supervisor table with the key field to get a name to show

Re-read what I wrote above, you dont need to create another table.


IF John Doe has a supervisor whose ID is 27 - What is the employee ID of that supervisor ?

27 is the employee ID of the supervisor

I try this

<cfquery name="getSupervisor" datasource="#datasource#">
      SELECT   *
FROM         employee_dataTest
WHERE     (leader_coach = 1) OR
                      (supervisor = 1) AND (active_account = 1) AND (ID = supervisor_ID)
</cfquery>

           <CFLOOP QUERY="getSupervisor">
             <select name="SuperVisorID">
             <option value="#supervisor_ID#">#getSupervisor.last_name#,#getSupervisor.first_name#</option>
             </select>
      </cfloop>

And I get what the image shows

Every supervisor shows for each employee

This seems to be due to your initial query.

 (supervisor = 1) AND (active_account = 1) AND (ID = supervisor_ID)

This is what I think you are trying to get:

Select * from employee_dataTest (Which brings back all employees)

Then

The subquery would look like this:

Select * from employee_dataTest where (some value that is unique to that supervisor and is mapped to supervisor id) = supervisor_ID

The idea here is
1. Your first query outputs all the employees
2. for each employee that is listed the second query finds the person who owns that supervisor ID.

Your only problem the one we have been trying to resolve is this:
Find a way to have the supervisor's record accessible by the supervisor id that is linked to each employee beneath him.

If Jane doe has a supervisor who is John Doe, then Jane Doe's record displays as below

Fname Lname  Value1 Value2 Supervisorid
Jane      Doe         val       val           17

We now in the second query find who is the person who is mapped to "17"

This is what you need to resolve now to get this working as expected,  I suggested a way to do this above.

This gives the same result

<cfquery name="getSupervisor" datasource="#datasource#">
      SELECT   *
FROM         employee_dataTest
WHERE     (leader_coach = 1) OR
                      (supervisor = 1) AND (active_account = 1) AND (IDassignedsupervisor = Supervisor_ID)
</cfquery>

 <CFLOOP QUERY="getSupervisor">
             <select name="SuperVisorID">
             <option value="#supervisor_ID#">#getSupervisor.last_name#,#getSupervisor.first_name#</option>
             </select>
      </cfloop>

Why not simply use
<cfquery name="getSupervisor" datasource="#datasource#">
      SELECT   *
FROM         employee_dataTest
WHERE (IDassignedsupervisor = Supervisor_ID)
</cfquery>

since there should be no duplicate entries for this value ?

It is still displaying like the image I uploaded - multiple supervisors for one record - makes no sense.

Try using:
WHERE (IDassignedsupervisor = #firstquery.Supervisor_ID#)

Let me play with it in a few and come up with something it seems tweaking the query and the loop are the only thing left here to do and you will have the result you need.

Still the same - I think I will just create another table to get it to display

Ok - sounds good - no great hurry yet

Just catching up.

I try this ... And I get what the image shows

It's only because your loop is in the wrong place.  It's generating 1 select list for each record in the query.  Move it inside the <select> tag.  Also, you *must* scope all of the query columns. Otherwise they may conflict with those in your outer cfoutput loop. Something like this

<select name="SuperVisorID">
     <cfloop query="getSupervisor">
             <option value="#getSupervisor.employee_ID#">
                        #getSupervisor.last_name#,#getSupervisor.first_name#
              </option>
     </cfloop>
</select>

> should display all the supervisors

So you want the <select> to list all *possible* supervisors. But pre-select the employee's current supervisor.  

I still don't think you need another table ... you just need the right query and CF code.

So what determines which records represent a supervisor? (The WHERE clause has a lot of filters like supervisor = 1, leader_coach = 1, etc...). I'm not sure which ones you really need.

The supervisor_ID field shows the ID number of the supervisor for the employee - for example

Employee Jane Doe with and employee ID of 10

Employee John Doe with an ID and a Supervisor ID 0f 20

Employee Jane Doe has in her record a value of 20 in her Supervisor ID field

I want the name John Doe to appear on the screen when someone is looking at her record and not the number 20

With the change I get the same supervisor for everyone.

 <select name="SuperVisorID">
     <cfloop query="getSupervisor">
             <option value="#getSupervisor.supervisor_ID#">
                        #getSupervisor.last_name#,#getSupervisor.first_name#
              </option>
     </cfloop>
</select>

<cfquery name="getSupervisor" datasource="#datasource#">
      SELECT   *
FROM         employee_dataTest
WHERE     (leader_coach = 1) OR
                      (supervisor = 1) AND (active_account = 1) AND (IDassignedsupervisor = #getEmployees.Supervisor_ID#)
</cfquery>

>   (IDassignedsupervisor = #getEmployees.Supervisor_ID#)

It's likely because of that line.  Don't query within a loop. What you need to do is run two queries, *outside* any loops. One to get all employees. The other to get *possible* supervisors.  

<cfquery name="getAllEmployess">
        query to get ALL employees
</cfquery>
<cfquery name="getAllSupervisors">
        query to get ALL supervisors (not just one)
</cfquery>

Then loop through the results to generate the form fields

<cfoutput query="getAllEmployess">
        show current employee
       <select name="SuperVisorID">
              <cfloop query="getAllSupervisors">
              <option value="#getAllSupervisors.ID#">
                        #getAllSupervisors.last_name#,#getAllSupervisors.first_name#
              </option>
             </cfloop>
       </select>

</cfoutput>

i am guessing at that point we will have to evaluate with an if or some other statement if supervisorid from query1 = issupervisor from query2 then display

Not to display no. Just to "pre-select" the current supervisor.  My understanding is he wants the list to display all supervisors. (Wouldn't really make much sense to create a <select> list with only one item).

But pre-selecting is simple. I'd get the basic form working first.

I thought I might just make a supervisor table and use the join query to grab the field to display

I get where agx is going here (I like it) less headache - less overhead as well.

You simply populate the select box with ALL supervisors and you select the relevant supervisor when making your edit.

Like this?

  <cfoutput query="getEmployees">
     
<cfform method="post" style="margin:0px" action="Admin.cfm">


      <cfinput type="hidden" name="ID" value="#ID#">
<tr>
<td><cfinput type="text" name="first_name" value="#first_name#" size="10"></td>
             <td><cfinput type="text" name="middle_name"  value="#middle_name#"   size="1"/></td>
          <td><cfinput type="text" name="last_name" value="#last_name#"   size="15"/></td>
          <td><cfinput type="text" name="main_dept"  value="#main_dept#"   size="2"/></td>
          <td><cfinput type="text" name="job_title"  value="#job_title#"   size="30"/></td>  
          <td><cfinput type="text" name="phone_ext"  value="#phone_ext#"   size="3"/></td>
          <td><cfinput type="text" name="email_name"  value="#email_name#"   size="15"/></td>
          <td><cfinput type="text" name="birth_month"  value="#birth_month#"   size="2"/></td>
          <td><cfinput type="text" name="birth_date"  value="#birth_date#"   size="2"/></td>
          <td><cfinput type="text" name="payroll_num"  value="#payroll_num#"   size="6"/></td>
          <td>
   
       <select name="SuperVisorID">
              <cfloop query="getSupervisor">
              <option value="#getSupervisor.supervisor_ID#">
                        #getSupervisor.last_name#,#getSupervisor.first_name#
              </option>
             </cfloop>
       </select>

            </td>            
           <td>
        <cfinput type="submit" name="editing"
                  value="EDIT" style="font-size:xx-small">
               </td>
           <td>
            <cfinput type="submit" name="deleting"
                  value="Inactivate" style="font-size:xx-small">
            </td>
            </tr>
</cfform>
</cfoutput>  

It gives me one supervisor for all

> It gives me one supervisor for all

Not possible, unless you forgot to remove the WHERE clause filters from the getSupervisor query.  Remember it should return *all possible* supervisors. Does it? Dump that query and check.

In other words, verify the queries contain the right data first. If they don't, the form will definitely be wrong. So there's no point testing the form code yet.

It's correct - 15 show up in the dropdown - it defaults to the first one in the list - I should have been more clear - I meant to say it gives the first supervisor for all

> it defaults to the first one in the list - I should have been more clear -
> I meant to say it gives the first supervisor for all

(EDIT)  Good. That's what should happen. We just need to add code to pre-select the current supervisor which we didn't add yet.  Give me a minute

That is how it reads now

I see - no problem

>  <option value="#getSupervisor.supervisor_ID#">

Quick note, shouldn't that be "ID"?  Otherwise, you're using the ID  of the supervisor's - supervisor (ie grandparent rather than parent). If that makes sense ;-)

Assuming it should be "ID", use this code for the select instead:

      <!--- get supervisor of current employee --->
     <cfset currSupervisorID = getEmployees.supervisor_ID>
     <select name="SuperVisorID">
              <cfloop query="getSupervisor">
              <option value="#getSupervisor.ID#"
                    <!--- preselect current supervisor --->
                    <cfif getSupervisor.ID eq currSupervisorID>selected</cfif>
                     >#getSupervisor.last_name#,#getSupervisor.first_name#
              </option>
             </cfloop>
       </select>

Edit: Sorry for doing it in steps, but it seemed we were all on different wavelengths.. Confirming each step is working as expected seemed the quickest way to get things back on track.

Ok that seems to be correct but it needs to be in a text box - the dropdown shows the first one in the list in correct records but the rest are blank - when I click the dropdown the correct name is there but does not display -

I am having trouble with the syntax for a text box with a query

<cfset currSupervisorID = getEmployees.supervisor_ID>
<td><cfloop query="getSupervisor"><cfinput type="text" name="SuperVisorID"  value="<cfif getSupervisor.ID eq currSupervisorID>#getSupervisor.last_name#,#getSupervisor.first_name#</cfif>"   size="6"/>  </cfloop></td>

Sorry but I'm totally confused now :/ ... (and have a meeting now).

As long the query contains first and last names for every supervisor, there's no reason they'd be blank. Not unless there's an error in the CF code, like a misaligned CFIF or something. I use this same technique all the time and it works perfectly.

Are you trying to create a text box because you can't make the list work? Because the current query makes no sense with a text box. You will just end up with multiple text boxes - one for each row in getSupervisor. Plus users won't be able to change the supervisor.

Can you post your complete code (the version with the list)? I have a feeling it's doing something different about than what I posted earlier.  Maybe someone else can spot it while I'm away.

Ok but in a nutshell the first one in the list appears correctly - all the others "are there" only when you click on the dropdown and they show like the picture - and they are correct they just need to move up the list to display properly
example2.jpg

Here is the code

 
 <cfquery name="getEmployees" datasource="#datasource#">
select * from employee_dataTest
</cfquery>
<cfquery name="getSupervisor" datasource="#datasource#">
 SELECT *
      FROM         employee_dataTest
      WHERE     leader_coach = 1 OR
                      ((supervisor = 1) AND (active_account = 1))
</cfquery>
 
 
 
 
 <cfoutput query="getEmployees">
  <cfform method="post" style="margin:0px" action="Admin.cfm">
    <cfinput type="hidden" name="ID" value="#ID#">
    <tr>
      <td><cfinput type="text" name="first_name" value="#first_name#" size="10"></td>
      <td><cfinput type="text" name="middle_name"  value="#middle_name#"   size="1"/></td>
      <td><cfinput type="text" name="last_name" value="#last_name#"   size="15"/></td>
      <td><cfinput type="text" name="main_dept"  value="#main_dept#"   size="2"/></td>
      <td><cfinput type="text" name="job_title"  value="#job_title#"   size="30"/></td>
      <td><cfinput type="text" name="phone_ext"  value="#phone_ext#"   size="3"/></td>
      <td><cfinput type="text" name="email_name"  value="#email_name#"   size="15"/></td>
      <td><cfinput type="text" name="birth_month"  value="#birth_month#"   size="2"/></td>
      <td><cfinput type="text" name="birth_date"  value="#birth_date#"   size="2"/></td>
      <td><cfinput type="text" name="payroll_num"  value="#payroll_num#"   size="6"/></td>
      <td>
      
      <cfset currSupervisorID = getEmployees.supervisor_ID>
        <select name="SuperVisorID">
          <cfloop query="getSupervisor">
            <option value="#getSupervisor.ID#"> 
            <!--- preselect current supervisor --->
            <cfif getSupervisor.ID eq currSupervisorID>
              #getSupervisor.last_name#,#getSupervisor.first_name#
            </cfif>
            </option>
          </cfloop>
        </select></td>
        
      <td><cfinput type="submit" name="editing"
			value="EDIT" style="font-size:xx-small"></td>
      <td><cfinput type="submit" name="deleting"
			value="Inactivate" style="font-size:xx-small"></td>
    </tr>
  </cfform>
</cfoutput> 

Select allOpen in new window

I thought that was a typo because the color coding was off- thanks once again agx