• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

CFMail to goup by form field code numbers

New Here ,
Dec 05, 2007 Dec 05, 2007

Copy link to clipboard

Copied

Greetings

I have a situation where a user fills out a form with 1 to 10 code numbers (Commercial Commodity Codes), which triggers a CFMAIL to all Vendors with those codes.

Would a CFQUERY that had:

SELECT cccategory_ID, ccc_cat_num
FROM cccodes_categories
WHERE ccc_cat_num LIKE '%#form.code_01#%'
OR ..... etc. etc.

be the approach here?

I would need to:

<cfmail to = "vendor@whatever.com"

all the vendors with either 1 or several of the form field codes....</cfmail>

Thanks in advance

rinorman
TOPICS
Advanced techniques

Views

1.1K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
Dec 05, 2007 Dec 05, 2007

Copy link to clipboard

Copied

See sample attached.


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 06, 2007 Dec 06, 2007

Copy link to clipboard

Copied

Thanks so much for your help- I will try this today and see if I can get it to mail out..

regards

rinorman

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 06, 2007 Dec 06, 2007

Copy link to clipboard

Copied

quote:

Originally posted by: newportri
Greetings

I have a situation where a user fills out a form with 1 to 10 code numbers (Commercial Commodity Codes), which triggers a CFMAIL to all Vendors with those codes.

Would a CFQUERY that had:

SELECT cccategory_ID, ccc_cat_num
FROM cccodes_categories
WHERE ccc_cat_num LIKE '%#form.code_01#%'
OR ..... etc. etc.

be the approach here?

I would need to:

<cfmail to = "vendor@whatever.com"

all the vendors with either 1 or several of the form field codes....</cfmail>

Thanks in advance

rinorman

Are your users going to have to type out these codes or are you making it easy for them? If the latter, you don't need the LIKE keyword in your query. You can use

where ccc_cat_num in ( <cfqueryparam list="yes" )
instead.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 06, 2007 Dec 06, 2007

Copy link to clipboard

Copied

Thanks for all the replies.

Unfortunately, there are 8500 + codes to choose from. I have condensed this to about 500 general categories that the user (administrator) will choose from and enter manually. A vendor can have more than one CCCode, so that field in each vendor's record contains a comma-delimited list of from 1 to as many as 10 code numbers.

Basically this is a Vendor bidding application. When a job or service needs a bid, the admin sends out the bid info to each vendor that has the appropriate code.

There are + - 2000 vendors in the system.

Bob Dobb's solution looks like the approach I will need to take, but the comma-delimited list may be an issue?

HTH

Thanks again

rinorman

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 06, 2007 Dec 06, 2007

Copy link to clipboard

Copied

I got this to work- I'm sure there is a simpler way or a different way to do it- but...

<cfquery name="emailData" datasource="mydata">
SELECT vendor_email, vendor_cccodes
FROM main
WHERE vendor_cccodes LIKE '%#form.code_01#%'
OR vendor_cccodes LIKE '%#form.code_02#%'
OR vendor_cccodes LIKE '%#form.code_03#%'
OR vendor_cccodes LIKE '%#form.code_04#%'
OR vendor_cccodes LIKE '%#form.code_05#%'
OR vendor_cccodes LIKE '%#form.code_06#%'
OR vendor_cccodes LIKE '%#form.code_07#%'
OR vendor_cccodes LIKE '%#form.code_08#%'
OR vendor_cccodes LIKE '%#form.code_09#%'
OR vendor_cccodes LIKE '%#form.code_10#%'
</cfquery>

<cfloop query="emailData"> <!--- loop thru vendors and send one email to each --->

<cfmail to="#emailData.vendor_email#"
from="admin@whatever.com"
subject="Available Bid"
server = "emailsrv.whatever">#Form.bid_ID# is Available</cfmail>
</cfloop>

again, the "vendor_cccodes" field, in this example, is a comma-delimited list of from 1 to as many as 10 code numbers.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Dec 06, 2007 Dec 06, 2007

Copy link to clipboard

Copied

> the "vendor_cccodes" field, in this example, is a comma-delimited list of from
> 1 to as many as 10 code numbers.
> WHERE vendor_cccodes LIKE '%#form.code_01#%'

You mean vendor_ccccodes is a csv list like: ABC, EFG, HIJ, ... ??

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 06, 2007 Dec 06, 2007

Copy link to clipboard

Copied

newportri wrote:
> A vendor can have more than one CCCode, so that field in each
> vendor's record contains a comma-delimited list of from 1 to as many
as 10 code
> numbers.
>


yep, i am afraid that's what they mean!!! omg...

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 07, 2007 Dec 07, 2007

Copy link to clipboard

Copied

RE: omg

so would that be "omg, this guy has no idea how to set up a database" or "omg since I'm an expert, I'll impart some of my vast knowledge upon him"?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 07, 2007 Dec 07, 2007

Copy link to clipboard

Copied

no, it was just an expression of certain level of disbelief and
astonishment at the choice data model.
it is almost always NOT the way to model your data. lists of values in
db fields pretty much defy the purpose of a db, and make it hard to work
with data and/or your db and db connections code very inefficient, which
all leads to poor db and website performance.

if you know what a 3rd normal form is, and if a comma-delimited list of
values was your choice of data model, i would like to hear why...
if you do not know what 3rd normal form is, may i suggest you look into
that asap...


---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 07, 2007 Dec 07, 2007

Copy link to clipboard

Copied

There are over 8500 Commercial Commodity Codes (all numeric).

A Vendor may fall under several.

Each Vendor record needs to include one or all the CCCodes that applies to them.

There is a CCCode lookup table that contains all the codes.

Presently the main Vendor table has a field related to the actual code number from the lookup table, although each code of course has an auto-num primary key as well.

Thanks



Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
Dec 07, 2007 Dec 07, 2007

Copy link to clipboard

Copied

I agree with the previous posters that using a comma delimited list is not the most ideal database design.

You might consider structure like the attached sample.
Note the sample written for Microsoft SQL Server syntax, your syntax may vary.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 07, 2007 Dec 07, 2007

Copy link to clipboard

Copied

cccodes table
cccode_id [PK; autonumber/identity/aint auto_increment]
cccode_code [int/number]
(if all cccodes are by definition unique, you can just have cccode_code
as PK field)

vendors table
vendor_id [PK; autonumber/identity/aint auto_increment]
... (other fields) ...

vendor_codes table
A)
vendor_code_id [PK; autonumber/identity/aint auto_increment]
vendor_id [int/number; FK to vendor_id in vendors table]
cccode/cccode_id [int; FK to cccode table]

or B)
vendor_id [int, FK]
cccode_id [int, FK]
PRIMARY index on both fields

hth

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 07, 2007 Dec 07, 2007

Copy link to clipboard

Copied

Thanks!

I will attempt to use this method.

Just a little history: I started using Allaire ColdFusion version 4.0 in 1998, right after they took the space in the name out-

Have developed hundreds of DBs- mostly all simple and all Access, and all work great for what they are being used for.

At 57 y.o., I still learn something new everyday- I'm graduating to SQL Server next month!

Thanks Again.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
Dec 07, 2007 Dec 07, 2007

Copy link to clipboard

Copied

Bear in mind that the query you are using could result in unintended results.

SELECT vendor_email, vendor_cccodes
FROM main
WHERE vendor_cccodes LIKE '%#form.code_01#%'

If form.code_01 == "201" records for the following CCC_codes will be returned "201", "1201", "2013", etc.
If this is not what you intend you should rework your query and/or database structure.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 07, 2007 Dec 07, 2007

Copy link to clipboard

Copied

LATEST
Thanks again for everyone's input.

For your enjoyment, I have put up an image here:

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

This may be an overly-simplistic structure in your view- but for what it needs to do, it does now except for the issue of matching vendor codes to the code lookup table without using a comma-delimited (bad) method in the Vendor (main) table.

Bob is correct that using the "LIKE" would certainly be a problem.

There must be a simple solution since each code is unique and numeric?

Have a great weekend...

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation