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

    Import Data Pump script seems to replace not append to table


      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.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



         End If 'If YTD_Value <> 0 Then

      End If 'If IsNumeric (YTD_Value) Then



      'Close recordset


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

          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

            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.