4 Replies Latest reply on Jan 29, 2007 10:46 AM by reya276

    Obtain Multiple Form values and Insert

    reya276
      Hello,
      I'm trying to insert multiple value list passed through a form, but I can only accquired only one list. what I mean is I'm trying to pass two list on two different values as follow:

      ipa_code_ = "228,420,457"
      payor_code_ = "WC,HU,DMG,PC,VA"

      and below is the code that I'm trying to use to accomplish this:

      <cfif (username) NEQ "" AND (ipa_id_) NEQ "">
      <!---This loops through the ipa codes and inserts a record for each line--->
      <cfloop index="curLine" list="#ipa_id_#">
      <cfquery datasource="#request.dsn#">
      INSERT INTO Assign_ipa (ipa_id,hmoid,userid,username,PayorCode)
      VALUES ('#curLine#','#hmoid#','#userid#','#username#','#PayorCode#')
      </cfquery>
      </cfloop>
      <!---This tries to update the previously inserted values from above but it does not work, is trying to update the records with the payor codes---->
      <cfloop index="curLine" list="#PayorCode_#">
      <cfquery datasource="#request.dsn#">
      UPDATE Assign_ipa SET PayorCode = '#curLine#'
      WHERE userid = '#userid#'
      </cfquery>
      </cfloop>
      <cfset msg = "You successfully assigned IPAs to ("&#username#&")!">
      <cfelse>
      <cfset msg = "You NOT assigned IPAs to the users!">
      </cfif>

      So as you can see from the code above it will not work because every time I try to update the records only one value from the list is taken and updated.

      ipa_code hmoID userid username payor_code
      228 1 41 reya10276 VA
      420 1 41 reya10276 VA
      457 1 41 reya10276 VA

      Any ideas on how to accomplish this would be greatly appreciated, thanks.
        • 1. Re: Obtain Multiple Form values and Insert
          Dan Bracuk Level 5
          Your intent is not clear. If you start with these two lists,
          ipa_code_ = "228,420,457"
          payor_code_ = "WC,HU,DMG,PC,VA"

          what should your db records look like?
          • 2. Re: Obtain Multiple Form values and Insert
            reya276 Level 1
            My records should look like this:

            228 1 41 reya10276 WC
            420 1 41 reya10276 HU
            457 1 41 reya10276 DMG

            As oppose to what they are now which is:

            228 1 41 reya10276 VA
            420 1 41 reya10276 VA
            457 1 41 reya10276 VA

            What I need to do is select the IPAs(codes) and at the same time grab it's payorCode. Every IPA code has a payorCode so I just want to make sure it gets inserted along with the ipacode, so what I tried to do is just pass it as a hidden field but what I get is this:

            228 1 41 reya10276 WC,HU,DMG
            420 1 41 reya10276 WC,HU,DMG
            457 1 41 reya10276 WC,HU,DMG

            Since it's only doing the looping on the list of ipa_codes not the payorCodes
            <cfloop index="curLine" list="#ipa_code_#">
            • 3. Re: Obtain Multiple Form values and Insert
              Dan Bracuk Level 5
              If you start with these two lists,
              ipa_code_ = "228,420,457"
              payor_code_ = "WC,HU,DMG,PC,VA"

              and you want to end up with this:
              228 1 41 reya10276 WC
              420 1 41 reya10276 HU
              457 1 41 reya10276 DMG

              Do a loop from 1 to the listlen of ipa_code. Use the listgetat function inside that loop.
              • 4. Re: Obtain Multiple Form values and Insert
                reya276 Level 1
                Thank you so much that worked perfectly.

                <cfloop From = "1" TO= "#ListLen(ipa_id_)#" INDEX="Counter">
                <cfset ipa_id = #ListGetAt(ipa_id_, Counter)#>
                <cfset payorCode = #ListGetAt(payorCode_, Counter)#>
                <cfquery datasource="#request.dsn#">
                INSERT INTO Table(ipa_id,hmoid,userid,username,PayorCode)
                VALUES ('#ipa_id#','#hmoid#','#userid#','#username#','#payorCode#')
                </cfquery>
                </cfloop>