"CFMail to" based on code matches

[newportri]

Greetings

I have a form that an administrator fills out that has fields for up to 10 numeric inputs (code numbers).

If one of the inputed values matches that value (code) contained in customer's record, an email gets generated to that customer.

Each customer can have up to 10 codes - there is a field in each of their records for code 1, 2 etc. There is always at least one code, but that may be it.

I'm doing:

The form:

<p>Code 01: <input type="text" name="code_01" size="6" maxlength="6"></p>
<p>Code 02: <input type="text" name="code_02" size="6" maxlength="6"></p>

etc.

The action:

<cfquery name="emailData" datasource="customers">
SELECT customer_ID, customer_email, vendor_cccode_01, vendor_cccode_02, vendor_cccode_03, vendor_cccode_04, vendor_cccode_05,
vendor_cccode_06, vendor_cccode_07, vendor_cccode_08, vendor_cccode_09, vendor_cccode_10
FROM main
</cfquery>

then:

<cfloop query="emailData">

<cfmail to="#emailData.customer_email#" etc.

So if the customer only has one code, that's fine and it works.

I have tried using

WHERE form.code_01=vendor_cccode_01
OR etc,do the mailing

and

<cfif #form.code_02# IS NOT "">OR etc., do the mailing

and

<cfif emailData.vendor_cccode_01 EQ "#form.code_01#" OR emailData.vendor_cccode_02 EQ "#form.code_02#">

do the mailing

Can't get anything to work.

Any help would be appreciated

Thanks

[JR__Bob__Dobbs-qSBHQ2]

I suggest you change the way you are storing the codes per your previous post. It would make queries easier and you could add more then 10 codes per customer.
http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=1&catid=7&threadid=1320305&high...



To use your existing structure see code sample

[newportri]

Thanks so much- I decided that rather than having a field hold comma-delimited data, I would go this route- i.e. create 9 more fields in each record to hold up to 10 codes- I was a little unclear on your first solution- and I'm under the gun.

Yes- each customer has exactly one row in main table and yes, form field code_01 corresponds to data in field vendor_cccode_01- so I think I can get this to work.

Thanks again.

[Dan_Bracuk]

quote:

---

Originally posted by: newportri
Thanks so much- I decided that rather than having a field hold comma-delimited data, I would go this route- i.e. create 9 more fields in each record to hold up to 10 codes- I was a little unclear on your first solution- and I'm under the gun.

Yes- each customer has exactly one row in main table and yes, form field code_01 corresponds to data in field vendor_cccode_01- so I think I can get this to work.

Thanks again.

---

You may be under the gun but it takes less time to do something well than to do it twice. You are not doing it well.

A better approach would be to set up a one to many relationship between customers and codes. The customer_code table, whatever you call it, would have one record per code per customer. This gives you a lot more flexibility than you currently have.

On the bright side, you were smart enough to not store lists.

[newportri]

Hello again.

If I try to process, I get:

Invalid data '' for CFSQLTYPE CF_SQL_INTEGER



The fields are all set to number/long integer.

[JR__Bob__Dobbs-qSBHQ2]

You will need to account for a form field being empty by treating this as a NULL value

See sample and cfqueryparam documentation
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html#1102474

[newportri]

I agree- but what it comes down to now is the administrator will physically look up a code number from the list (see:

http://www.cityofnewport.com/vendor/relationships.html

)

which has no relation to the main table.

They then enter the appropriate codes on a form, which emails the vendors with the codes entered.

If you can get that link above- let me know where I went wrong...

Keep in mind there are 8000 codes, 260 general category codes which the admin looks up, and about 2500 vendors.

[Dan_Bracuk]

Instead of putting the 10 fields in the main table, add another table called vendor_code. The primary key will have at least two fields, vendor_id and ccode_id. A possible third one for the primary key will indicate whether or not the combination is active. Add other fields as required.

It's a standard many to many relationship. A code can apply to more than one vendor and a vendor can have more than one code.

You may consider renaming your main table to vendor.

[newportri]

Thanks again for your reply.

I understand that I would need to create a "junction" table that has its own key field, the vendor's ID and code ID foreign keys, but now this table needs to contain all the matching vendor/code associations i.e several thousand records?

Thanks - hope you had a great Holiday.

[JR__Bob__Dobbs-qSBHQ2]

quote:

---

Originally posted by: newportri
Thanks again for your reply.

I understand that I would need to create a "junction" table that has its own key field, the vendor's ID and code ID foreign keys, but now this table needs to contain all the matching vendor/code associations i.e several thousand records?

Thanks - hope you had a great Holiday.

---

That is correct. See samples posted by myself and Azadi in one of your previous posts.


http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=1&catid=7&threadid=1320305&high...