This content has been marked as final. Show 10 replies
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#".
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?
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!!! : )
> 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.
What would be a better solution?
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
1 | 2,4,18
You should store them in a separate table, with each dealer/divisionID in a separate row.
Table name: DealerDivision
1 , 2
1 , 4
1 , 18
Then you could use a single query to retrieve your results instead of using cfloop.
"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!"
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.
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.
ListFind() - found it!