AR Receipt - Special / invalid characters in AR check / receipt field causing Bank Reconciliation issue

SUGGESTED

A user entered a '[' within text in the AR Receipt Check/Receipt No. field which then caused an issue in the Bank Reconciliation. We are on version 2014 (300 Premium ERP) (Build 1) (Product Update 1).
A posting by Connie H. in Sage Ideas database titled "AR check / receipt field should not allow special characters" indicates there is a hot fix to correct this. Can anyone confirm this?
Any thoughts on whether reversing the AR Receipt Entry and redoing would fix the fault?

  • 0
    It's an old, unfixed bug. Hopefully reversing it works. I've had to fix this three times for clients, you have to edit IDRMIT in BKTRAND, ARRRH, ARPJD, AROBL, ARPJH, AROBP, ARTCR, and AROBS.
  • 0 in reply to Jay Converse Acumen
    Thanks Jay - surprising this is unfixed!
  • 0 in reply to Andrew789
    SUGGESTED
    There is an sql script called fixbadcharifsafemacro.zip
  • 0

    A user entered a special character today (a single quote mark) in the AR Receipt Check/Receipt No. field which then caused an issue in the Bank Reconciliation. We are on version 2018 (300 Premium ERP) (Build 30) (Product Update 3).  

    This is a known bug - how do we get this fixed by Sage?  Connie H has already logged this on the Sage 300 Ideas forum, noting that it is a software bug.

    Erzsi Institorisz - how do we get Sage to fix this bug? 

  • 0 in reply to Andrew789

    You could either ask your Sage 300 Business Partner or Sage Support (in certain regions) to walk you through fixing it, or, if you or a colleague is confident with SQL, log into MSSQL, select your company database then locate and fix the receipt by running:

    select * from bktrand where idremit like'%`%'

    (This might identify your transaction right away - replace the reverse quote between the %'s if different but in all cases I've seen its been a reverse quote ( ` ).

    OR

    Identify the bank code, the date and the serial of the deposit and run:

    select * from bktrand where bank = 'your bank code' and dateremit = 'yyyymmdd' and serial = 'the serial number'

    (just an example, update before running)

    Either of these should confirm the bad transaction - look for a row with the reverse quote ` in the idremit field. Then work out what the idremit should be (should be obvious as there will be a missing number in the sequence between other similar rows).  Also write down the line number of the identified transaction.  Get plenty of criteria for ensuring you update the correct idremit record.  Once you have that worked out, run the following to fix it (I'll use a sample of real data in the example):

    update bktrand
    set idremit = '000009376-00004'
    where bank = 'OPER'
    and dateremit = '20240118'
    and serial = '228842'
    and line = '4'

    (just an example, update before running)

    All the best...Tim