10 Replies Latest reply on Sep 7, 2007 6:55 AM by rpmindllc

    CFLOOP List

      Im wanting to loop some records, each record has a list of division ids in a field called divverify.

      I want to run a query inside the loop against each id in the fields so that an email gets sent out to all the id numbers.

      It is throwing me an error - probably because it is trying to list all the ids at once, and not individually after every loop. How can I achieve this?

      Here is the error:

      Error Executing Database Query.
      The multi-part identifier "seldealer.divverify" could not be bound.

      24 : SELECT *
      25 : FROM loldivlist
      26 : WHERE divid=#i#
      27 : </CFQUERY>

      ::::::::::::::: Here is the CODE :::::::::::::::
        • 1. Re: CFLOOP List
          Try using the query attribute of cfloop to loop through all the records in the first query. Although if the first query returned a single record where divverify is a list, you had it right but you need to do list="#seldealer.divverify#".

          • 2. Re: CFLOOP List
            rpmindllc Level 1
            I am wanting to loop thru the list contained in the divverify field - the example you gave me will loop thru the dealers - I need to loop thru the divisionid's (in essence the divisions) to email the divisions associated with that dealer area to send the division an email. How do I query each divisionid in that list one at a time and loop at the same time?
            • 3. Re: CFLOOP List
              rpmindllc Level 1
              Nevermind - I just saw what you pointed out I did wrong - I did not put the ## signs around the list variable - that was it and it worked great. I was so CLOSE!!! : )

              Thanks bud!

              • 4. Re: CFLOOP List
                cf_dev2 Level 1
                > each record has a list of division ids in a field called divverify

                Storing multiple values in a single field complicates code unnecessarily. Its also a bad design choice as it violates normalization rules.
                • 5. Re: CFLOOP List
                  rpmindllc Level 1
                  What would be a better solution?
                  • 6. Re: CFLOOP List
                    cf_dev2 Level 1
                    Sounds like you have a many-to-many relationship. Meaning one dealer(?) belongs to many divisions (?). Instead of storing the division ids in a single column like this

                    DealerID| DivVerify
                    1 | 2,4,18

                    You should store them in a separate table, with each dealer/divisionID in a separate row.

                    Table name: DealerDivision

                    DealerID, DivisionID
                    1 , 2
                    1 , 4
                    1 , 18

                    Then you could use a single query to retrieve your results instead of using cfloop.
                    • 7. Re: CFLOOP List
                      Dinghus Level 1
                      "Normalization"??? Say it aint so?


                      I'm glad I read to the end of the posts because I was about to say "NORMALIZE YOUR DATABASE!"
                      • 8. Re: CFLOOP List
                        Dinghus Level 1
                        Let me point out something else too.
                        In the first query you are grabbing everything "SELECT *" but you are only using seldealer.divverify You should only select what you are going to use. Saves time and bandwidth and memory.
                        • 9. Re: CFLOOP List
                          rpmindllc Level 1
                          This DIVverify is a "temp" field, which is only used if the dealer has not been assigned a division yet. What my goal was, is too put a list of division into one field, and then email each division in that field asking the division if this is there dealer. Once a dealer is claimed by a division, this field is wiped clean and never used again.

                          My second challenge is, how do I remove a specific id from this list, so that if the division says "nope, not my dealer", that I can remove their divisionid number from the list. If there was a way to determine the position of the list element, then I could use ListDeleteAt() function and tell it to delete that position...is there a way to determine list position for a specific list element?

                          I thought this would be the best and easiest way of working with these temp variables without creating another table.
                          • 10. Re: CFLOOP List
                            rpmindllc Level 1
                            ListFind() - found it!