Skip to main content
G
GwenH
Inspiring
October 24, 2008
Question

Extract Boilerplate Text From Second Table

  • October 24, 2008
  • 4 replies
  • 666 views
I have a link in an email message that passes a URL parameter to a form. That form has a CFQUERY statement that pulls one single record from "MyDatabase", "Customers". On the same page, I'd like to display all the records from "BoilerParagraphs" of MyDatabase. The records in Customers are not related to BoilerParagraphs. Customers is names, emails, phone numbers, etc. of people. BoilerParagraphs is simply 10-15 boilerplate paragraphs. I want to display the entire list of boilerplate paragraphs on my form, then check Yes or No option buttons next to each paragraph.

After I submit the form, I want to update the record selected by the URL parameter in Customers, which has fields titled "para1" "para2", etc. I want to add to this record either the value 0 (for No) or 1 (For yes) for each paragraph displayed on the form.

So basically, the form would have to query Customers and BoilerParagraphs, then display a single specific record from Customers and all records from BoilerParagraphs. But, update Customers based on Yes/No values selected for records output from BoilerParagraphs. I'm pretty sure this means that the records in BoilerParagraphs will need a field, let's say "paraNo", that contains text like "para1", "para2", etc. Then the relevant column headings in Customers will need to be titled "Para1", "Para2", etc.

I am hoping that I'm making some sense, and that someone will have pity on me this Friday afternoon and share some ***basic**** pseudocode. I'm a CF noob who hasn't worked her way up to the fancy stuff yet.

Thanks,
GwenH

    This topic has been closed for replies.

    4 replies

    -
    -__cfSearching__-
    Inspiring
    October 25, 2008
    GwenH wrote:
    > Sorry, you misunderstood.

    Probably. I am not real clear on the layout. It just sounded like you were going to create a wide table with 10-20 columns all representing the same thing: "prop1", "prop2", ..., "propX", which is often the wrong approach.
    G
    GwenHAuthor
    Inspiring
    October 24, 2008
    Thanks to everyone who responded. My problem was figuring out how to structure what I wanted to achieve. I think what I'll do is add columns to the Customers table, then just put the boilerplate paragraphs in separate CFM files by themselves. Then when the customer views their report, I'll use this.
    <cfif #para1# = 1>
    <cflinclude template="para1a.cfm">
    <cfelse>
    <cflinclude template="para1b.cfm">
    </cfif>

    Thanks for your help in thinking this through.

    GwenH
    -
    -__cfSearching__-
    Inspiring
    October 25, 2008
    > I think what I'll do is add columns to the Customers table

    Why do you need separate columns? Can a customer can select one paragraph or multiple paragraphs? If only one, just store the paragraphs in a table and give the table an ID. Then store the selected paragraph ID in the Customer table and use a simple JOIN to retrieve the information. If they can select multiple paragraphs, I would use a separate table as mentioned above.



    G
    GwenHAuthor
    Inspiring
    October 25, 2008
    Sorry, you misunderstood. The customers aren't the ones doing the paragraph selecting. I am. I'm setting up a form to use to create custom reports for potential customers by outputting selected boilerplate paragraphs into a HTML report on a website. The report consists of a 12-point Yes/No checklist, and about 8 paragraphs. The first 12 points are all Yes/No answers. The 8 paragraphs will not all be displayed on a potential customer's report - only those I select.

    Sorry for the confusion,
    GwenH
    -
    -__cfSearching__-
    Inspiring
    October 24, 2008
    Assuming the correct variables exist, yes.

    Aside from the question of correct table structure, what is the issue you are having? Is the code generating an error, or are you unsure how to structure it?
    -
    -__cfSearching__-
    Inspiring
    October 24, 2008
    > Customers, which has fields titled "para1" "para2", etc

    If there is a many-to-many relationship between Customers and "BoilerParagraphs", the information should be stored in a separate table. Each record containing a CustomerID and Paragraph ID. So if Customer A selected paragraphs 1, 6, and 8. The table would contain three records:

    CustomersBoilderParagraphs
    CustomerID, BoilderParagraphID
    22 (Customer A), 1 (Paragraph 1)
    22 (Customer A), 6 (Paragraph 6)
    22 (Customer A), 8 (Paragraph 8)

    http://en.wikipedia.org/wiki/Database_normalization
    G
    GwenHAuthor
    Inspiring
    October 24, 2008
    Yes, but can you do two cfquery and one cfinsert statements on the same page?

    GwenH
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices