Extending Acumatica BQL queries: Two Essential Considerations for Pagination and Data Completeness
When customizing Acumatica screens, for example like printing invoices, there are subtle pitfalls that you need to be aware of. In our extended ARPrintInvoices screen, we encountered two major challenges:
- Extending Original Logic Requires a Complete Copy of GetBQLStatement
- Retrieving Complete Customer Data Requires a Separate Query
In this post, we’ll explore these challenges and how to address them effectively.
1. Fully Copying GetBQLStatement for Proper Pagination
In Acumatica, the default data retrieval method (baseMethod) in a PXGraph extension is built to return only the first page of data. This is not ideal when your customization demands proper pagination across the complete result set.
Why Copy GetBQLStatement?
The answer for this question is simple, we cannot use baseMethod here because it will always return paginated, when filtering we need to go through every record, thats why we need to reimplement not only original method, but also GetBQLStatement since it has protected type (of course we can use reflection also but I suppose it will be better way to reimplement original function)
2. Ensuring Complete Customer Data Retrieval
Another subtle challenge is that using the typical line:
csharp
Customer customer = record.GetItem<Customer>();
may not include all the fields of the Customer record. This happens because the BQL statement you build for the query only selects a subset of fields needed for joining, which means some customer details might be missing.
The Workaround:
After you have processed the initial record, retrieve the full customer data with an additional query. For instance:
csharp
customer = SelectFrom<Customer>
.Where<Customer.bAccountID.IsEqual<@P.AsInt>>.View
.Select(Base, invoice.CustomerID);
This extra step ensures that all fields of the Customer record are available for any subsequent business logic. It adheres to the KISS principle by keeping the initial join lightweight while offloading the complete data retrieval to a dedicated query when necessary.
Conclusion
When extending Acumatica's ARPrintInvoices functionality, the key takeaways are:
● Full Duplication of GetBQLStatement:
To bypass the pagination limitations of the base method, you must fully replicate the original BQL query logic. This allows you to construct a query that fetches the entire dataset, providing a solid foundation for custom pagination.
● Separate Query for Complete Customer Data:
Relying on the initial join may leave out important customer details. A separate query ensures that you retrieve a fully populated Customer record, maintaining data integrity for further processing.
By addressing these two points, you create a robust, maintainable extension that aligns with best practices and meets advanced filtering and pagination requirements. These considerations ensure that your Acumatica customizations are both user-friendly and scalable as business needs evolve.
This approach exemplifies a clean, well-structured strategy for extending Acumatica’s functionality, ensuring that your code remains robust, maintainable, and fully aligned with core software development principles.