Error: Cannot create a record in Balance (CustVendTmpOpenTransBalances). Currency: , 0,00.The record already exists

Question Status

Suggested Answer
Paula Marie Dayaon asked a question on 3 Nov 2011 7:21 PM

Hi,

This is the error we got from the AP module.

This error only occurs when a specific Vendor is being used. There are no duplicate records in the table VendTransOpen and it is very unlikely that a recodid from VendOpentrans is being inserted twice into CustVendTmpOpenTransBalances because the process works fine when using other Vendor Accounts.

 

Can you guys give me some insights on how to solve this?

Thanks! :)

 

 

Reply
Suggested Answer
Einar Lárusson responded on 18 Nov 2011 7:00 AM

In the add method of the class CustVendOpenTransBalancesManager i added this code

if (tmpBalances.TransOpenRecId)

       tmpBalances.insert();

The system was trying to add a record to the t,p tabel with the TransOpenRecId = 0.

Why that happens i don't know but i am trying to figure that out, but this is a temp solution so that it is possible to open the form at least

Hope this helps

Einar

epexplorer.blogspot.com

Reply
RenBee responded on 20 Nov 2011 4:34 PM

Hi Einar,

   We inserted the code in classes\CustVendOpenTransBalancesManager\add but the same error coming out, in addition to this the debugger coming out without any breakpoints set. The debugger pointing on classes\CustVendOpenTransBalancesManager\add   Debug::assert(_transExchRate != 0);

Reply
Suggested Answer
Einar Lárusson responded on 21 Nov 2011 12:41 AM

Hi

Then you should put the if statement around the whole code block, then nothing is done in case of there is no record to insert into the tmp tabel.

Let me know how that works

Regards

Einar

Reply
RenBee responded on 21 Nov 2011 10:54 PM

I try to insert the code,if statement in the whole block of codes but the same error is coming out..The debugger is pointing in every related codes that has a Debug::assert(_transExchRate != 0);..

Reply
Suggested Answer
Einar Lárusson responded on 22 Nov 2011 12:13 AM

Hi

Then you are not haveing the exact same problem as we had. We had a problem that the system was trying to insert revords with 0 as the transrecid and we got the error the second time the system tryed to insert a record with transrecid = 0.

This offcourse breaks the unique index on the table and we get the error that the record allready exists.

You problably need to do a check on the transExchRate since that is what is causing the debugger to dtop.

Regards

Einar

Reply
Suggested Answer
RenBee responded on 22 Nov 2011 12:46 AM

May i know how did you fixed your error?Just for reference.

Einar,

 The form is temporarily OK without an error. what I did is, I comment out the code: and add your suggested code.

/*

   // Assert that the exchange rate values passed in are not zero as they may be used in calculations as divisors

   Debug::assert(_transExchRate != 0);

   Debug::assert(_transToMstExchRate != 0);

   Debug::assert(_transMstToDisplayMstExchRate != 0);

   Debug::assert(_mstToDisplayExchRate != 0);

   tmpBalances.TransCompany = _transCompany;

   tmpBalances.TransOpenRecId = _transOpenRecId;

   tmpBalances.TransAmount = _transAmount;

   tmpBalances.EstimatedCashDiscAmount = _estimatedCashDiscAmount;

   tmpBalances.TransCurrency = _transCurrency;

   tmpBalances.TransDate = _transDate;

   tmpBalances.TransExchRate = _transExchRate;

   tmpBalances.TransToMstExchRate = _transToMstExchRate;

   tmpBalances.TransMstToDisplayMstExchRate = _transMstToDisplayMstExchRate;

   tmpBalances.MstToDisplayExchRate = _mstToDisplayExchRate;

   tmpBalances = this.calculateBalances(tmpBalances);

   tmpBalances.insert(); */

   if (tmpBalances.TransOpenRecId)

      tmpBalances.insert();

Reply
Suggested Answer
Einar Lárusson responded on 22 Nov 2011 3:21 AM

Hi

The only thing that i did was to add the if statement like this

if (tmpBalances.TransOpenRecId)

     tmpBalances.insert();

Then the system is not inserting empty records which was the root for our problem => the system tried to insert two records with the TransOpenRecId = 0.

If you comment out the code like you do then you will not get anything in the tmp table.

This is how the code looks like

  // Assert that the exchange rate values passed in are not zero as they may be used in calculations as divisors

  Debug::assert(_transExchRate != 0);

  Debug::assert(_transToMstExchRate != 0);

  Debug::assert(_transMstToDisplayMstExchRate != 0);

  Debug::assert(_mstToDisplayExchRate != 0);

  tmpBalances.TransCompany = _transCompany;

  tmpBalances.TransOpenRecId = _transOpenRecId;

  tmpBalances.TransAmount = _transAmount;

  tmpBalances.EstimatedCashDiscAmount = _estimatedCashDiscAmount;

  tmpBalances.TransCurrency = _transCurrency;

  tmpBalances.TransDate = _transDate;

  tmpBalances.TransExchRate = _transExchRate;

  tmpBalances.TransToMstExchRate = _transToMstExchRate;

  tmpBalances.TransMstToDisplayMstExchRate = _transMstToDisplayMstExchRate;

  tmpBalances.MstToDisplayExchRate = _mstToDisplayExchRate;

  tmpBalances = this.calculateBalances(tmpBalances);

  if (tmpBalances.TransOpenRecId)

     tmpBalances.insert();

