How to use OnChange in a select (drop down list) box

[ndeeley]

Hi,

I have a CF query that extracts all the information from a db and sprays in into a table.

I'd like a drop down list box that can filter this data based on the value in the box. IE at loading it shows 'All' jobs, but when changed to 'Completed' it just shows Completed jobs. I'd like to use the onChange event to trigger this.

What's the best way of achieving this in Cold Fusion?

Thanks
Neil

[acoder]

The newer versions of Coldfusion have tags that probably allow you to do this without much knowledge of JavaScript/Ajax. If you're using an older version, then you'll need to have at least some basic JavaScript knowledge unless you use a submit button instead of directly changing via an onchange.

As for the Coldfusion part, you could use a Query of Queries together with a cached query which queries the query returned rather than the database each time, though it's not necessary.

Should it be instant, or can the change happen with a page change?

[ndeeley]

I'm using CF MX (5 I think) so that rules out any easy option! I am using a submit button at the moment, with a form action to a copy of the page. I'd rather keep it all in one page - should I set up a variable and use IsDefined to find if the submit button has attributed it a value?

[ndeeley]

Ah,

I think I have done it but with one problem.

I created a form with the same action as the page:

<form action="TB_MyTasksGIS.cfm" method="post">
        Tasks to show
        <select name="changeitem" class="droplist">
        <option value="*">All</option>
        <option value="Not Begun">Not Begun</option>
        <option value="Started">Started</option>
        <option value="Pending">Pending</option>
        <option value="Completed">Completed</option>
        </select>
        <input type="submit" value="submit" class="buttons">
        </form>
 

then set up an cf IsDefined to catch it:

<cfif IsDefined("form.changeitem")>
 
<cfquery datasource="taskbook" name="GetMyTasks">
select        T.ID, 
            T.WorkID, 
            T.CustomerName,
            T.Dept,
            T.Location,
            T.ContactNo,
            T.EmailAddress,
            T.TaskTypeFK,
            T.TaskDetails,
            T.ChargeableYN,
            T.ChargeCode,
            T.JobStatusFK,
            T.AssignedToFK,
            T.AssignedNotes,
            T.AssignedByFK,
            T.AssignedStatusFK,
            T.StartDate,
            T.PendReasonFK,
            T.CompletionDate,
            T.TimeTaken,
            T.CompletionNotes,
            T.FileServerFK,
            T.FileLoc,
            T.RequestDate,
            T.AddedBy,
            S.FirstName,
            S.Surname,
            S.LoginName
from        tblTaskBooker T left outer join tblStaff S
on            T.AddedBy = S.LoginName
where        AddedBy = '#username#' and JobStatusFK = '#form.changeitem#'
order by    ID
</cfquery>
 
<cfelse>
 
..same code again with no "and JobStatusFK"
 
</cfif>
 
 

which all works fine - I just now can't get the table to show all the records again if selected from the list. I'm assuming my value in the select 'All' field is wrong. I've tried ="" and ="*" but neither work. Any idea what I am doing wrong?

[acoder]

You need to get rid of the condition to show all the records.

You can use an empty string and use cfparam to give a default value, then when "changeitem" is empty, you can remove the condition.

In fact, you could have one query and have the cfif around the condition:

where AddedBy = '#username#'
<cfif form.changeitem NEQ "">
    and JobStatusFK = '#form.changeitem#'
</cfif>

You should also be using cfqueryparam for any user input into a query.

[ndeeley]

All changed!

Brilliant - thanks very much. Saved me a lot of code!

Cheers
Neil

[ndeeley]

Hi,

I've used this script successfully but now have a small amendment.

I have two of the same scripts on the same page, but with different form.changeitem names. obviously because of the default at the top when I use either of the forms the other defaults back to showing that value, when I want to keep it displaying the information the user has already selected:

<cfparam name="form.changeitem" default="">
<cfquery datasource="taskbook" name="GetMyTasks">
    select        T.ID, 
                T.WorkID, 
                T.CustomerName,
                T.Dept,
                T.Location,
                T.ContactNo,
                T.EmailAddress,
                T.TaskCategoryFK,
                T.TaskTypesFK,
                T.TaskDetails,
                T.ChargeableYN,
                T.ChargeCode,
                T.JobStatusFK,
                T.AssignedToFK,
                T.AssignedNotes,
                T.AssignedByFK,
                T.AssignedStatusFK,
                T.StartDate,
                T.PendReasonFK,
                T.CompletionDate,
                T.TimeTaken,
                T.CompletionNotes,
                T.FileServerFK,
                T.FileLoc,
                T.RequestDate,
                T.AddedBy,
                S.FirstName,
                S.Surname,
                S.LoginName
    from        tblTaskBooker T left outer join tblStaff S
    on            T.AddedBy = S.LoginName
    where        AddedBy = '#username#'
                <cfif form.changeitem NEQ ""> 
                and JobStatusFK = '#form.changeitem#' 
                </cfif>
    order by    RequestDate desc
</cfquery>
 
<cfparam name="form.changeAitem" default="">
 
 
        <cfquery datasource="taskbook" name="GetMyATasks">
        select        T.ID, 
                    T.WorkID, 
                    T.CustomerName,
                    T.Dept,
                    T.Location,
                    T.ContactNo,
                    T.EmailAddress,
                    T.TaskCategoryFK,
                    T.TaskTypesFK,
                    T.TaskDetails,
                    T.ChargeableYN,
                    T.ChargeCode,
                    T.JobStatusFK,
                    T.AssignedToFK,
                    T.AssignedNotes,
                    T.AssignedByFK,
                    T.AssignedStatusFK,
                    T.StartDate,
                    T.PendReasonFK,
                    T.CompletionDate,
                    T.TimeTaken,
                    T.CompletionNotes,
                    T.FileServerFK,
                    T.FileLoc,
                    T.RequestDate,
                    T.AddedBy,
                    S.FirstName,
                    S.Surname,
                    S.LoginName
        from        tblTaskBooker T left outer join tblStaff S
        on            T.AssignedByFK = S.LoginName
        where        AssignedStatusFK = 'Assigned' and AssignedToFK = '#username#' and AddedBy <> '#username#'
                        <cfif form.changeAitem NEQ ""> 
                        and JobStatusFK = '#form.changeAitem#' 
                        </cfif>
        order by    RequestDate desc
        </cfquery>
 

How can I ensure that the lower form shows the form.ChangeAitem value instead of resetting it back to default when the page refreshes? I've tried using:

<cfif Not IsDefined ("form.changeitem")
..set default
<cfself>
..run code with form.changeitem value
</cfif>
 

but I can't get this to work!

Thanks

[acoder]

How does the page refresh? Is it a form post?

If it is, you can pass the current value(s) of changeitem as a hidden field(s).

[ndeeley]

Yes, it is. I'll try passing the value as a hidden field in the url...

Let you know how I get on.

Thanks acoder!

[acoder]

A quick note: if you pass a value in the URL, it would be accessed as url.fieldname. To have the value accessible as form.fieldname, you need to post the form.