Solved

Retrieve data from InventorySummaryEnquiryResult

  • 6 November 2020
  • 7 replies
  • 887 views
SadokHanini
Rank
Userlevel 2

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”

icon

Best answer by Naveen Boga 6 November 2020, 14:53

View original

7 replies

Vinay Koppula
Rank
Userlevel 5
Badge +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. 

 

Vinay K
SadokHanini
Rank
Userlevel 2

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
Rank
Userlevel 7
Badge +17

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

 

Naveen B
SadokHanini
Rank
Userlevel 2

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
Rank
Userlevel 7
Badge +17

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

Naveen B
BenjaminCrisman
Rank
Userlevel 7
Badge +4

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
Rank
Userlevel 5
Badge +2

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


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved