OpenSync

Home Forums OpenSync Bug: Cannot create new invoice and populate receivepayment in the same update.

This topic contains 7 replies, has 3 voices, and was last updated by  Yudel Rosales 1 year ago.

Viewing 8 posts - 1 through 8 (of 8 total)
  • Author
    Posts
  • #66768

    Mark
    Premium Member

    Step 1:
    Populate invoice with the following fields:
    TxnID => Generated ID… like NEW-1234
    RefNumber
    ARAccountRef_FullName
    CustomerRef_FullName
    Subtotal
    Memo
    Other
    Status => Add

    Populate invoicelinedetail with the following fields:
    IDKEY => Same Invoice ID
    ItemRef_FullName
    Quantity
    Description
    Rate

    Step 2:
    Populate LinkedTxnDetail with the following fields
    IDKEY => Same Invoice ID
    TxnID => Generated ID… like NEW-12345
    TxnType
    TxnDate
    RefNumber
    LinkType
    Amount

    Populate ReceivePayment with the following fields
    TxnID => Same as LinkedTxnDetail TxnID
    RefNumber
    TotalAmount
    CustomerRef_FullName
    ARAccountRef_FullName
    TxnDate
    DepositToAccountRef_FullName
    PaymentMethodRef_FullName
    Memo
    UserData
    Status => Add

    Populate AppliedToTxnDetail with the following fields
    IDKEY => Same as LinkedTxnDetail TxnID
    TxnID => Same Invoice ID
    Amount

    If both step 1 and step 1 are done together, and then ‘Update to QB’ task is run which populates Invoice and Receive Payment then this happens:

    elect * from error_table \G
    *************************** 1. row ***************************
    Error_Num: -2147220962
    Error_Table: receivepayment:appliedtotxndetail
    Error_Field: txnid
    IDKEY: NEW-516882980
    Error_Desc: Unable to locate the transaction you are trying to apply the payment to. You may want to refresh the table to make sure all TxnID’s are current. Reminder: You may only apply a payment to an invoice
    TIME_DATE: 2020-02-09 22:27:41

    The invoice is added successfully, but the receivepayment:appliedtotxndetail is not

    If Step 1 is done, and then OpenSync ‘Update to QB’ is completed,
    And then Step 2 is completed using the updated QB transaction IDS like 1-1240343283 (Instead of my generated ‘NEW-516882980’). Then this DOES work.

    This second step is very cumbersome because it requires two syncs for every operation.

    #66776

    Yair
    Participant

    @mark welcome to QuickBooks, API don’t expect anything to work perfectly, in particular, hold your fingers after an update 🙂

    It sounds like you should be using a sale receipt.
    if an invoice is paid straight away there is no point in making an invoice just make a sale-receipt
    and make sure you apply to “undeposite funds” or directly to the right bank account you receive the payment.

    The other option I am using I have a table called “ApplyCredit” which I created.
    and the end of the day I click a button and all the credit is uploaded.

    if the customer wants an invoice printed showing the amount is paid, I generate an invoice that check if there is “pending credit to upload” and does the calculation.

    #66800

    Mark
    Premium Member

    Hi Yair,

    We’re syncing invoices from an outside invoicing system and replicating into quickbooks.

    But, I see what you’re saying that creating a Sales Receipt is probably the way to go.

    Also

    What happened to the premium support I paid for? Exactly one week and no response?

    #66806

    Yudel Rosales
    Keymaster

    -Invoice Table

    ARAccount ListID or FullName (Required)
    BillAddress Fields
    Class ListID or FullName
    Customer ListID or FullName (Required)
    CustomerMessage
    DueDate
    FOB
    Memo
    IsPending
    IsToBePrinted
    PONumber
    RefNumber
    SalesRep
    SalesTaxCode ListID or FullName
    SalesTaxItem ListID or FullName
    ShipAddress Fields
    Terms
    TxnDate
    TxnID (Required) – Links with IDKEY in InvoiceLineDetail table

    -InvoiceLineDetail Table

    Class ListID or FullName
    Rate
    RatePercent
    IDKEY (Required) – Links with TxnID in Invoice table
    Item ListID or FullName (Required)
    Desc
    Quantity
    SalesTaxCode ListID or FullName

    #66807

    Yudel Rosales
    Keymaster

    to delete an invoice set the status column on the row that you want to delete with the “delete” word. then run and update tasks. That should do it.

    #66814

    Mark
    Premium Member

    Delete does not work:

    UPDATE invoice SET status = ‘DELETE’ WHERE TxnID = ‘….’

    Sync:

    SELECT * FROM error_table;

    | -2147467259 | invoice | critical | B24D-1581384286 | [MySQL][ODBC 5.3(a) Driver][mysqld-5.5.5-10.2.12-MariaDB]Data too long for column ‘IDKEY’ at row 1 | 2020-02-10 22:48:34 |

    A similar type of error has already been reported in another thread.

    #66815

    Yair
    Participant

    Try setting QTY to 0 and change status to “UPDATE”

    so like
    UPDATE invoicelinedetail set Quantity=0 where IDKEY='[TxnID]’ <– txnid from invoice
    UPDATE invoice SET status = ‘UPDATE’,Memo=’Deleted OpenSYnc’ WHERE TxnID = ”

    #66838

    Yudel Rosales
    Keymaster

    it seems like it is giving you an error on the query that you are running to set the status column. I don’t see the error coming from OpenSync.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic.