Problem creating list of changed entries

Report · Jun 08, 2012

I've got a form that calls a list of file names and other details from a data table. There is a checkbox to select whether or not each item will appear on another page, and I'm trying to keep track of which records are changed each time the form is submitted. For example, if all items are selected (checked) and you uncheck the first 4, I'd like to create a variable that will save the ids of those items in a list so I can call it on a confirmation page. I'm having trouble getting my sql to populate the list properly though, and I'm not sure exactly why. In order to run the loop, I've collected a list of all the items displayed in a hidden form field called "listofIDs." That should be the starting point. What I've tried to do is as the loop is processed, compare the original value of the checkbox (displayOld) to the new value of the checkbox (displayNew) and if they are the same, remove that id from the original list. Then I could call that list as a url parameter and use it on the confirmation page. Unfortunately, it usually works properly when I go from all items selected to deselecting a couple, but once I've got some selected and some not at the starting point, it's not giving me the right IDs as the changed records. Could anyone please check my sql below and let me know what the issue is? I've included comments as to my thought process.

</cfif>

</cfif>

UPDATE Files

SET Display = #displayNew#

WHERE ID = #currentID#

</cfquery>

</cfloop>

</cfif>

Report · Jun 08, 2012

My first suggestion is to get rid of your query and cflocation code until you sort out your check boxes.

My second suggestion is, when in doubt, look at your data. I generally troubleshoot if/else problems like this:

if I see what I expect to see

output yes

else

output no, what I expected, and what I got.

Finally, once you solve your current problem of the user unchecking previously checked boxes, start looking at what happens if they check some previously unchecked boxes.

Report · Jun 08, 2012

Thanks for your thoughts, Dan. I'll try some of those ideas. The only thing is, the database is being updated properly, so I don't think the problem is with the checkboxes. I think it has something to do with the list functions, but I guess I'm not familiar enough with using and manipulating lists (and many other aspects of Coldfusion, for that matter) to really understand what I'm doing wrong. I'll play around with it some more and see if I can figure it out.

I really appreciate all the help, Dan and everyone else. Hopefully it's not too frustrating helping out a newbie!

Jeremy

Report · Jun 08, 2012

I am not sure followed all that correctly 😉 But it sounds like you ultimately want to compare two lists of id's and identify which ones were removed from the originalList.

ie

originalList = 1,3,4,5

newList = 3,5,6

idsThatWereUnchecked = 1,4

If so, it is simpler to give your checkboxes the same name (using the ID for the checkbox "value"). Then when the form is submitted, the checkbox value will be a list of all id's that were checked . Simply compare that list to the original and find the differences. (There is a handy function at cflib.org called ListCompare which does exactly that). Then do whatever you need to do with the list of checked and unchecked ids.

Also, if you are updating all records with the same value you can a single IN (...) clause is generally more efficient than looping ie performing multiple updates. At least for a moderate amount of values.

UPDATE SomeTable

SET Display = 0

WHERE ID IN ( <cfqueryparam value="#someListOfIDs#" cfsqltype="cf_sql_integer" list="true"> )

Report · Jun 09, 2012

Okay, I think what you posted will do the trick, but I'll have to run it twice. Let me try to explain what I'm doing again:

I have a database with a list of files, names, locations, urls, etc, that are called up on a page for my students to download. Periodically I have to hide certain files from being displayed, and show other ones that were hidden. For this, I have a Yes/No field called "Display" in the table. The display page for my students only shows those marked "Yes."

I also have a protected page that only I can view that lists ALL the files that have been uploaded, regardless of the status of the Display column. The purpose of this page is to be able to hide/unhide different files in the table. This page has a form with a loop function that displays the records, a hidden field that contains the ID (set to of the record, and a checkbox input (named display#D#) that indicates the current value of Display.

What I'm trying to do is somehow record which records were changed, either from yes to no, or no to yes, so I can send these IDs to the confirmation page that displays after you submit the form. The way I see it, I can do it one of 3 ways, but I haven't been able to get any of them to work so far:

1. The update form has a hidden field called "listofIDs" that displays a list of all the records displayed on the update page (which is not every record from the table, some are filtered out by date, etc.). As the update loop is processed, I could compare the original value of display#ID to the new value and if they ARE equal, remove that ID from the list, which is what I had in my first example. Then I could pass the original list (listofIDs) and the new list (changedIDs) to the confirmation page to be processed.

2. When I compare the two values, if they are UNEQUAL, I could add them to a list. The thing I'm not sure about here is how this works if my list only has one value. Will it be processed correctly if it's not actually a "list" yet? Do I need to include a dummy value to start and then remove it later so that CF knows it's a list and not just a single value? Or, will CF automatically change it to a list once I run the ListAppend function?:

</cfif>

In theory, this would be processed inside the loop, so changedIDs should retain it's previous value and just be appended each time.

3. I can run a query of queries to creat a list of records that are originally set to "Yes" and another list that are originally set to "No." Then on the confirmation page I could repeat the process, create new lists, compare them to the original lists, and then extract the changes:

On the update page (original query is "file_listing"):

SELECT ID

FROM file_listing

WHERE Display = "Yes"

</cfquery>

SELECT ID

FROM file_listing

WHERE Display = "No"

</cfquery>

On the confirmation page:

SELECT ID

FROM Files

WHERE Display = "Yes"

</cfquery>

SELECT ID

FROM Files

WHERE Display = "No"

</cfquery>

Then I can run the compare list function you mentioned to find which values are in the new lists but not the old lists.

Thoughts on the best approach?

Report · Jun 10, 2012

I can run a query of queries

I would not bother with a QoQ unless you need the data for something else in the page. Just run two database queries: one to retrieve "yes" records and another to retrieve the "no" records.

What I'm trying to do is somehow record which records were changed, either from yes to no, or no to yes

There are several different ways you could slice it. But technically you could derive that information with just two lists: ids marked "yes" (before any changes) and ids marked "yes" (after the changes).

- Id's that exist in the original, but not the new list, were changed from "yes" -> "no"

- Id's that exist in the new list, but not the original, were changed from "no" -> "yes"

Is there a problem using that function in that position inside the query?

Yes, databases knows nothing about CF functions and vice versa. Within a cfquery, the ColdFusion server evaluates any CF code first. The server translates those expressions into simple strings. Then sends the final sql string off to your database for execution. So you cannot use CF functions on a database column because it is evaluated long before you are even connected to the database.

Like I mentioned above, you should use a WHERE IN (...) clause to return multiple records by id. Just with a SELECT rather than UPDATE. See the example and link above.

Report · Jun 10, 2012

Yeah, I realized that shortly after I posted that and tried to get rid of the post before anyone saw it. I got it to work using the where in statement. After all that, it was really easy. Thanks for all the help!

Report · Jun 10, 2012

The forums automatically send email notifications when new responses are posted (but not edits or deletes). So those responding via email do not see those kinds of changes. Nothing is ever really deleted on the internet

Anyway, glad you resolved it. It is always nice when the solution turns out to be simpler than you thought.

-Leigh

Report · Jun 11, 2012

Yeah, I know, but it was worth a shot. Thanks again!

Connected by DROID on Verizon Wireless

Report · Jun 13, 2012

Okay, so I discovered an error in my SQL. The page is set up as a bound cfm file so that you can filter the results on the display page. There are three filters: Category, Ensemble, and Selection. The checkboxes should set the Display field in the data table to either -1 or 0. When I filter the results to display only the items where Selection = 'Part I', and then I submit the form to update the table, any items that have been filtered out (e.g. Selection = 'Part III') are set to 0. My intention is to leave these items unchanged. Here's the sql on the bound document that controls the filter settings:

SELECT *

FROM Files

WHERE Upload_Time > #pastDate#

<cfif url.ensFilter NEQ "">AND Ensemble = <cfqueryparam value="#url.ensFilter#" cfsqltype="cf_sql_varchar"></cfif>

<cfif url.catFilter NEQ "">AND Category = <cfqueryparam value="#url.catFilter#" cfsqltype="cf_sql_varchar"></cfif>

<cfif url.selFilter NEQ "">AND Selection = <cfqueryparam value="#url.selFilter#" cfsqltype="cf_sql_varchar"></cfif>

ORDER BY Upload_Time DESC, ID ASC

</cfquery>

That part seems to be working properly. However, it seems that the variable "listofIDs" set below is being set to ALL the items in the table, instead of only those currently being displayed based on the filters. Therefore, when the update query processes those items, and cannot find the corresponding checkbox, it's being set to 0. Any ideas for a workaround? Here's the full SQL:

SELECT *

FROM Files

WHERE Upload_Time > #pastDate#

<cfif url.ensFilter NEQ "">AND Ensemble = <cfqueryparam value="#url.ensFilter#" cfsqltype="cf_sql_varchar"></cfif>

<cfif url.catFilter NEQ "">AND Category = <cfqueryparam value="#url.catFilter#" cfsqltype="cf_sql_varchar"></cfif>

<cfif url.selFilter NEQ "">AND Selection = <cfqueryparam value="#url.selFilter#" cfsqltype="cf_sql_varchar"></cfif>

ORDER BY Upload_Time DESC, ID ASC

</cfquery>

</cfif>

UPDATE Files

SET Display = #displayNew#

WHERE ID = #ID#

</cfquery>

</cfif>

</cfloop>

</cfif>

Report · Jun 14, 2012

jawmusic wrote:
    <cfif IsDefined("form.display#ID#")>
      <cfset displayNew = -1>
    <cfelse>
      <cfset displayNew = 0>
    </cfif>

This code is in a section that has already confirmed that the form has been submitted. Hence IsDefined("form.display#ID#") will always be True. Did you perhaps intend to say something like:

</cfif>

WHERE ID = #ID#

Didn't you say earlier that you had to correct this into WHERE ID IN (...)?

Report · Jun 14, 2012

BKBK,

The “Where In” was on the confirmation page in order to display info about which records were changed. This is on the action page where the records are actually updated. As for that “IsDefined” statement, that’s for the checkbox, so it’s only defined if it’s currently checked, unless I’m mistaken. That’s the way I’ve always handled checkboxes and I’ve never had a problem, including here.

I’m pretty sure the issue isn’t actually with that checkbox, it’s that the update query is updating all the records in the default query (before the filters are changed) instead of only the records displayed after the filter fields are changed. For example, when the page first loads, it’s loading all files uploaded in the last 6 months. Then you can filter those results using selection fields to narrow the display down to a certain ensemble (marching_band, percussion, guard), selection (Part I, Part II, etc.) or category (music, visual, etc.). The filters are working fine for the display, they’re just not filtering the list of IDs that get processed through the update query. I thought once I moved etc.), but that didn’t work either.

JAW

Report · Jun 14, 2012

jawmusic wrote:
The “Where In” was on the confirmation page in order to display info about which records were changed. This is on the action page where the records are actually updated. As for that “IsDefined” statement, that’s for the checkbox, so it’s only defined if it’s currently checked, unless I’m mistaken.

What you say is justified.

I’m pretty sure the issue isn’t actually with that checkbox, it’s that the update query is updating all the records in the default query (before the filters are changed) instead of only the records displayed after the filter fields are changed. For example, when the page first loads, it’s loading all files uploaded in the last 6 months. Then you can filter those results using selection fields to narrow the display down to a certain ensemble (marching_band, percussion, guard), selection (Part I, Part II, etc.) or category (music, visual, etc.). The filters are working fine for the display, they’re just not filtering the list of IDs that get processed through the update query. I thought once I moved etc.), but that didn’t work either.

