2 Replies Latest reply on Jul 28, 2016 5:25 PM by user13337505

    Import Data Pump script seems to replace not append to table

    user13337505

      I have been using FDM for a while, but new to scripting outside of some pretty basic stuff.  I have a file which has a Closing Balance and the Current Year movement in 2 separate columns and I am trying to create a new line on the Import for the CY Movement, while still loading the Closing Balance values.  The issue I am having is my script is effectively replacing the Closing Balance data, instead of appending to it.  So if I run the Import with no script on the Amount, my Closing Balance rows are coming through, but as soon as I add the script the CY movement data comes through without the Closing Balance data.

       

      I have spent quite some time searching through forums and the like, but can't seem to find a solution to my issue.  Any help would be much appreciated.

       

      This is the script as is currently stands:

      '------------------------------------------------------------------

       

      Dim strWorkTableName

      Dim rsAppend

      Dim YTD_Value

      Dim strAccount

       

      'Get name of temp import work table

      strWorkTableName = RES.PstrWorkTable

       

      'Create temp table trial balance recordset

      Set rsAppend = DW.DataAccess.farsTable(strWorkTableName)

       

      YTD_Value = DW.Utilities.fParseString(strRecord, 11, 9, ",")

      strAccount = DW.Utilities.fParseString(strRecord, 11, 6, ",")

       

      If IsNumeric (YTD_Value) Then

         If YTD_Value <> 0 Then

               ' Create a new record, and supply it with its field values

               rsAppend.AddNew

               rsAppend.Fields("DataView") = "YTD"

               rsAppend.Fields("PartitionKey") = RES.PlngLocKey

               rsAppend.Fields("CatKey") = RES.PlngCatKey

               rsAppend.Fields("PeriodKey") = RES.PdtePerKey

               rsAppend.Fields("CalcAcctType") = 9

               rsAppend.Fields("Account") = strAccount

               rsAppend.Fields("Entity") = DW.Utilities.fParseString(strRecord, 11, 4, ",")

               rsAppend.Fields("ICP") = strAccount

               rsAppend.Fields("UD1") = strAccount & "_CY"

               rsAppend.Fields("UD2") = strAccount

               rsAppend.Fields("Amount") = YTD_Value

       

            'Append the record to the collection

            rsAppend.Update

       

         End If 'If YTD_Value <> 0 Then

      End If 'If IsNumeric (YTD_Value) Then

       

       

      'Close recordset

      rsAppend.close

        • 1. Re: Import Data Pump script seems to replace not append to table
          SH_INT

          Assuming that this is the entire script, your issue here is that you are not returning the Closing Balance value in the script which will be the value in strField. If your function was called InsertExtraValue you need to include a line at the end of your import script like:

           

               InsertExtraValue = strField

          1 person found this helpful
          • 2. Re: Import Data Pump script seems to replace not append to table
            user13337505

            Thanks for the response.  I must admit, I don't really get it, but it worked!!

             

            My assumption was the rsAppend would just add onto the load already there.

             

            From what I understood, you are suggesting is that I also need to add something into the script to get the original lines back in.  So if the Closing is in field 11 of 11, I would need to specifically pull that into the script as well.  So I added a field called Closing_Value

             

            Closing_Value = DW.Utilities.fParseString(strRecord, 11, 11, ",")

             

            Then added a line

            FunctionName = Closing Value

            Before the End Function

             

            All seems to have worked as I had hoped.

             

            Thanks again for your help.