CiviCRM Community Forums (archive)

You will also need to drop the unique index from the db

I think we use and check for uniqueness in a couple of places. You might want to search the code for trxn_id and fix it as needed

lobo

Brian - I'm not sure if your change will affect online transactions, altho i doubt it.

However, from a overall data design perspective - the "intent" for folks entering check payment transaction id's was that the "acct # + chk #" would provide a unique key for the transaction. Perhaps this is not a "realistic" approach to how folks enter this data and we should consider providing a different field for this or removing the uniqueness constraint in general ??

Would be great to hear thoughts from others on this.

Would it make sense to create a custom field (called check #) and use that to record check numbers, and avoid using trxn_id?

will make maintainance a lot easier

lobo

Hi. Hope this is a good place to put this.

I am testing and documenting the best way for administrators to enter payments received from those who elect to "pay later" on the contribution form.  Using Joomla 1.5 and CiviCRM 2.0.4.

Following instructions in CiviCRM,  I use "Update Pending Contribution Status" for this, which works fine, updates membership status as well as contribution status.  On one occasion, however I forgot to enter the amount in the "Fee Amount" field on the "Update Contribution Status" screen. I did enter a unique Transaction Id and accepted the default Transaction date. After updating I realised my mistake.  It made me start to test possible procedures for correcting such errors.

I went into Edit for the particular record and reset the contribution status to "pending" which allowed me to once again go into "Update Pending Contribution Status".  This time I entered the Fee Amount, left the Transaction Id and Transaction Date as they were.  On update, I received a database error - see below. 

I know the transaction id is supposed to be unique and that would be fine - if the system was doing a database UPDATE. But according to the error it is trying to do an INSERT and of course, failing. 

Why is it doing an INSERT here?

I agree with others who posted here that it would be good to have transaction id or cheque id field that is not unique nor potentially a mix of autogenerated and manually entered data.

Peta




 Sorry. A non-recoverable error has occurred.

    DB Error: already exists

    Database Error Code: Duplicate entry '9723-1' for key 2, 1062

    Return to home page.

Error Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

=> -5
    [message] => DB Error: already exists
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_financial_trxn (domain_id , contribution_id , trxn_date , trxn_type , total_amount , fee_amount , net_amount , currency , payment_processor , trxn_id ) VALUES ( 1 ,  24 ,  20080709 , 'Debit' ,  0.05 ,  0.05 ,  0 , 'AUD' , 'PayPal_Standard' , '9723' )  [nativecode=1062 ** Duplicate entry '9723-1' for key 2]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_financial_trxn (domain_id , contribution_id , trxn_date , trxn_type , total_amount , fee_amount , net_amount , currency , payment_processor , trxn_id ) VALUES ( 1 ,  24 ,  20080709 , 'Debit' ,  0.05 ,  0.05 ,  0 , 'AUD' , 'PayPal_Standard' , '9723' )  [nativecode=1062 ** Duplicate entry '9723-1' for key 2]
    [to_string] => [db_error: message="DB Error: already exists" code=-5 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_financial_trxn (domain_id , contribution_id , trxn_date , trxn_type , total_amount , fee_amount , net_amount , currency , payment_processor , trxn_id ) VALUES ( 1 ,  24 ,  20080709 , 'Debit' ,  0.05 ,  0.05 ,  0 , 'AUD' , 'PayPal_Standard' , '9723' )  [nativecode=1062 ** Duplicate entry '9723-1' for key 2]"]
)


Hi

Thank you for your reply.

So, the first time I did the "Update Pending Contribution Status",  the contribution record was UPDATED and a record was INSERTED into the financial transaction table with a Fee Amount of $0.

The second time I am trying to correct that error using "Update Pending Contribution Status".
If I change the transaction id which will allow the INSERT into the financial transaction table I will have two financial records against this contribution, one with a fee amount of $0 and one with the correct amount. Is that correct?

I wonder if it would be possible to allow edit of the financial transaction table entry, by checking for its existence by contributionid, before determining whether an INSERT or UPDATE is required?  I am sure my client will make the same mistake I did, or put the wrong date or transaction id.

Related to this: today I applied the code changes in CRM-3177 to my J1.5.3/Civi2.0.4 - as I need to have a "pay later" without a payment processor.  This worked beautifully for the contribution form and process.

But good old "Update Pending Contribution Status" failed with an error "Could not find payment processor for contribution record:".  I have tracked this (I think) to

administration/components/civicrm/CRM/Contribute/Form/Task/Status.php about row 239

    if ( ! baseIPN -->validateData.... etc etc

and it fails in  administration/components/civicrm/CRM/Core/Payment/BaseIPN.php in the function loadObjects around row 199 where it requires a 'payment processor'!

Is there another CRM number for this fix?  If not I can restore my system to remove the CRM-3177.  I think it's a bit beyond me to work out how to fix this one!

Peta

Hi

Thank you so much for this! I was able to apply it as a patch - (I needed to put a comma after 'id', though) - now it is working fine.

Peta

// if a transaction already exists for a contribution id, lets get the id
        66   +           $id = CRM_Core_DAO::getFieldValue( 'CRM_Contribute_DAO_FinancialTrxn',
        67   +                                              $trxn->contribution_id,
        68   +                                              'id'
        69   +                                              'contribution_id' );
        70   +           if ( $id ) {
        71   +               $trxn->id = $id;
        72   +           }