Skip to main content
Answer

Connection between PMTran and APTran

  • April 24, 2023
  • 6 replies
  • 310 views
aaghaei
Captain II
Forum|alt.badge.img+10

Hello Community,

 

I need to Join PMTran and APTran. My understanding is the below join should work but when I compare some of the transactions which are generated from AP, their LineNbr doesn’t agree to OrigLineNbr in PMTran. Below is the query I need, which is a simple one but not sure why LineNbr field doesn’t align. 

                var tran = SelectFrom<PMTran>
                    .LeftJoin<APTran>
                        .On<PMTran.origTranType.IsEqual<APTran.tranType>
                        .And<PMTran.origRefNbr.IsEqual<APTran.refNbr>>
                        .And<PMTran.origLineNbr.IsEqual<APTran.lineNbr>>
                        .And<PMTran.origModule.IsEqual<BatchModule.moduleAP>>>
                    .View.Select(Base);

If this is not the right join, then what is it?

Best answer by aaghaei

UPDATE:

I ended up with the below query and so far it has passed all my tests.

PXResultset<PMTran> actual = SelectFrom<PMTran>
    .InnerJoin<GLTran>
        .On<PMTran.tranID.IsEqual<GLTran.pMTranID>
        .And<GLTran.module.IsEqual<BatchModule.moduleAP>>>
    .LeftJoin<APTran>
        .On<GLTran.tranType.IsEqual<APTran.tranType>
        .And<GLTran.refNbr.IsEqual<APTran.refNbr>>
        .And<GLTran.tranLineNbr.IsEqual<APTran.lineNbr>>>
    .View.Select(Base);

 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
iqraharrison

6 replies

Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Naveen Boga
  • Captain II
  • April 24, 2023

Hi @aaghaei  I did not check but may work, can you check this please?

 


				var Details = new PXSelectJoin<PMTran,
					InnerJoin<ARTran, On<ARTran.tranType, Equal<PMTran.aRTranType>,
					And<ARTran.refNbr, Equal<PMTran.aRRefNbr>,
					And<ARTran.lineNbr, Equal<PMTran.refLineNbr>>>>>,
					Where<PMTran.aRTranType, Equal<Required<PMTran.aRTranType>>,
					And<PMTran.aRRefNbr, Equal<Required<PMTran.aRRefNbr>>>>,
					OrderBy<Asc<ARTran.lineNbr>>>(Base);

 

Naveen B - Acumatica MVP 🏆 | Independent Developer for Hire 🚀 ⏩ https://www.linkedin.com/in/boga-naveen-kumar-54079a66/
Chris Hackett
aaghaei
Captain II
Forum|alt.badge.img+10
  • aaghaei
  • Author
  • Captain II
  • April 24, 2023

Thank you @Naveen Boga 

the code you provided joins the AR but I need to join APTran to PMTran. The join you provided links the invoices which are generated from PMProforma Invoices to PMTran

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Naveen Boga
  • Captain II
  • April 24, 2023

Oops...Sorry,@aaghaei  I thought it was for ARTran. 

Let me check for APTran and let you know If I found anything.

Naveen B - Acumatica MVP 🏆 | Independent Developer for Hire 🚀 ⏩ https://www.linkedin.com/in/boga-naveen-kumar-54079a66/
aaghaei
Captain II
Forum|alt.badge.img+10
  • aaghaei
  • Author
  • Captain II
  • April 24, 2023

Thanks @Naveen Boga Appreciate your time pal

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
aaghaei
Captain II
Forum|alt.badge.img+10
  • aaghaei
  • Author
  • Captain II
  • Answer
  • April 24, 2023

UPDATE:

I ended up with the below query and so far it has passed all my tests.

PXResultset<PMTran> actual = SelectFrom<PMTran>
    .InnerJoin<GLTran>
        .On<PMTran.tranID.IsEqual<GLTran.pMTranID>
        .And<GLTran.module.IsEqual<BatchModule.moduleAP>>>
    .LeftJoin<APTran>
        .On<GLTran.tranType.IsEqual<APTran.tranType>
        .And<GLTran.refNbr.IsEqual<APTran.refNbr>>
        .And<GLTran.tranLineNbr.IsEqual<APTran.lineNbr>>>
    .View.Select(Base);

 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Chris Hackett
Chris Hackett
Community Manager
Forum|alt.badge.img
  • Chris Hackett
  • Acumatica Community Manager
  • April 25, 2023

Thank you for sharing your solution with the community @aaghaei !

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*.
aaghaei
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