Quite clear.However, I suspect the problem is actually with the display! Your code, <cfset displayOld = file_listing.Display>, treats display as if there is just one value. There is one for every row of data.

I therefore expected to see each ID being associated with the display value from the same row. Something like this:

<cfset displayOld = file_listing.Display[rowNo]>

</cfif>

UPDATE Files

SET Display = #displayNew#

WHERE ID = #ID#

</cfquery>

</cfif>

</cfloop>

</cfif>

Report · Jun 15, 2012

Your code, <cfset displayOld = file_listing.Display>, treats display as if there is just one value.

Good spot. You need to loop through both values ie use a a query loop. That said, unless there are other factors we are unaware of, the IN (...) approach is simpler IMO.

Report · Jun 15, 2012

Thank you both for your input, I will try some of these ideas today. What I don’t understand, and I suppose it’s because this is all so new to me, is why the “ would be a problem. Since it’s inside the loop, isn’t it being set to a single value on each cycle of the loop? And wouldn’t that value correspond to the initial value of the Display column for that particular entry? Why then would that be causing problems? For each cycle of the loop, it should only have one value, the value that corresponds to that record’s ID. Am I missing something?

I do thank you both though, and I will see if I can get it to work with some of your suggestions. -==cfSearching==-, I guess I’m not really sure how you are suggesting I use the IN (...) clause in this situation. I’ll play around with it though and see if I can figure it out.

