Hi All,
Today want to share with you one example of how you can extend Acumatica BQL operators.
As an example I will take a sub-select that will calculate count of linked entities.
Lets assume that we have a list of customers. Each of ours customers has some contracts. All contracts can be for different types of services.
And we want to have an inquiry screen where we can show total count of each type of contract for each customer separately.
Customer
|
SaaS Contracts
|
Perpetual Contracts
|
ABARTENDE
|
1
|
3
|
BLUELINE
|
2
|
1
|
AVACUS
|
5
|
0
|
To save some performance on selects, we can use sub-selects and calculate count there. This will give us ability to move all calculation to SQL server side and limit query results.
But Acumatica does not support counts in sub-selects, so we need to add some additional logic to standard operators that allows us to get it.
Code snippet:
Now then we have function, lets use it.
[PXDBScalar(typeof(Search4<Contract.contractID, Where<Contract.baseType, Equal<Contract.ContractBaseType>, And<Where<Contract.customerID, Equal<BAccount.bAccountID>, Or<ContractBillingSchedule.accountID, Equal<BAccount.bAccountID>>>>>, Aggregate<ScalarCount<Contract.contractID>>>))] public Int32 Total { get; set; }
This operator will calculate count of lines in sub-select and provide value to specified field. Field must be Integer, to be able to accept count value.
You can add this code into your DAC or Extension and use the result straightaway.
Have a nice statistic!
Very practical article. Basically it’s possible to make any kind of Group operations on SQL side instead of chunking them