Dynamics Community Forum Thread Details

Hi Everyone,

I'm having trouble writing off the small balances within the Payment Applications module when utilizing Transaction Import.

It seems as if the macro is incorrectly interpreting the line in my Excel import file for this to occur, as I keep getting an error in my log file that reads "Sum of amount to apply and cash discount must not exceed adjusted document balance". This is an error that occurs when attempting to write off a small balance with a number that exceeds the document balance (i.e. if the Write Off Amount is entered as 26 when the Doc. Bal. is only 25).

Below is the macro I use, it

Hi Everyone,

I'm having trouble writing off the small balances within the Payment Applications module when utilizing Transaction Import.

Below is the macro I use, with the problem I'm experiencing in bold. It seems that it simply won't process level 3 data despite it being entered in the same manner in my Excel file as all of the other information (level 1 and level 2):

'ControlMacroType: VBAComplete
' VBComponent: ThisScreen, ComponentType: 100 **********************************************************
'08030 Control Macro
Sub ProcessImportLine( LevelNumber%, Retval% )
select Case LevelNumber
case TI_Start

call AliasConstant( "Level0", "Batch" )

call AliasConstant( "Level1", "Document" )

call AliasConstant( "Level2", "Transaction" )

call AliasConstant( "Level3", "Transaction" )

case 0 ' 0 of 3

    'Level 0 if of Type N
      ' cbatnbr is a key field for level 0
      ' Field mask is UUUUUU
      serr = SetObjectValue( "cbatnbr", ImportField(1) )

' cperpost is a Period Field
serr = SetObjectValue( "cperpost", ImportField(2) )

' cDepdate is a Date Field Type
serr = SetObjectValue( "cDepdate", ImportField(3) )

'Currently Disabled cstatus

      ' cbathandling is an Combo Box
      ' Values are H;Hold,B;Release Later,R;Release Now,N;No Action
      ' NOTE -- This field is a required field
      serr = SetObjectValue( "cbathandling", ImportField(4) )

'Currently Disabled ccrtot

serr = SetObjectValue( "cctrltot", ImportField(5) )

'Currently Disabled ccustid05

case 1 ' 1 of 3

    'Level 1 if of Type N
      ' ccustid is a key field for level 1
      ' Field mask is UUUUUUUUUU
      ' NOTE -- This field is a required field
      serr = SetObjectValue( "ccustid", ImportField(1) )

      ' cdoctype is a key field for level 1
      ' cdoctype is an Combo Box
      ' Values are PA;Payment ,CM;Credit Memo,PP;Prepayment
      ' NOTE -- This field is a required field
      serr = SetObjectValue( "cdoctype", ImportField(2) )

      ' crefnbr is a key field for level 1
      ' Field mask is UUUUUUUUUU
      ' NOTE -- This field is a required field
      serr = SetObjectValue( "crefnbr", ImportField(3) )

' cdocdate is a Date Field Type
serr = SetObjectValue( "cdocdate", ImportField(4) )

'Currently Disabled ccurrbal

serr = SetObjectValue( "corigdocamt", ImportField(5) )

      ' Field mask is UUUUUUUUUU
      ' NOTE -- This field is a required field
      serr = SetObjectValue( "cbankacct", ImportField(6) )

      ' Field mask is WW-WWW-WWW
      ' NOTE -- This field is a required field
      serr = SetObjectValue( "cbanksub", ImportField(7) )

'Currently Disabled cappltotal

'Currently Disabled cwrkgdocbal

'Currently Disabled ctotdisc

serr = SetObjectValue( "capplamt", ImportField(8) )

      ' cDocSortOrd(0) is an Option Button
      ' Value is                                                                                                    D to select button with caption "                                                                                        By D&ue Date"
      ' cDocSortOrd(1) is an Option Button
      ' Value is                                                                                                    R to select button with caption "                                                                                By &Reference Number"
      ' cDocSortOrd(2) is an Option Button
      ' Value is                                                                                                    T to select button with caption "                                                                                        By Doc T&ype"
      serr = SetObjectValue( "cDocSortOrd", ImportField(9) )

case 2 ' 2 of 3

    'Level 2 if of Type D
      ' crefnbr1 is a key field for level 2
      ' Field mask is UUUUUUUUUU
      serr = SetObjectValue( "crefnbr1", ImportField(1) )

' Field mask is XXXXXXXXXXXXXXXX
serr = SetObjectValue( "xproject", ImportField(2) )

'Currently Disabled ccpnyid

serr = SetObjectValue( "ctranamt", ImportField(3) )

serr = SetObjectValue( "cCuryUnitPrice", ImportField(4) )

'Currently Disabled cdocbal1

'Currently Disabled cdiscbal1

'Currently Disabled ccuryid

'Currently Disabled csalesordnbr

'Currently Disabled cdocdesc1

'Currently Disabled cdoctype1

'Currently Disabled cdocdate1

'Currently Disabled cdiscdate1

'Currently Disabled cduedate1

'Currently Disabled crefnbr05

'Currently Disabled cdocbal05

'Currently Disabled ccmmnpct05

'Currently Disabled ctranbatnbr05

'Currently Disabled cappltotal01

case 3 ' 3 of 3

    'Level 3 if of Type D
      ' Field mask is UUUUUUUUUU
      serr = SetObjectValue( "cwoacct", ImportField(1) )

' Field mask is WW-WWW-WWW
serr = SetObjectValue( "cwosub", ImportField(2) )

serr = SetObjectValue( "corigdocamt05", ImportField(3) )

'Currently Disabled ccustidtran05

'Currently Disabled cdoctype05

'Currently Disabled PICText1

'Currently Disabled cTranrefnbr05

case TI_Finish

End Select
End Sub
' The following shows the correct syntax to
' PRESS a button
' These object names are specific to this screen.
' Button object name cOK05, Caption &OK
' serr = SetObjectValue( "cOK05", "PRESS" )

' Button object name TemplateFormBtnPaste, Caption Paste
' serr = SetObjectValue( "TemplateFormBtnPaste", "PRESS" )

' Button object name TemplateFormBtnSave, Caption Save
' serr = SetObjectValue( "TemplateFormBtnSave", "PRESS" )

' Button object name TemplateFormBtnDelete, Caption Delete
' serr = SetObjectValue( "TemplateFormBtnDelete", "PRESS" )

' Button object name TemplateFormBtnClose, Caption Close
' serr = SetObjectValue( "TemplateFormBtnClose", "PRESS" )

' Button object name ApplyAmount, Caption Apply Am&ount
' serr = SetObjectValue( "ApplyAmount", "PRESS" )

' End VBComponent **********************************************************

If I'm interpreting this correctly, Column A Line 3 is the bank account, column B Line 3 is the project, and column C Line 3 is the write off amount. If the write off amount does not exceed the document amount, then I should not be getting the error "Sum of amount to apply and cash discount must not exceed adjusted document balance". An example of a log file detailing this error is below:

'System Message 8000: Start Processing 16:12:53

'System Message 8042: Transaction Import Processing Combine Edit and Update mode

'System Message 8040: Database Name RAAPP

'System Message 8011: ========Begin Processing of a level0 Data Line========

'level0,,,,N,,,,

'System Message 8005: New Record Requested

'System Message 8012: ========Successful Processing of a level0 Data Line========

'System Message 8011: ========Begin Processing of a level1 Data Line========

'level1,CAUPRO,PA,CR 001,2/28/2017,"4,545.00",10004,0,"4,545.00"

'System Message 8005: New Record Requested

'System Message 9: Item not found, please reenter.

'System Message 8002: Error In Field: cbanksub Value:

'System Message 8004: Data File Record/Line Number: 2 Column: 51

'level1,CAUPRO,PA,CR 001,2/28/2017,"4,545.00",10004,0,"4,545.00"

'System Message 8055: ========Error Processing level1 Data Line========

'System Message 8011: ========Begin Processing of a level2 Data Line========

'level2,143618,,"4,545.00 ",,,,,

'System Message 8005: New Record Requested

'System Message 8012: ========Successful Processing of a level2 Data Line========

'System Message 8011: ========Begin Processing of a level3 Data Line========

'level3,62705,40160310,25.00 ,,,,,

'System Message 8005: New Record Requested

'System Message 217: Sum of amount to apply and cash discount must not exceed adjusted document balance.

'System Message 8002: Error In Field: corigdocamt05 Value:

'System Message 8004: Data File Record/Line Number: 4 Column: 22

'level3,62705,40160310,25.00 ,,,,,

'System Message 8055: ========Error Processing level3 Data Line========

level0,,,,N,,,,

level1,CAUPRO,PA,CR 001,2/28/2017,"4,545.00",10004,0,"4,545.00"

level2,143618,,"4,545.00 ",,,,,

level3,62705,40160310,25.00 ,,,,,

'System Message 8030: The Number of Errors detected was 2

'System Message 8001: Completed Processing 16:13:00

Is there something I can do to allow this to work? Am I missing something in the macro code that is keeping level 3 data from processing? Is this not possible to accomplish via Transaction Import? Any feedback would be extremely helpful - thank you!