19 Replies Latest reply: Jun 15, 2012 11:22 AM by jawmusic RSS

    Problem creating list of changed entries

    jawmusic

      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 structKeyExists(form, "submit")>

       

      <!--- set new list to equal original list --->

       

        <cfset changedIDs = form.listofIDs>

        <cfloop index="ID" list="#form.listofIDs#">

          <cfset currentID = "#ID#">

       

      <!--- set displayOld as original value --->

       

          <cfset displayOld = "#file_listing.Display#">

       

      <!--- set displayNew as current value --->

       

          <cfif IsDefined("form.display#ID#")>

            <cfset displayNew = "-1">

          <cfelse>

            <cfset displayNew = "0">

          </cfif>

       

      <!--- compare old and new and if equal, find index of current id in changed ID list --->

       

          <cfif displayOld EQ displayNew>

            <cfset IDPosition = ListFind(changedIDs, currentID)>

       

      <!--- delete ID at index determined above --->

       

            <cfset changedIDs = ListDeleteAt(changedIDs, IDPosition)>

          </cfif>

       

      <!--- run the update query on all records (I've tried it on all and on only the changed records and it seems to work fine either way, so I believe the problem is with the list functions above) --->

       

          <cfquery datasource="ctband">

            UPDATE Files

            SET Display = #displayNew#

            WHERE ID = #currentID#

          </cfquery>

        </cfloop>

       

      <!--- set the original and changed lists as url parameters to check if successful --->

       

        <cflocation url="/admin/filelist_update_confirm.cfm?startlist=#form.listofIDs#&endlist=#changedIDs#">

      </cfif>

        • 1. Re: Problem creating list of changed entries
          Dan Bracuk Community Member

          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.

          • 2. Re: Problem creating list of changed entries
            jawmusic Community Member

            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

            • 3. Re: Problem creating list of changed entries
              -==cfSearching==- Community Member

              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.

               

                           <cfset idsThatWereUnchecked = ListCompare( form.originalList, form.nameOfCheckboxField )>

               

              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"> )

              • 4. Re: Problem creating list of changed entries
                jawmusic Community Member

                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?:

                 

                <cfparam name="changedIDs" default="">

                 

                <!--- start the loop...code omitted --->

                 

                <cfset currentID = ID>

                <cfif displayOld NEQ displayNew>

                  <cfif changedIDs EQ "">

                    <cfset changedIDs = currentID>

                  <cfelse>

                    <cfset changedIDs = ListAppend(changedIDs, currentID)>

                  </cfif>

                 

                <!--- Run update query...code omitted --->

                 

                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"):

                 

                <cfquery dbtype="query" name="YesIDs">

                  SELECT ID

                  FROM file_listing

                  WHERE Display = "Yes"

                </cfquery>

                <cfset startYes = ValueList(YesIDs, ID)>

                 

                <cfquery dbtype="query" name="NoIDs">

                  SELECT ID

                  FROM file_listing

                  WHERE Display = "No"

                </cfquery>

                <cfset startNo = ValueList(NoIDs, ID)>

                 

                <!--- pass them as url parameters to confirmation page --->

                 

                <cflocation url="confirm.cfm?startYes=#startYes#&startNo=#startNo#">

                 

                On the confirmation page:

                 

                <cfset startYes = url.startYes>

                <cfset startNo = url.startNo>

                <cfquery name="YesIDs" datasource="ctband">

                  SELECT ID

                  FROM Files

                  WHERE Display = "Yes"

                </cfquery>

                <cfset newYes = ValueList(YesIDs, ID)>

                <cfquery name="NoIDs" datasource="ctband">

                  SELECT ID

                  FROM Files

                  WHERE Display = "No"

                </cfquery>

                <cfset newNo = ValueList(NoIDs, ID)>

                 

                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?

                • 5. Re: Problem creating list of changed entries
                  BKBK MVP

                  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 structKeyExists(form, "submit")>

                      <!--- set new list to equal original list --->

                      <cfset changedIDs = form.listofIDs>

                      <cfloop index="ID" list="#changedIDs#">

                          <!--- set displayOld as original value --->

                          <cfset displayOld = "#file_listing.Display#">

                          <!--- set displayNew as current value --->

                          <cfif IsDefined("form.display#ID#")>

                              <cfset displayNew = "-1">

                          <cfelse>

                              <cfset displayNew = "0">

                          </cfif>

                          <!--- compare old and new and if equal, find index of current id in changed ID list --->

                          <cfif displayOld EQ displayNew>

                              <cfset IDPosition = ListFind(changedIDs, ID)>

                              <!--- delete ID at index determined above --->

                              <cfif IDPosition NEQ 0>

                                  <cfset changedIDs = ListDeleteAt(changedIDs, IDPosition)>

                              </cfif>

                          </cfif>

                          <!--- run the update query on all records (I've tried it on all and on only the changed records and it seems to work fine either way, so I believe the problem is with the list functions above) --->

                          <cfquery datasource="ctband">

                          UPDATE Files

                          SET Display = #displayNew#

                          WHERE ID = #ID#

                          </cfquery>

                      </cfloop>

                  <!--- set the original and changed lists as url parameters to check if successful --->

                  <cflocation url="/admin/filelist_update_confirm.cfm?startlist=#form.listofIDs#&endlist=#changedIDs#">

                  </cfif>

                  • 6. Re: Problem creating list of changed entries
                    -==cfSearching==- Community Member

                    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.

                    • 7. Re: Problem creating list of changed entries
                      jawmusic Community Member

                      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!

                      • 8. Re: Problem creating list of changed entries
                        -==cfSearching==- Community Member

                        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

                        • 9. Re: Problem creating list of changed entries
                          jawmusic Community Member

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

                           

                          Connected by DROID on Verizon Wireless

                          • 10. Re: Problem creating list of changed entries
                            jawmusic Community Member

                            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:

                             

                            <cfparam name="url.ensFilter" default="">

                            <cfparam name="url.catFilter" default="">

                            <cfparam name="url.selFilter" default="">

                            <cfset pastDate=DateAdd("d", -180, Now())>

                            <cfquery name="file_listing" datasource="ctband">

                            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:

                             

                            <cfparam name="url.ensFilter" default="">

                            <cfparam name="url.catFilter" default="">

                            <cfparam name="url.selFilter" default="">

                            <cfset pastDate=DateAdd("d", -180, Now())>

                            <cfquery name="file_listing" datasource="ctband">

                            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 structKeyExists(form, "submit")>

                              <cfset listofIDs = ValueList(file_listing.ID)>

                              <cfloop index="ID" list="#listofIDs#">

                                <cfset displayOld = file_listing.Display>

                                <cfif IsDefined("form.display#ID#")>

                                  <cfset displayNew = -1>

                                <cfelse>

                                  <cfset displayNew = 0>

                                </cfif>

                                <cfif displayNew NEQ displayOld>

                                  <cfquery datasource="ctband">

                                    UPDATE Files

                                    SET Display = #displayNew#

                                    WHERE ID = #ID#

                                  </cfquery>

                                </cfif>

                              </cfloop>

                              <cflocation url="/admin/admin.cfm">

                            </cfif>

                            • 11. Re: Problem creating list of changed entries
                              BKBK MVP

                              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:

                               

                              <cfset displayNew = -1>

                              <cfif form["display#ID#"] NEQ -1>

                                    <cfset displayNew = 0>

                              </cfif> 

                               

                              WHERE ID = #ID#

                               

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

                              • 12. Re: Problem creating list of changed entries
                                jawmusic Community Member

                                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

                                • 13. Re: Problem creating list of changed entries
                                  -==cfSearching==- Community Member

                                  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 (...)

                                   

                                                <!--- for brevity omitted cfqueryparam --->

                                                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?

                                   

                                                     <cfdump var="#URL#" label="URL">

                                                     <cfdump var="#FORM#" label="FORM">

                                                     <cfdump var="#yourResultName#" label="Result">

                                  • 14. Re: Problem creating list of changed entries
                                    BKBK MVP

                                    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:

                                     

                                    <cfif structKeyExists(form, "submit")>

                                        <cfset listofIDs = ValueList(file_listing.ID)>  

                                        <cfset rowNo = 1>

                                     

                                        <cfloop index="ID" list="#listofIDs#">

                                            <!--- Associate each display with the corresponding ID --->

                                            <cfset displayOld = file_listing.Display[rowNo]>

                                            <cfif IsDefined("form.display#ID#")>

                                                <cfset displayNew = -1>

                                                <cfelse>

                                                <cfset displayNew = 0>

                                                </cfif>

                                                <cfif displayNew NEQ displayOld>

                                                <cfquery datasource="ctband">

                                                UPDATE Files

                                                SET Display = #displayNew#

                                                WHERE ID = #ID#

                                                </cfquery>

                                            </cfif>

                                            <cfset rowNo = rowNo+1>

                                        </cfloop>

                                        <cflocation url="/admin/admin.cfm">

                                    </cfif>

                                    • 15. Re: Problem creating list of changed entries
                                      -==cfSearching==- Community Member

                                      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.

                                      • 16. Re: Problem creating list of changed entries
                                        jawmusic Community Member

                                        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

                                        • 17. Re: Problem creating list of changed entries
                                          jawmusic Community Member

                                          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

                                          • 18. Re: Problem creating list of changed entries
                                            -==cfSearching==- Community Member

                                            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:

                                             

                                                       <cfset displayOld = file_listing.Display>

                                             

                                            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:

                                             

                                                 <cfloop query="file_listing">

                                                   <cfoutput>

                                                      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"

                                             

                                                             <input type="checkbox" name="selectedIDs" value="#ID#">

                                             

                                            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.

                                            • 19. Re: Problem creating list of changed entries
                                              jawmusic Community Member

                                              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!