Staging Area Inventory extraction

Need assistance regarding the following:

  1. How might I extract part number information (part number, description, quantity available, unit weight, unit list price, unit cost) for a given organization and/ or staging beneath a parent organization utilizing the API?

  2. How might I extract Scaffold Register header and shipping details (including date, part number and quantities) for a given job?

Using VB.NET.

Any and all advise welcome.

Hi James. To do this you’ll fetch a trading partner, then use it to fetch the organization, branch, staging area, or job. Once you have that you can loop through the StockedProducts on that object and get what you need.

    ' The root level generic entity is called a trading partner, which must
    ' be fetched first. You can fetch anything in the treeview with this name.
    Dim tp As TradingPartner = TradingPartner.GetTradingPartner("CorpNodeName_Here")

    ' Make sure to check the name after you've fected it. if
    ' the name doesn't match then it wasn't fetched
    If tp.Name <> "CorpNodeName_Here" Then
        Throw New Exception("Item not found")
    End If
    ' After the trading partner is fetched you can get the corporate node
    ' from the trading partner ID
    Dim corp As CorporateNode = CorporateNode.GetCorporateNode(tp.StockingLocationID)

    If corp.Name <> "CorpNodeName_Here" Then
        Throw New Exception("Item not found")
    End If

    ' For anything other than a corporate node us this
    Dim loc As StockingLocation = StockingLocation.GetStockingLocation(tp.StockingLocationID, False)

I know this is asking a lot, but can you elaborate a bit more on loop through the StockedProducts on that object and get what you need…

I assume you mean iterating Avontus.Rental.Library.StockedProductCollection but I have no idea how…

Okay, I can do it in SQL (see query below) which I know is not supported, but how do I achieve the same using API?

SELECT        StockingLocation.Number, TradingPartner.Name, StockedProduct.QForRent, Product.PartNumber, Product.Description, Product.Weight, Product.DefaultSell, Product.DefaultReplacementCost, 
                     Product.DefaultAverageCost, Product.DefaultCost
FROM            StockedProduct INNER JOIN
                     StockingLocation ON StockedProduct.StockingLocationID = StockingLocation.StockingLocationID 
                     TradingPartner ON StockingLocation.StockingLocationID = TradingPartner.StockingLocationID
                     Product ON StockedProduct.BaseProductID = Product.ProductID
WHERE        (StockedProduct.QForRent > 0) AND (StockingLocation.Number = '2830199')
ORDER BY TradingPartner.Name, Product.PartNumber

Hi James. Attaching directly to the database and running SQL is not supported and will nullify your support contract. To loop through the products is very simple. Sample code below. You might want to experiment with intellisense, all of the properties should match the default UI.

    Dim tp As TradingPartner = TradingPartner.GetTradingPartner("JobName")
    Dim job As StockingLocation = StockingLocation.GetStockingLocation(tp.StockingLocationID, False)

    If job.Name <> "JobName" Then
        Throw New Exception("JobName not found")
    End If

    For Each prod As StockedProduct In job.StockedProducts

Thank for the code, as I was going down the wrong path through the API anyway. I realized the SQL query route is not supported, which is why I asked for additional assistance.

At any rate, thanks again.

and the fun continues…

Using this:

  Dim StockLocation As StockingLocation = StockingLocation.GetStockingLocation(str_Stage_Area_Number)

            If StockLocation.Number <> str_Stage_Area_Number Then

                Throw New Exception("Staging Area not found")

            End If

            For Each prod As StockedProduct In StockLocation.StockedProducts



I get this:

prod.QuantityOnRent = Nothing
prod.Description = Nothing
prod.DefaultSell = Nothing



has values…

So the item exists, but it doesn’t???


Staging areas have prod.QuantyForRent, so the QuantityOnRent will be null (nothing).

prod.QuantityForRent = Nothing too…

Odd, this is working for me. After you fetch the staging area is it populated with the correct data (name, number, address)?

Also, you may want to rename your variable from StockingLocation (which is a library class) to something like stock, this way the variable and the object name won’t collide. Possible (but rare) that this would have a compile issue and is causing your problem.

StockLocation.Number returns expect value
StockLocation.Name returns expect value

Per your suggestion, changed StockLocation object to SL and still prod.QuantityForRent = Nothing

Dim sl As StockingLocation = StockingLocation.GetStockingLocation(str_Stage_Area_Number)

If sl.Number <> str_Stage_Area_Number Then

    Throw New Exception("Staging Area not found")

End If

For Each prod As StockedProduct In sl.StockedProducts



Can you send a backup of your database to our support department and also let them know on the support ticket what staging area you’re trying to retrieve? I’ll use your sample code here to troubleshoot. Thanks.

I uploaded the database last week but have not heard anything back from anyone.

Hi James. Yes, I have received it. Can you let me know the name of the staging area that you’re trying to fetch?

Looks like we may have an issue with the description not showing up,. All of the other properties are correct. In order to work around this you can simply load the product and use the property direct from it.

    For Each prod As StockedProduct In staging.StockedProducts

        Dim productFromCatalog As Product = Product.GetProduct(prod.ProductID)
        Console.WriteLine(prod.PartNumber + " - " +
                          productFromCatalog.Description +
                          " Weight: " + prod.Weight.ToString() +
                          " Qty: " + prod.QtyForRentOriginal.ToString())