Skip to main content
Answer

Opportunity ID in Invoice Report – Join does not display value

  • July 1, 2025
  • 7 replies
  • 118 views
P
Jr Varsity III
Forum|alt.badge.img

Hello everyone,

I'm trying to display the Opportunity ID on the invoice form (Report Designer). To do this, I created the following join structure in the schema:

ARInvoice(NoteID) → CRRelation(RefNoteID)
CRRelation(TargetNoteID) → CROpportunity(NoteID)

Unfortunately, the Opportunity ID still does not appear on the report.

I also tried using SOOrder instead of ARInvoice, but then the line items are shown twice (I’ve tried all possible join options).

Does anyone have a tip on how I should proceed?

Best regards,
Pantea

Best answer by PanteaShahrian

Hi ​@Chris Hackett ,

Yes, I was able to resolve it. I created a custom field on the document where the Opportunity ID can be entered manually, and then I linked that field to the report.

Best regards,
Pantea

    7 replies

    darylbowman
    Captain II
    Forum|alt.badge.img+15

    Are you creating the Invoices directly from the Opportunities or through Sales Orders?

    Daryl⚡Freelance Acumatica Developer (MVPx2🏆) for hire ⏩ linkedin.com/in/darylbowman
    Chris Hackett
    P
    Jr Varsity III
    Forum|alt.badge.img
    • PanteaShahrian
    • Author
    • Jr Varsity III
    • July 1, 2025

    I create the Sales Order from the Opportunities, and the invoice is then generated based on the Sales Order.

    darylbowman
    Captain II
    Forum|alt.badge.img+15

    Sorry I don’t have time for a more polished answer, but maybe this will help:

    CROpportunity O
    INNER JOIN 
        CRRelation R ON O.NoteID = R.TargetNoteID
    INNER JOIN 
        SOOrder S ON S.NoteID = R.RefNoteID
    INNER JOIN 
        SOOrderShipment SS ON S.OrderType = SS.OrderType AND S.OrderNbr = SS.OrderNbr
    INNER JOIN 
        ARTran T ON SS.ShipmentType = T.SOShipmentType AND SS.ShipmentNbr = T.SOShipmentNbr
    INNER JOIN 
        ARInvoice I ON T.TranType = I.DocType AND T.RefNbr = I.RefNbr

     

    Daryl⚡Freelance Acumatica Developer (MVPx2🏆) for hire ⏩ linkedin.com/in/darylbowman
    Chris Hackett
    P
    P
    Jr Varsity III
    Forum|alt.badge.img
    • PanteaShahrian
    • Author
    • Jr Varsity III
    • July 1, 2025

    thanks

    darylbowman
    Chris Hackett
    P
    Jr Varsity III
    Forum|alt.badge.img
    • PanteaShahrian
    • Author
    • Jr Varsity III
    • July 2, 2025

    ​@darylbowman 
    I tried it, but unfortunately it didn’t work. Do you have any other ideas?
    I’m sending you the report—could you please check the relations?

    thank you in advance

    1 Attachments
    Chris Hackett
    Community Manager
    Forum|alt.badge.img
    • Chris Hackett
    • Acumatica Community Manager
    • September 24, 2025

    Hi ​@PanteaShahrian were you able to find a solution? Thank you!

    Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
    P
    Jr Varsity III
    Forum|alt.badge.img
    • PanteaShahrian
    • Author
    • Jr Varsity III
    • Answer
    • September 26, 2025

    Hi ​@Chris Hackett ,

    Yes, I was able to resolve it. I created a custom field on the document where the Opportunity ID can be entered manually, and then I linked that field to the report.

    Best regards,
    Pantea

    Chris Hackett
    Terms & ConditionsCookie settingsAccessibility statement
    About Acumatica ERP system
    Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
    © 2008 — 2024  Acumatica, Inc. All rights reserved