Regards

Einar

Reply
RenBee responded on 22 Nov 2011 3:30 PM

Hi,

  Thanks for the help Einar, Its now working.

Reply
Suggested Answer
Kenneth Damsgaard responded on 10 Feb 2012 2:02 AM

You most likely have data damage (Orphaned records) in the specTrans table.

you can check it with this small job below...:

___________________

static void Check_Orphans_Spectrans(Args _args)
{
    SpecTrans       specTrans;
    CustTransOpen   custTransOpen;
    counter         i;
    ;

    while select forupdate * from SPECTRANS where SpecTrans.RefTableId == 865 // 866 for Debtors
    {
        select firstonly * from CustTransOpen where CustTransOpen.RecId == SpecTrans.RefRecId;

        if(!CustTransOpen.RecId)
        {
            info(strFmt("%1 balance : %2",SpecTrans.RefRecId,SpecTrans.Balance01));
            //SpecTrans.delete();
            i++;
        }
    }
    info(strFmt("records deleted : %1",i));
}

___________________

If you find records it means they don't have a corresponding Vend/CustTransOpen record which should be impossible!!

You can delete the specTrans records that meet this criteria as the specTrans table holds the settling flag between invoice and payment pre-posting. If you delete the SpecTrans record you lose the marking, which you can complete again. If you find records that meet the criteria from the job it showing there is marking but they are marked to nothing so they need to be removed.

You can remove the records you found by un-commenting the //SpecTrans.delete() line in the job...

 

Reply
Trond Djønne responded on 12 Apr 2012 11:58 PM

Rynharth, did every user experience this on the specific vendor?

We've got the same issue with a vendor record, but not with every user.

Reply
somvir responded on 2 Apr 2013 6:21 AM

The only solution is , delete your related transaction from SpecTrans table. It seems a data loss or mismanage of data . I had the same issue, debug it for a while and compare with two other companies, which did not have this issue and found the problem with this table . Do not comment or add anything in the code. That do not follow best practice.

let me know if there is any issue.

Somvir Gahlawet

Reply
rayle responded on 4 Jul 2013 3:44 PM

I had this issue and it was due to two orphaned records in the SpecTrans table.

Thanks Somvir, code works great.

Reply
John Bevan responded on 2 Jan 2015 6:45 AM

Based on Kenneth Damsgaard's X++ code, here's some SQL to monitor for all such issues:

DECLARE @tables TABLE
  (
     specid BIGINT,
     refid  BIGINT,
     spec   SYSNAME,
     ref    SYSNAME
  )

INSERT @tables
       (specid,
        refid,
        spec,
        ref)
SELECT DISTINCT spectableid,
                reftableid,
                ss.sqlname,
                sr.sqlname
FROM   spectrans s
       INNER JOIN sqldictionary ss
               ON ss.tableid = s.spectableid
                  AND ss.fieldid = 0
       INNER JOIN sqldictionary sr
               ON sr.tableid = s.reftableid
                  AND sr.fieldid = 0

DECLARE @sql NVARCHAR(max),
        @r   SYSNAME,
        @s   SYSNAME,
        @ri  BIGINT,
        @si  BIGINT

