What is the downside of using SQL alongside BQL in Acumatica

Question

I have a very specific user request I have been working on. The end goal is to change a user defined field on FSServiceOrder, FSSODet, FSAppointment, FSAppointmentDet based upon inventory availability in INLocationStatus.

I have a processing screen for this, since it needs to run on demand, or on a schedule. Note: the user never really interacts with anything on the processing screen. He will always simply click: "Process All"

Using BQL for this seems to be working against the grain.

Using a Join in my Processing event:

    public PXProcessingJoin<INLocationStatus, 
         LeftJoin<InventoryItem, On<INLocationStatus.inventoryID, 
         Equal<InventoryItem.inventoryID>>>>   Processing;

Isn't very helpful as a starting place.

I cannot even get the value from the joined table without running a separate BQL inside of the processing event. (If I understand correctly. I found no way of doing it, and this SO seems to indicate that it is not possible.)

So I have to run a BQL inside of my processing Delegate, just to get the inventory information. I have to also do several BQLs as I process each of my DACs -- and I have to create several PXGraphs as well.

This seems like a LOT of overhead -- when all I am really doing is reading data into a memory cache, processing it, and then setting a user defined field.

I understand that the value in using BQL is it ensures all of the business logic is performed, but in my case, that doesn't apply. There is no business logic that needs to be run, that I can determine. I am simply running through rows of data, calculating my own available inventory value in memory, and then setting a usr defined flag in the database.

Wouldn't I be better off just calling to SQL directly, getting my data, and then updating the flags? If I do that, does it cause issues I am not considering? Or does it create huge problems down the road?

Has anyone else mixed BQL and SQL in a processing screen?

Kyle Vanderstoep Kyle Vanderstoep · Accepted Answer · 2021-05-24T20:22:06

Mark,

If you are confident that updating your user defined fields will not trigger unintended consequences, then I wouldn't worry about it. If you need to get your code certified by Acumatica however they will not support it. I think the only unintended consequence by updating the database directly will be that it will not notify the application cache that a change has been made (Unless the PXDatabase functions do that and I just didn't realize) and another user might get a "Another processes has updated the ... record" error when they try to edit the same record.

What is the downside of using SQL alongside BQL in Acumatica

2 Answers