Solved

New Checklist

Posted on 2011-02-15
22
430 Views
Last Modified: 2013-12-24
All - I am trying to create a dynamic checklist using Coldfusion and Access.
The basic premis... I want a user to create a new checklist. They enter a project number, then a new checklist get's created for that projnum. I need all of my checklist items to be written into a table in my database that has a relationship to that projnum.
Do i need to loop over my checklist entries? Please advise.

Below is my action code. I kind of suck at CF.
<cfset na="N/A">

<cfquery datasource="projects" name="items">
Select * From tbl_checklist_items Where checklist_cat="Both"

<cfquery datasource="projects" name="newcachecklist">
INSERT INTO tbl_checklist 
(proj_num)
Values
('#form.projnum#')

INSERT INTO tbl_master_checklist
(checklist_id, checklist_item, checklist_status)
Values
(tbl_checklist.id, #items.checklist#, '#na#')

</cfquery>
</cfquery>

<cflocation url="/index.cfm">

Open in new window

Capture.JPG
0
Comment
Question by:bcrosby007
  • 9
  • 8
  • 5
22 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 34898364
Do i need to loop over my checklist entries?

If I'm understanding your structure correctly - no.  Just use a SELECT to insert the values from your template table (tbl_checklist_items) into tbl_master_checklist

Once you get it working, you should use cfqueryparam for all of the values.

<cftransaction>
     <cfquery datasource="projects" name="newcachecklist">
        INSERT INTO tbl_checklist (proj_num)
        VALUES ('#form.projnum#')
     </cfquery>
     <cfquery datasource="getID" name="newcachecklist">
         SELECT @@IDENTITY AS NewRecordID
     </cfquery>
     <cfquery datasource="addItems" name="newcachecklist">
           INSERT INTO tbl_master_checklist (checklist_id, checklist_item, checklist_status)
           SELECT #getID.NewRecordID, 'N/A'
           FROM    tbl_checklist_items
           WHERE  checklist_cat='Both'
     </cfquery>
</cftransaction>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34898375
Typo correction, this line:

         SELECT #getID.NewRecordID, 'N/A'

should be:

          SELECT #getID.NewRecordID#, checklist, 'N/A'
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34898383

Yes, you would need to loop over your checklist items to insert them into the tables.   The format is a bit more like I'm showing below.

We can help guide you through this project if you like, I am a bit confused by your table structure, can we start there?

Project - it seems this is the top level table that holds the project informaton

You have MASTER_CHECKLIST, CHECKLIST and CHECKLIST_ITEMS

Does this mean that there is a Master or General list of items that every project will use and that is stored in the MASTER?

Then the Project has it's COPY of the master checklist.

Perhaps a small example of the data?

Also, one tip - you should name the ID field for each table with the Table Name, not just ID.  It will become very confusing when you start joining tables.

Project_ID
CheckList_ID
etc..

<cfquery datasource="projects" name="addNewChecklist">
  INSERT INTO tbl_checklist  (proj_num)
  Values ('#form.projnum#')
</cfquery>

<cfloop .. for each checklist item ... >
  <cfquery datasource="projects" name="newcachecklist">
   INSERT INTO tbl_master_checklist 
    (checklist_id, checklist_item, checklist_status)
   Values
    (tbl_checklist.id, #items.checklist#, '#na#')
  </cfquery>
</cfloop>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34898406
I was thinking this would be a form with a list of items and checkboxes, perhaps you could describe what the form page would look like
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34898453
I am a bit confused by your table structure

I agree. I'd expect the MASTER table to be a template for all projects.  But I think some samples and maybe a screen shot of the form page would help clarify.
0
 
LVL 7

Author Comment

by:bcrosby007
ID: 34898490
The form page to add a new project would be simply a text box wth submit button. The submit passes the projnum variable to the action page where a single entry gets put in the tbl_checklist table. Then the tbl_master_checklist gets populated with the checklist_id from the tbl_checklist in every row as well as all items in the tbl_checklist_items.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34898627
Then the code in my 1st response should do what you need.

But .. if it were me, I'd rename the tables.  The name "Master" typically implies the table is a template of some sort.  In your schema it's the reverse.

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34898675
I agree, perhaps something like this:

MasterListItems
  - MasterItem_id

Projects
  - Project_id
ProjectList
  - ProjectList_Id
  - Project_Id  -- foreign Key toProject table
ProjectListItems
  - ListItem_id
  - ProjectList_id -- foreign key to list table
  - MasterItem_id -- foreign key to master checklist table


0
 
LVL 7

Author Comment

by:bcrosby007
ID: 34899097
Here is where i am now. Can you expand on the cfloop?
<cfset na="N/A">
<cfquery datasource="projects" name="newcachecklist">
INSERT INTO tbl_checklist 
(proj_num)
Values
('#form.projnum#');
</cfquery>

<cfquery datasource="projects" name="items">
Select * From tbl_checklist_items Where checklist_cat="Both"
</cfquery>

<cfquery datasource="projects" name="getnewprojinfo">
Select * from tbl_checklist where projnum='#form.projnum#'
</cfquery>

<cfloop >
<cfoutput query="getnewprojectinfo">
<cfquery datasource="projects" name="populatechecklist">
INSERT INTO tbl_master_checklist
(checklist_id, checklist_item, checklist_status)
Values
(getnewprojectinfo.id, #items.checklist#, '#na#')
</cfquery>
</cfoutput>
</cfloop>


<cflocation url="/index.cfm">

Open in new window

0
 
LVL 7

Author Comment

by:bcrosby007
ID: 34899101
First time trying a loop...
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34899149
You DON'T need a loop!  :)  Did you try the code I posted in my first response .. ?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 39

Expert Comment

by:gdemaria
ID: 34899178
Right, I was thinking you were doing something different, you don't need the loop.

Agx shows you how to insert into a table selecting from another table

based on your latest code, i think it may be something like this..

<cfquery datasource="projects" name="populatechecklist">
  insert into  tbl_master_checklist  (checklist_id, checklist_item, checklist_status)
  select getnewprojectinfo.id, #items.checklist#, '#na#'
  from tbl_checklist 
  where projnum='#form.projnum#'
</cfquery>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 34899286

Guys, barring any additional typos ;-) the code original code should do everything ... exactly as is.

The 1st query inserts your project

  INSERT INTO tbl_checklist (proj_num)
  VALUES ('#form.projnum#')

Open in new window


The 2nd query get's the ID of that record (ie autonumber value).  

     SELECT @@IDENTITY AS NewRecordID

Open in new window


The 3rd query copies all the records from your tbl_checklist_items table into  tbl_master_checklist

         INSERT INTO tbl_master_checklist (checklist_id, checklist_item, checklist_status)
         SELECT #getID.NewRecordID#, checklist, 'N/A'
         FROM    tbl_checklist_items
         WHERE  checklist_cat='Both'

Open in new window


Note: CFTRANSACTION is required !
0
 
LVL 7

Author Comment

by:bcrosby007
ID: 34899289
Ok. Here is where i am plus my error... I think I am close. Maybe...
<cfset na="N/A">
<cfquery datasource="projects" name="newcachecklist">
INSERT INTO tbl_checklist 
(proj_num)
Values
('#form.projnum#');
</cfquery>

<cfquery datasource="projects" name="items">
Select * From tbl_checklist_items Where checklist_cat="Both"
</cfquery>

<cfquery datasource="projects" name="getnewprojinfo">
Select * from tbl_checklist where proj_num='#form.projnum#'
</cfquery>


<cfquery datasource="projects" name="populatechecklist">
insert into  tbl_master_checklist  (checklist_id, checklist_item, checklist_status)
  select getnewprojectinfo.id, #items.checklist#, '#na#'
  from tbl_checklist 
  where proj_num='#form.projnum#'

</cfquery>




<cflocation url="/index.cfm">

Open in new window

Capture2.JPG
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34899303
Are my responses not showing up or something ... ?
0
 
LVL 7

Author Comment

by:bcrosby007
ID: 34899338
Error using the cftransaction code. ScreenShot
<cftransaction>
     <cfquery datasource="projects" name="newcachecklist">
        INSERT INTO tbl_checklist (proj_num)
        VALUES ('#form.projnum#')
     </cfquery>
     <cfquery datasource="getID" name="newcachecklist">
         SELECT @@IDENTITY AS NewRecordID
     </cfquery>
     <cfquery datasource="addItems" name="newcachecklist">
           INSERT INTO tbl_master_checklist (checklist_id, checklist_item, checklist_status)
           SELECT #getID.NewRecordID#, checklist, 'N/A'
           FROM    tbl_checklist_items 
           WHERE  checklist_cat='Both'
     </cfquery>
</cftransaction>


<cflocation url="/index.cfm">

Open in new window

0
 
LVL 7

Author Comment

by:bcrosby007
ID: 34899351
Sorry AGX. I am trying your way now.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34899363
The dsn is swapped with the query name. Change these lines:

     <cfquery datasource="projects" name="newcachecklist">
     <cfquery datasource="getID" name="newcachecklist">
     <cfquery datasource="addItems" name="newcachecklist">

To

     <cfquery datasource="projects" name="newcachecklist">
     <cfquery datasource="projects" name="getID">
     <cfquery datasource="projects" name="addItems">
0
 
LVL 7

Author Comment

by:bcrosby007
ID: 34899382
Jackpot. Worked perfectly.
Thanks!
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34899388
yes, try agx's code, it is correct
0
 
LVL 7

Author Closing Comment

by:bcrosby007
ID: 34899399
Super easy once i see the code.
Thanks!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34899449
You're welcome.  We finally got there in the end ;-)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now