WHILE EXISTS(SELECT TOP 1 1
             FROM   @tables)
  BEGIN
      SELECT TOP 1 @r = ref,
                   @s = spec,
                   @ri = refid,
                   @si = specid
      FROM   @tables

      SET @sql = 'select ' + Quotename(@r, '''')
                 + ' tableName, x.dataareaid, x.accountnum, s.* '
                 + 'from spectrans s ' + 'left outer join ' + @s
                 + ' x on x.recid = s.specrecid '
                 + 'where not exists' + '(' + ' select top 1 1 '
                 + ' from ' + @r + ' o '
                 + ' where o.recid = s.refrecid ' + ') '
                 + 'and s.spectableid = '
                 + Cast(@si AS NVARCHAR(max)) + ' '
                 + 'and s.reftableid = '
                 + Cast(@ri AS NVARCHAR(max)) + ' '

      EXEC(@sql)

      DELETE FROM @tables
      WHERE  refid = @ri
             AND specid = @si
  END 

This code could easily be adapted to create delete statements; I've not done that here since I believe in automated monitoring and manual resolution; since that way you'll be aware of issues and able to investigate underlying causes.

Reply
Suggested Answer
Einar Lárusson responded on 18 Nov 2011 7:00 AM

In the add method of the class CustVendOpenTransBalancesManager i added this code

if (tmpBalances.TransOpenRecId)

       tmpBalances.insert();

The system was trying to add a record to the t,p tabel with the TransOpenRecId = 0.

Why that happens i don't know but i am trying to figure that out, but this is a temp solution so that it is possible to open the form at least

Hope this helps

Einar

epexplorer.blogspot.com

Reply
Suggested Answer
Einar Lárusson responded on 21 Nov 2011 12:41 AM

Hi

Then you should put the if statement around the whole code block, then nothing is done in case of there is no record to insert into the tmp tabel.

Let me know how that works

Regards

Einar

Reply
Suggested Answer
Einar Lárusson responded on 22 Nov 2011 12:13 AM

Hi

Then you are not haveing the exact same problem as we had. We had a problem that the system was trying to insert revords with 0 as the transrecid and we got the error the second time the system tryed to insert a record with transrecid = 0.

This offcourse breaks the unique index on the table and we get the error that the record allready exists.

You problably need to do a check on the transExchRate since that is what is causing the debugger to dtop.

Regards

Einar

Reply
Suggested Answer
RenBee responded on 22 Nov 2011 12:46 AM

May i know how did you fixed your error?Just for reference.

Einar,

 The form is temporarily OK without an error. what I did is, I comment out the code: and add your suggested code.

/*

   // Assert that the exchange rate values passed in are not zero as they may be used in calculations as divisors

   Debug::assert(_transExchRate != 0);

   Debug::assert(_transToMstExchRate != 0);

   Debug::assert(_transMstToDisplayMstExchRate != 0);

   Debug::assert(_mstToDisplayExchRate != 0);

   tmpBalances.TransCompany = _transCompany;

   tmpBalances.TransOpenRecId = _transOpenRecId;

   tmpBalances.TransAmount = _transAmount;

   tmpBalances.EstimatedCashDiscAmount = _estimatedCashDiscAmount;

   tmpBalances.TransCurrency = _transCurrency;

   tmpBalances.TransDate = _transDate;

   tmpBalances.TransExchRate = _transExchRate;

   tmpBalances.TransToMstExchRate = _transToMstExchRate;

   tmpBalances.TransMstToDisplayMstExchRate = _transMstToDisplayMstExchRate;

   tmpBalances.MstToDisplayExchRate = _mstToDisplayExchRate;

   tmpBalances = this.calculateBalances(tmpBalances);

   tmpBalances.insert(); */

   if (tmpBalances.TransOpenRecId)

      tmpBalances.insert();

Reply
Suggested Answer
Einar Lárusson responded on 22 Nov 2011 3:21 AM

Hi

The only thing that i did was to add the if statement like this

if (tmpBalances.TransOpenRecId)

     tmpBalances.insert();

Then the system is not inserting empty records which was the root for our problem => the system tried to insert two records with the TransOpenRecId = 0.

If you comment out the code like you do then you will not get anything in the tmp table.

This is how the code looks like

  // Assert that the exchange rate values passed in are not zero as they may be used in calculations as divisors

  Debug::assert(_transExchRate != 0);

  Debug::assert(_transToMstExchRate != 0);

  Debug::assert(_transMstToDisplayMstExchRate != 0);

  Debug::assert(_mstToDisplayExchRate != 0);

  tmpBalances.TransCompany = _transCompany;

  tmpBalances.TransOpenRecId = _transOpenRecId;

  tmpBalances.TransAmount = _transAmount;

  tmpBalances.EstimatedCashDiscAmount = _estimatedCashDiscAmount;

  tmpBalances.TransCurrency = _transCurrency;

  tmpBalances.TransDate = _transDate;

  tmpBalances.TransExchRate = _transExchRate;

  tmpBalances.TransToMstExchRate = _transToMstExchRate;

  tmpBalances.TransMstToDisplayMstExchRate = _transMstToDisplayMstExchRate;

  tmpBalances.MstToDisplayExchRate = _mstToDisplayExchRate;

  tmpBalances = this.calculateBalances(tmpBalances);

  if (tmpBalances.TransOpenRecId)

     tmpBalances.insert();

Regards

Einar

Reply
Suggested Answer
Kenneth Damsgaard responded on 10 Feb 2012 2:02 AM

You most likely have data damage (Orphaned records) in the specTrans table.

you can check it with this small job below...:

___________________

static void Check_Orphans_Spectrans(Args _args)
{
    SpecTrans       specTrans;
    CustTransOpen   custTransOpen;
    counter         i;
    ;

    while select forupdate * from SPECTRANS where SpecTrans.RefTableId == 865 // 866 for Debtors
    {
        select firstonly * from CustTransOpen where CustTransOpen.RecId == SpecTrans.RefRecId;

        if(!CustTransOpen.RecId)
        {
            info(strFmt("%1 balance : %2",SpecTrans.RefRecId,SpecTrans.Balance01));
            //SpecTrans.delete();
            i++;
        }
    }
    info(strFmt("records deleted : %1",i));
}

___________________

If you find records it means they don't have a corresponding Vend/CustTransOpen record which should be impossible!!

You can delete the specTrans records that meet this criteria as the specTrans table holds the settling flag between invoice and payment pre-posting. If you delete the SpecTrans record you lose the marking, which you can complete again. If you find records that meet the criteria from the job it showing there is marking but they are marked to nothing so they need to be removed.

You can remove the records you found by un-commenting the //SpecTrans.delete() line in the job...

 

Reply