15 Replies Latest reply on Dec 7, 2007 9:50 AM by newportri

    CFMail to goup by form field code numbers

    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
        • 2. Re: CFMail to goup by form field code numbers
          newportri Level 1
          Thanks so much for your help- I will try this today and see if I can get it to mail out..

          regards

          rinorman
          • 3. Re: CFMail to goup by form field code numbers
            Dan Bracuk Level 5
            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.
            • 4. Re: CFMail to goup by form field code numbers
              newportri Level 1
              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
              • 5. Re: CFMail to goup by form field code numbers
                newportri Level 1
                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.

                • 6. Re: CFMail to goup by form field code numbers
                  cf_dev2 Level 1
                  > 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, ... ??
                  • 7. Re: CFMail to goup by form field code numbers
                    Level 7
                    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
                    • 8. Re: CFMail to goup by form field code numbers
                      newportri Level 1
                      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"?
                      • 9. Re: CFMail to goup by form field code numbers
                        Level 7
                        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
                        • 10. Re: CFMail to goup by form field code numbers
                          newportri Level 1
                          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



                          • 11. CFMail to goup by form field code numbers
                            JR "Bob" Dobbs-qSBHQ2 Level 3
                            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.

                            • 12. Re: CFMail to goup by form field code numbers
                              Level 7
                              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
                              • 13. Re: CFMail to goup by form field code numbers
                                newportri Level 1
                                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.
                                • 14. Re: CFMail to goup by form field code numbers
                                  JR "Bob" Dobbs-qSBHQ2 Level 3
                                  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.
                                  • 15. Re: CFMail to goup by form field code numbers
                                    newportri Level 1
                                    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...