2 Replies Latest reply on Aug 10, 2015 7:28 AM by SH_INT

    FDMEE - Import Script - Separate Columns for DR/CR Balances

    2737489

      I am trying to import a pipe delimited file in FDMEE (v11.1.2.4) that has separate amount columns for debits and credits.  I need to subtract the credit column from the debit column to get the amount in the proper trial balance format.  A sample set of records is illustrated below:

       

      Acct    |  Desc                     | Debit         | Credit

      1000    |  Cash in Bank         |   5,000.00  |

      1005    |  Petty Cash             |      100.00 |

      2000    |  Accounts Payable  |                  |  1,250.00

       

       

      I want to use a jython import script to split the record, then use column 3 and 4 to create the net balance for use in FDM.  I am able to split the record and pull the appropriate columns using the split function.  This issue I am having is when I attempt to subtract column 3 from column 4.  It appears as though the script is interpreting the fields as strings instead of numbers.  I have tried various ways of stripping out spaces/commas, etc., and converting it using the decimal and int functions but nothing seems to work.

       

      Is there a simple way to accomplish this?

       

      Thanks for any assistance.

        • 1. Re: FDMEE - Import Script - Separate Columns for DR/CR Balances
          Dayalan Punniyamoorthy

          Well i believe your should be converting it to numeric, this is the code i had for a similar requirement.

           

           

          ----------------------------------------------------------------------------------------------------------------------------------------------------------

          Dim myDebit

          Dim myVar1

          Dim myCredit

          Dim myVar2

           

          myVar1 = DW.Utilities.fParseString(strRecord, 10, 5, ",")

          myVar2 = strField

           

          If myVar1 ="" Then

          myVar1 = 0

          End If

           

          If myVar2 ="" Then

          myVar2 = 0

          End If

           

          'To avoid Type Mismatch error for CDbl, make sure first row of data file

          'starts with data only, not the column names...!!!....or else you can use

          'IsNumeric Function To avoid first row as below

          If IsNumeric(myVar1) Then

            myDebit = CDbl(Trim(myVar1))

            myCredit = CDbl(Trim(myVar2))

          End If

           

          Crown_GetAmountFinal = myCredit-myDebit

           

          End Function

          ----------------------------------------------------------------------------------------

          Note: This was on VB Script for your reference.

           

          Regards,

          Dayalan P.

          • 2. Re: FDMEE - Import Script - Separate Columns for DR/CR Balances
            SH_INT

            Converting the string to a numeric data type is definitely the right approach. Are you using Jython or VB Script? Can you paste an example of your failing code along with any error messages you are getting?