Skip to main content
Solved

Help to debug Generic Inquire generated query

R
Freshman I

Hello everyone,


I'm working on a tool to convert GIs to SQL Queries for an external tool, and when debugging the generated Acumatica SQL query for one of my Generic Inquiry I see that it has a LEFT JOIN to the ARPayment table, but I can't find any reference to ARPayment in the GI's editor, or in the GI's exported XML file. The ARPayment is neither a BaseType DAC for any of the DACs used in the GI.

 

These are the tables in the GI:

And all relations:

 

The generated query used to retrieve the GI data has this LEFT JOIN:

 

There is no reference to the ARPayment in the selected fields also.

 

Does someone have any clue as to why this join is being added?

 

Thanks!

Ricardo.

Best answer by andriitkachenko

Hi @ricardosilva 

PX.Objects.SO.SOInvoice is a projection:

[PXProjection(typeof(Select2<SOInvoice, InnerJoin<SOInvoice.ARRegister, On<SOInvoice.ARRegister.docType, Equal<SOInvoice.docType>, And<SOInvoice.ARRegister.refNbr, Equal<SOInvoice.refNbr>>>, LeftJoin<PX.Objects.AR.Standalone.ARPayment, On<SOInvoice.docType, Equal<PX.Objects.AR.Standalone.ARPayment.docType>, And<SOInvoice.refNbr, Equal<PX.Objects.AR.Standalone.ARPayment.refNbr>>>>>>), Persistent = true)]

When projection is added to an SQL query, Acumatica replaces it with the subquery, built using BQL declaration defined in the PXProjectionAttribute.

View original
Did this topic help you find an answer to your question?

8 replies

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

Could you post your Results tab?

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
Chris Hackett
1 person likes this
  • Chris Hackett
    Chris Hackett
R
Freshman I
  • ricardosilva
  • Author
  • Freshman I
  • 4 replies
  • June 19, 2024

Hi, @darylbowman,

Here are the Results Grid tab:

Results Grid 1/2
 
Result Grid 2/2

 

Thanks!

Ricardo.

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

I was looking for something like this, where you’re using a field that is available but not able to be displayed without the system performing an extra table join, but I’m not really seeing anything related to ARPayment.

 

Does the generated query also include the results? Can you see what information is being returned by [SOInvoice_ARPayment]?

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
Chris Hackett
R
2 people like this
  • Chris Hackett
    Chris Hackett
  • R
    ricardosilva
R
Freshman I
  • ricardosilva
  • Author
  • Freshman I
  • 4 replies
  • June 19, 2024

The results do not appear to have any SOInvoice_ARPayment related data. The joins to resolve the descriptions fields are expected and I can understand, but I can't trace the source of the join to ARPayment.

Chris Hackett
Community Manager
Forum|alt.badge.img
  • Chris Hackett
  • Acumatica Community Manager
  • 2809 replies
  • July 29, 2024

Hi @ricardosilva 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*.
R
1 person likes this
  • R
    ricardosilva
R
Freshman I
  • ricardosilva
  • Author
  • Freshman I
  • 4 replies
  • July 29, 2024

Hi, @Chris Hackett. Not yet. We're checking each GI query on the "trace" screen manually.

Chris Hackett
1 person likes this
  • Chris Hackett
    Chris Hackett
andriitkachenko
Jr Varsity I
Forum|alt.badge.img+6

Hi @ricardosilva 

PX.Objects.SO.SOInvoice is a projection:

[PXProjection(typeof(Select2<SOInvoice, InnerJoin<SOInvoice.ARRegister, On<SOInvoice.ARRegister.docType, Equal<SOInvoice.docType>, And<SOInvoice.ARRegister.refNbr, Equal<SOInvoice.refNbr>>>, LeftJoin<PX.Objects.AR.Standalone.ARPayment, On<SOInvoice.docType, Equal<PX.Objects.AR.Standalone.ARPayment.docType>, And<SOInvoice.refNbr, Equal<PX.Objects.AR.Standalone.ARPayment.refNbr>>>>>>), Persistent = true)]

When projection is added to an SQL query, Acumatica replaces it with the subquery, built using BQL declaration defined in the PXProjectionAttribute.

Andrii Tkachenko | Sprinterra | https://www.sprinterra.com/ | 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
MichaelShirk
R
3 people like this
  • Chris Hackett
    Chris Hackett
  • MichaelShirk
    MichaelShirk
  • R
    ricardosilva
R
Freshman I
  • ricardosilva
  • Author
  • Freshman I
  • 4 replies
  • July 30, 2024

Hi, @andriitkachenko. This is what I have beenlooking for. Thank you!

Chris Hackett
1 person likes this
  • Chris Hackett
    Chris Hackett

Reply


Most helpful members this week

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
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