3 Replies Latest reply on Jan 14, 2013 2:50 PM by Sitaga214

    cfloop not looping as expected

    Leonard B Level 2

      Hello,

       

      CF = 8

      DB = MySQL

       

      Scenario:

      There are 43 employees who must complete quarterly required training. There are 12 first quarterly items to complete.

      I am attempting to load each of the required quarterly training items respective to each employee number, so that

      subsequent reporting can determine what required training has not been completed by each employee.

       

      Below is the code I have so far. When this is ran, it loops through the rsEmployeeNumber query and inserts 43

      quarterly training records. However, it is only picking up the first record of the rsQuarterlyTraining and not the

      subsequent 11 records.

       

      Final Goal:

      To query the required quarterly training requirements for a given employee and in this case display the 12

      quarterly training items for the first quarter of 2013 to see what has or has not been completed.

       

      What am I missing with my process?

       

      <cfquery name="rsEmployeeNumber" datasource=""> <== 43 records

          SELECT

              record_id, last_name, first_name, emp_id, emp_id_old, assigned_shift, password, administration, emp_rank, access_group, access_status, email_address, login, exp_date, site_management, training_records, med_cert_level, med_registry_number, manage_training_events

          FROM

              personnel

          WHERE

              assigned_shift = <cfqueryparam cfsqltype="cf_sql_varchar" value="A"> or

              assigned_shift = <cfqueryparam cfsqltype="cf_sql_varchar" value="B"> or

              assigned_shift = <cfqueryparam cfsqltype="cf_sql_varchar" value="C">

      </cfquery>

       

      <cfloop query="rsEmployeeNumber">

       

      <cfquery name="rsQuarterlyTraining" datasource=""> <== 12 records

          SELECT RecordID, QuarterYear, QuarterNumber, TrainingGroup, GroupCode, MaterialType, TrainingTitle, TrainingCode, TrainingFile

          FROM

              to_quarterly_training_test

          WHERE

              QuarterYear = <cfqueryparam cfsqltype="cf_sql_integer" value="2013"> and

              QuarterNumber = <cfqueryparam cfsqltype="cf_sql_integer" value="1"> and

              MaterialType = <cfqueryparam cfsqltype="cf_sql_varchar" value="Training">

      </cfquery>

       

      <cfquery name="rsUploadTraining" datasource=""> <== Should insert 516 records

      Insert Into training_completed_required_test (

          trng_year

          , trng_quarter

          , trng_title

          , emp_id)

      Values (

            <cfqueryparam cfsqltype="cf_sql_integer" value="#rsQuarterlyTraining.QuarterYear#">

          , <cfqueryparam cfsqltype="cf_sql_integer" value="#rsQ2uarterlyTraining.QuarterNumber#">

          , <cfqueryparam cfsqltype="cf_sql_varchar" value="#rsQuarterlyTraining.TrainingTitle#">

          , <cfqueryparam cfsqltype="cf_sql_varchar" value="#rsEmployeeNumber.emp_id#">

          )

      </cfquery>

       

      </cfloop>

        • 1. Re: cfloop not looping as expected
          Sitaga214

          Did you mean to loop though the rsQuarterlyTraining on the insert?

           

          If so you need to add a

           

          <cfloop query="reQuarterlytraining"> above the cfquery insert tag:

           

          <cfloop query="rsEmployeeNumber">

           

          <cfquery name="rsQuarterlyTraining" datasource=""> <== 12 records

              SELECT RecordID, QuarterYear, QuarterNumber, TrainingGroup, GroupCode, MaterialType, TrainingTitle, TrainingCode, TrainingFile

              FROM

                  to_quarterly_training_test

              WHERE

                  QuarterYear = <cfqueryparam cfsqltype="cf_sql_integer" value="2013"> and

                  QuarterNumber = <cfqueryparam cfsqltype="cf_sql_integer" value="1"> and

                  MaterialType = <cfqueryparam cfsqltype="cf_sql_varchar" value="Training">

          </cfquery>

           

          <cfloop query="rsQuarterlyTraining">


          <cfquery name="rsUploadTraining" datasource=""> <== Should insert 516 records

          Insert Into training_completed_required_test (

              trng_year

              , trng_quarter

              , trng_title

              , emp_id)

          Values (

                <cfqueryparam cfsqltype="cf_sql_integer" value="#rsQuarterlyTraining.QuarterYear#">

              , <cfqueryparam cfsqltype="cf_sql_integer" value="#rsQ2uarterlyTraining.QuarterNumber#">

              , <cfqueryparam cfsqltype="cf_sql_varchar" value="#rsQuarterlyTraining.TrainingTitle#">

              , <cfqueryparam cfsqltype="cf_sql_varchar" value="#rsEmployeeNumber.emp_id#">

              )

          </cfquery>

          </cfloop>

          </cfloop>

           

           

          /bill

          • 2. Re: cfloop not looping as expected
            Leonard B Level 2

            Thanks Bill,

             

            I knew it had to be something simple. Sometimes a person just stares too much at something - lol.

             

            Leonard B

            • 3. Re: cfloop not looping as expected
              Sitaga214 Level 1

              Glad it was helpful!  Good luck!