Skip to main content
Solved

Retrieve data from InventorySummaryEnquiryResult

  • 6 November 2020
  • 7 replies
  • 950 views

SadokHanini
Freshman II

Im using this query to get data from InventorySummaryEnquiryResult


var query = new PXSelectReadonly<InventorySummaryEnquiryResult>(new PXGraph()).Select<InventorySummaryEnqFilter>().Where(s => s.SiteID == 13).FirstOrDefault();

 

But i have an error message in the app 
Invalid object name “InventorySummaryEnquiryResult”

Best answer by Naveen Boga

Hi @SadokHanini ,

To get the unitcost of a Stock Item, please use the below code.

 

  INItemCost objINItemCost =PXSelect<INItemCost, Where<INItemCost.inventoryID, Equal<Required<INItemCost.inventoryID>>>>.Select(base, row.InventoryID);
                        if (objINItemCost != null)
                        {
                            var UnitCost = objINItemCost.LastCost;
                        }

 

Best Regards,

Naveen B

View original
Did this topic help you find an answer to your question?

7 replies

Vinay Koppula
Semi-Pro II
Forum|alt.badge.img+1

Hi SadokHanini,

Change the PXSelectReadonly with PXSelect and try in your query, because PXSelectReadonly fetches the data directly from the Database but InventorySummaryEnquiryResult is not a Acumatica DB table. 

Hope this will helps you to fix the issue. 

 


SadokHanini
Freshman II
  • Author
  • Freshman II
  • 42 replies
  • November 6, 2020
vinayrajk wrote:

Hi SadokHanini,

Change the PXSelectReadonly with PXSelect and try in your query, because PXSelectReadonly fetches the data directly from the Database but InventorySummaryEnquiryResult is not a Acumatica DB table. 

Hope this will helps you to fix the issue. 

 

Same Error 
PXResultset<InventorySummaryEnquiryResult> res = PXSelect<InventorySummaryEnquiryResult>.Select(new PXGraph());


Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3377 replies
  • November 6, 2020

Hi @SadokHanini,

You want to get the QtyAvailable value based on the warehouse and inventory?

If yes, the below code will help you to get the QtyAvailable value from the database

 

 PXSelectGroupBy<INSiteStatus, Where<INSiteStatus.inventoryID, Equal<Required<INSiteStatus.inventoryID>>>,                                                              Aggregate<GroupBy<INSiteStatus.inventoryID, Sum<INSiteStatus.qtyAvail>>>                                     .Select(graph, row.InventoryID);

 

Best Regards,
Naveen B

 


SadokHanini
Freshman II
  • Author
  • Freshman II
  • 42 replies
  • November 6, 2020
naveenb74 wrote:

Hi @SadokHanini,

You want to get the QtyAvailable value based on the warehouse and inventory?

If yes, the below code will help you to get the QtyAvailable value from the database

 

 PXSelectGroupBy<INSiteStatus, Where<INSiteStatus.inventoryID, Equal<Required<INSiteStatus.inventoryID>>>,                                                              Aggregate<GroupBy<INSiteStatus.inventoryID, Sum<INSiteStatus.qtyAvail>>>                                     .Select(graph, row.InventoryID);

 

Best Regards,
Naveen B

 

Thanks :) 
I need also to get UnitCost
Do u have an idea how ?

thanks

 


Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3377 replies
  • Answer
  • November 6, 2020

Hi @SadokHanini ,

To get the unitcost of a Stock Item, please use the below code.

 

  INItemCost objINItemCost =PXSelect<INItemCost, Where<INItemCost.inventoryID, Equal<Required<INItemCost.inventoryID>>>>.Select(base, row.InventoryID);
                        if (objINItemCost != null)
                        {
                            var UnitCost = objINItemCost.LastCost;
                        }

 

Best Regards,

Naveen B


