How to construct complicated queries in BQL of Acumatica

Hello everybody,

today I want to describe the following situation. Imagine that you have to write following SQL statement in Acumatica:

SELECT InventoryItem.InventoryCD, InventoryItem.imageUrl, UploadFile.Name, NoteDoc.FileId, * FROM InventoryItem
LEFT JOIN UploadFile ON InventoryItem.imageUrl = UploadFile.Name
LEFT JOIN NoteDoc ON UploadFile.FileID = NoteDoc.FileID where imageurl is not null

I myself prefer to have way to write a code that is easy to modify and maintain. So instead of creating one big BQL statement I'd like to make it as few smaller statements. Is it achievable with Acumatica BQL? Yes, it's achievable. Take a look at following code that does exactly what I've described: little by little builds that BQL statement:

var imagesSelect = new PXSelectJoin<InventoryItem,
    LeftJoin<UploadFileOn<InventoryItem.imageUrlEqual<UploadFile.name>>>>(this);
imagesSelect.Join<LeftJoin<NoteDocOn<UploadFile.fileIDEqual<NoteDoc.fileID>>>>();
imagesSelect.WhereAnd<Where<InventoryItem.imageUrlIsNotNull>>(); var images = imagesSelect.Select(); foreach (PXResult<InventoryItemUploadFileNoteDoc> boxedImageInfo in images) {     //put some processing logic with images }

As you can see at presented code initially we created select just with one LeftJoin. In the next line we added one more LeftJoin. And finally we appended it with one more filtering condition in order not to load all data, but just load those Inventory items that have some images.

Also with such approach you can easilty create dynamic queries for Acumatica.

No Comments

Add a Comment