Recently, I had the opportunity to attend Acumatica Summit Development and Customization Training to update my certification to the latest version. Throughout the course, I was struck by how important a concept Business Query Language (BQL) was to be able to customize Acumatica. So today, I want to share the insights I have gained over the last four years working with BQL, and perhaps jump-start your learning curve on the subject.
Business Query Language is how we query data from the database in Acumatica. The purpose of BQL is to create an agnostic layer abstracting the database provider (i.e., MySQL or SQL) or even a future implemented database type from the queries written in Acumatica. Eventually Oracle, MongoDB or Elasticsearch could be implemented without developers needing to update each individual query and page of Acumatica.
The original or traditional format uses C# generic class syntax. The use of C# allows us to use classes across multiple data types. With BQL, we are able to build nested chains of statements from the various BQL components and mimic the basic structure of SQL and MySQL. Each Generic<T,U> class uses the type T or U to refer to a Table aka Data Access Class(DAC), Field in a Table/DAC, or to another Generic<T,U> class that further limits or expands the records being chosen.
As a simple example:
Select<InventoryItem, Where<InventoryItem.itemType, Equal<Current<Table1.itemType>>>>
is the same as SQL
@CurrentParam = Table1.ItemType.CurrentValue
Select *
From InventoryItem
Where InventoryItem.itemType = @CurrentParam
Note that the Current Parameter here would be used to limit or filter the results only by the current record displayed on screen or the field value at the last unapplied operation not yet completed (Insert, Delete, Update etc.).
Breaking this down, we can see that Select< T, U> in our example is a DAC at type position T and a Where clause Generic class at type position U. The Where<T, U> references a field at type position T and the Equal operation Generic class at type position U. The Equal<T> and Current<T> classes only have one type referenced; Equal<T> accepts either a parameter, field or constant type. The Current<T> class takes only a field reference. Many more classes exist to replicate the common functionality found in databases. A handy reference to these classes can be found here.
The last few versions of Acumatica have seen a gradual transition from Traditional BQL to Fluent BQL. Fluent BQL is much simpler and the queries are shorter, yet are just as powerful as Traditional BQL in most cases. One big benefit of Fluent BQL is that the laundry list of Generic Classes to start a query off has been reduced to just one SelectFrom. The other significant benefit is that Intellisense in Visual Studios can now offer suggestions via the type ahead and can help you close out your <>. Gone are the days of counting open and closing angle brackets. A handy cross-reference between Traditional and Fluent BQL is found here.
The example above would be written as follows in BQL Fluent:
SelectFrom<InventoryItem>.Where<InventoryItem.itemType.Equal<Table1.itemType.FromCurrent>>>
One final note on BQL’s and DAC’s. Every field referenced on a DAC consists of at least two lines — the abstract class notated with a lowercase and inheriting from IBqlField or PX.Data.BQL.Bql + the type for instance PX.Data.BQL.BqlInt which inherits from IBqlFieldOf<TTable>, OR the property denoted by virtual and the data type and denoted by a capital letter. Key to understanding Acumatica is recognizing that the first is used in BQL queries and statements, while the second is used in referring to the value in a record.
For example, our Inventory Item DAC for the inventory field would look like this:
namespace PX.Objects.IN
{
public partial class InventoryItem : PX.Data.IBqlTable
{
…
public abstract class itemType : PX.Data.BQL.BqlString.Field<itemType> { }
public virtual String ItemType {get; set;}
We use the lowercase itemType when querying with BQL. We would use the uppercase version when getting or setting the data from the Cache aka current session.
In the below example, we cast the current opportunity record to the InventoryItem DAC, then check that the record was not null, and finally validate that the current item selected is a labor item, i.e., equal to the constant LaborItem.
InventoryItem item= (InventoryItem)PXSelectorAttribute.Select<CROpportunityProducts.inventoryID>(e.Cache, e.Row);
if (item != null)
{
if (item.ItemType == INItemTypes.LaborItem)
As our developer series continues, we at CS3 look forward to introducing you to more key concepts to customizing your Acumatica experience.
