CFLOOP List

[rpmindllc]

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

[tslow · Accepted solution]

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#".

-Tim

[rpmindllc]

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?

[rpmindllc]

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!

[cf_dev2]

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

[rpmindllc]

What would be a better solution?

[cf_dev2]

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.

[rpmindllc]

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.

[rpmindllc]

ListFind() - found it!

[Dinghus]

"Normalization"??? Say it aint so?

LOL

I'm glad I read to the end of the posts because I was about to say "NORMALIZE YOUR DATABASE!"

[Dinghus]

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.