reference custom field in bql statement

Hi ​@justen0351,

Please use below code snippet.

  public SelectFrom<MaterialRequest>
.LeftJoin<AMProdItem>
.On<Brackets<MaterialRequest.materialID.IsEqual<AMProdItem.inventoryID>>
    .And<MaterialRequest.siteID.IsEqual<AMProdItem.siteID>>
    .And<Brackets<AMProdItem.statusID.IsEqual<constantP>>>
    .Or<AMProdItem.statusID.IsEqual<constantR>
        .And<MaterialRequest.requestID.IsEqual<AMProdItemExt.usrSawReq>>
    >>
.Where<MaterialRequest.createdDateTime.IsGreater<MyDate>>
.OrderBy<Asc<MaterialRequest.requestID>>
.View PTMMaterial;

Hope, it helps!

Hello ​@justen0351 ,

Solution given by ​@Nilkanth Dipak will solve your problem for sure, but I want to explain how it works in Acumatica Framework and BQL.

1. BQL and Abstract Classes for Properties

   • In Acumatica, each DAC property is represented in BQL by its own abstract class, which maps to a real database column name.

   • When you type AMProdItem., you only see the base DAC’s properties—your custom fields (which live in your extension) aren’t listed there.

2. CacheExtensions to the Rescue

   • To expose your custom fields in BQL, you define a CacheExtension (e.g., AMProdItemExt).

   • There’s no separate SQL table for your extension; under the hood, Acumatica still queries the AMProdItem table.

3. Why Your Filter Needs the Extension Class

   • In a Where<…IsEqual<…>> filter, BQL expects a column name (i.e., an abstract class for a property), not a table name.

   • Since your LeftJoin<…> already set the table to AMProdItem, BQL just needs to know which column to compare.

   • By referencing AMProdItemExt.usrSawReq, you’re pointing BQL to the correct abstract property class, which it translates to the real usrSawReq column name in AMProdItem.

Always use your extension’s abstract classes for properties (e.g., AMProdItemExt.usrSawReq) when filtering on custom fields. The system already knows the table; your extension tells it which column name to use.

Hope that clears it up! Let me know if you have any other questions.

​@Nilkanth Dipak thank you for your response. i tried putting that code in and it looks like it recognizes the field name now but not the extension itself . it already has the namespace definied (px.objects.am) so i tried adding a using directive for the extension but that didnt work. screenshot below. any idea on what i am missing or doing wrong?

​@harutyungevorgyan thank you for the explanation I really appreciate it. that makes alot more sense to me now. i have been struggling with the references in here. i previously couldnt get this code to reference the table itself (materialrequest) but i rewatched the T200 series and realized that i had added the using directive but hadnt actually added the reference to the reference section. that had solved that, but in this case, i have added the table itself so its confusing why it doesnt recognize it.Knowing that i have to reference the extension helps alot, but it seems im still missing something.

​@justen0351 it's strange because you already use PX.Objects.AM could you please tell me if the code you show is in your dll or in the package, I mean in AppRuntimeCode folder of your instance?

Because if your DAC extension is in the package and you want to have access to it from the Visual Studio, from your actual dll where graph is located, it can be the reason of your problem.

​@harutyungevorgyan the code for the new screen is in a dll. i didnt move the graph extension to a dll. do i need to move it or possibly put it in the same customization project and then move it over?

​@harutyungevorgyan thank you very much! moving it to the dll with everything else made it find the reference. i dont think the join is working right, but i can play with it a little and get it working.