Thanks again!

Jeremy

Report · Jun 15, 2012

EUREKA!

So the solution was, as before, easier than it seemed. Since defining the list based on the original query results wasn't working, I just made a hidden field in the form like this:

<input type="hidden" name="listofIDs" value=<cfoutput>#ValueList(file_listing.ID)#</cfoutput>">

Now #listofIDs# is repopulated with the filters, the same time the form is repopulated! It all makes sense now...

Probably not the easiest or most elegant way of doing what I'm trying to do, but it'll work for our purposes. Thanks again for all your help everyone!

JAW

Report · Jun 15, 2012

jawmusic wrote:
What I don’t understand, and I suppose it’s because this is all so new to me, is why the “ would be a problem. Since it’s inside the loop, isn’t it being set to a single value on each cycle of the loop? And wouldn’t that value correspond to the initial value of the Display column for that particular entry? Why then would that be causing problems? For each cycle of the loop, it should only have one value, the value that corresponds to that record’s ID. Am I missing something?

No, because it is inside a list loop. That only affects your list of id's. It has no impact on the query:

If you do a little debugging (which I highly recommend) and output the values inside your loop you will probably see the #displayOld# value never changes as BKBK mentioned. It always points to the value in the first row of your query. You need to use a query loop instead, so you can iterate through both values:

debugging - current ID = #ID# old display value = #display# <br />

</cfoutput>

</cfloop>

I guess I’m not really sure how you are suggesting I use the IN (...) clause in this situation.
...
I just made a hidden field in the form like this:

Good. Now if you give all of the checkboxes the same name ie "selectedIDs"

you will end up with two lists on your action page: form.listOfIDs (ids that were selected) and form.selectedIDs (new id's after changes). You can compare those two lists to derive what changed. For a more detailed explanation reread my responses from Jun 10, 2012 6:57 AM and Jun 14, 2012 12:36 PM.

Report · Jun 15, 2012

Yeah, I sort of did a combination of what I already had and what you suggested and it spears to be working now. The big issue was the listofIDs. Now that that's being populated correctly, the remaining sql seems to work okay. Thanks again!

Report · Jun 14, 2012

Why use individually named checkboxes at all? The "same name" checkboxes approach we discussed earlier is much simpler. When the form is submitted the checkbox value will be a list of ID's. Simply compare that to another list to find the differences. Then run your UPDATE queries with WHERE IN (...)

UPDATE Table SET Display = 0 WHERE ID IN ( #listOfUNcheckedIDs# )

UPDATE Table SET Display = 1 WHERE ID IN ( #listOfCheckedIDs# )

jawmusic wrote:
However, it seems that the variable "listofIDs" set below is being set to ALL the items in the table, instead of only those currently being displayed based on the filters. Therefore, when the update query processes those items, and cannot find the corresponding checkbox, it's being set to 0.

But to answer your question, the list is populated from the "file_listing" query. That means the query is returning more records than you think. Most likely because the url filters are not defined when you submit the form. The first thing you should do is comment out the cflocation so you can see what is actually happening. Add the "result" attribute to your cfquery, so you can view the generated sql.

<cfquery name="file_listing" datasource="ctband" result="yourResultName"> .....

Then dump the result, URL and FORM scopes: What are the results?

Report · Jun 10, 2012

2 points regarding your original code.

1) There is no need to introduce a new variable, currentID. ID is implicitly the current ID.

2) Wasn't it your intention to do the cflocation outside the loop?

I am thinking of a starting point like the following, then paying attention to what other posters have said:

</cfif>

</cfif>

UPDATE Files

SET Display = #displayNew#

WHERE ID = #ID#

</cfquery>

</cfloop>

</cfif>

Adobe Community

Problem creating list of changed entries