BenjaminCrisman
Acumatica Employee
Forum|alt.badge.img+4
  • Acumatica Support Team
  • 625 replies
  • November 6, 2020

Hi @SadokHanini ! InventorySummaryEnquiryResult is an Acumatica DAC used on that screen, but isn’t in SQL, but created to show some data in a particular view.

As Naveen included in the above suggestions, if you are looking for the quantities of items then check INSiteStatus:

If you are looking for cost of items then I would check INCostStatus:

 

These are also SQL tables, so they can be found here to check the database values stored, but Acumatica DACs will not be found in SQL and to check their contents you can try loading the DAC in a GI by itself to check the values, though sometimes the nature of the DAC doesn’t really work well in a GI environment and is more intended use is the screen it is used on.

Alternatively, you can also check the Customization > Source Code > Data Access screen to check the contents of an Acumatica DAC to see other helpful information about it:

 


Hughes Beausejour
Acumatica Employee
Forum|alt.badge.img+2
  • Acumatica Developer Support Team
  • 91 replies
  • November 6, 2020

InventorySummaryEnquiryResult is an unbound DAC. Any select operation will fail because there’s no table bound to that DAC in database.

In graph InventorySummaryEnq the method iSERecordsFetch is executing select methods on other tables and dynamically creating InventorySummaryEnq records in memory with that data.

You can either ignore InventorySummaryEnquiryResult and write your own BQL queries from scratch or reverse engineer the queries in InventorySummaryEnq.iSERecordsFetch method (excerpt below) to understand how the data is fetched:

protected virtual IEnumerable iSERecordsFetch()
{
   [ … ]

    PXSelectBase<INLotSerialStatus> cmd_lss = new PXSelectReadonly2<INLotSerialStatus,
        InnerJoin<INLocation,
            On<INLotSerialStatus.FK.Location>,
        InnerJoin<InventoryItem,
            On<InventoryItem.inventoryID, Equal<INLotSerialStatus.inventoryID>,
            And<Match<InventoryItem, Current<AccessInfo.userName>>>>,
        InnerJoin<INSite,
            On2<INLotSerialStatus.FK.Site,
            And<INSite.siteID, NotEqual<SiteAttribute.transitSiteID>,
            And<Match<INSite, Current<AccessInfo.userName>>>>>,
        InnerJoin<INSubItem,
            On<INLotSerialStatus.FK.SubItem>,
        LeftJoin<INLotSerClass,
            On<InventoryItem.FK.LotSerialClass>,
        LeftJoin<INLocationCostStatus,
            On<INLocationCostStatus.inventoryID, Equal<INLotSerialStatus.inventoryID>,
                And<INLocationCostStatus.subItemID, Equal<INLotSerialStatus.subItemID>,
                And<INLocationCostStatus.locationID, Equal<INLotSerialStatus.locationID>>>>,
        LeftJoin<INSiteCostStatus,
            On<INSiteCostStatus.inventoryID, Equal<INLotSerialStatus.inventoryID>,
                And<INSiteCostStatus.subItemID, Equal<INLotSerialStatus.subItemID>,
                And<INSiteCostStatus.siteID, Equal<INLotSerialStatus.siteID>>>>>>>>>>>,
        Where<INLotSerialStatus.inventoryID, Equal<Current<InventorySummaryEnqFilter.inventoryID>>,
            And<Where<Current<InventorySummaryEnqFilter.expandByLotSerialNbr>, Equal<True>, And<INLotSerClass.lotSerAssign, Equal<INLotSerAssign.whenReceived>,
            Or<InventoryItem.valMethod, Equal<INValMethod.specific>>>>>>>(this);

  […]

    return resultset
        .OrderBy(x => x.SubItem?.SubItemCD)
        .ThenBy(x => x.Site?.SiteCD)
        .ThenBy(x => x.Location?.LocationCD)
        .ThenBy(x => x.Result.LotSerialNbr)
        .Select(x => x.Result);
}


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings