By using this website, you agree to our Terms of Use (click here)
Hi!
I am looking to update an existing GI built for Opportunities where the connected sales order number was displaying using CROpportunity.orderNbr field. When the screen was updated to use quotes, this field was removed and a tab with Sale Order grid was added.
Has anyone used the new fields in a GI or report and know the relation Acumatica is using?
Thank you,
Shaun
The ORDERS tab of the Opportunities (CR304000) screen makes it seem that there can be multiple Sales Orders for an Opportunity so I don't think it can be a field. I think it needs to be a table.
There's a CRRelation table that could probably be used to join the Opportunities and Sales Orders together. CRRelation.TargetNoteID looks like the Opportunity ID and CRRelation.RefNoteID looks like something you can join to SOOrder.NoteID to get the Sales Order ID.
Hi Tim,
Thank you for responding to my post. Your suggestion worked to display the order number.
Thank you,
Shaun
Is there a possibility to see how you did the joins? I'm having trouble joining these three tables.
Nevermind, I already found a way. I was using the OpportunityID and not the NoteID of the Opportunity, silly mistake.
Thanks anyways for bringing the CRRelation route.
@timrodman you're my hero.
@shaunz Hi Shaun. Do you mind elaborating on the joins that you used?
Tim's post has put me in the ballpark but I'm obviously missing something.
All solutions are welcome.
Cheers, Scott.
🙂
I just tried adding these two joins to the Opportunities (CR3040PL) generic inquiry in Acumatica 2020 R1 and it seemed to work:
CROpportunity.NoteID -> CRRelation.DocNoteID
CRRelation.RefNoteID -> SOOrder.NoteID
@timrodman Hi Tim. You beauty!
That's working for me too now I've found my problem;
I was using the CROpportunity.OpportuntiyID, not the CROpportunity.noteID. D'oh!
I have no idea how I got it muddled. [ Cleans glasses - now that's better 🙂 ]
Appreciate the quick response. Still getting my head around the backend of this beastie.
Thanks again. Scotty.
This was very helpful. Is there a way to join CROpportunityProducts to ARTran?
@drustorm21 I just tried looking for a relationship between the Opportunity Lines and the Sales Order Lines, but I can't fine one. It seems to only be between the Opportunity and the Sales Order, not the Sales Order Lines. I would think it would be the same thing for Invoice Lines (ARTran).
@drustorm21 @timrodman I am seconding this. I've done a lot of looking, but Acumatica as of 2020R1 doesn't keep that link. In the end, I'm going to write a custom action that will keep this link alive. We have a use case where, with certain Custom items, items are split off of an opportunity into multiple sales orders. Letting the users pick some items to make an order with, and then come back and pick the rest later will make that process streamlined. Also eventually, the way we handle our commissions wants to know this information too.
All I want to say about this is THANK YOU. I have been trying to do a similar link from quote to converted sales order. This got me there!