Total weight of rental material outstanding per end of month on invoice

#1

Dear all,

Several of our sales organizations and their customers request us to have the total weight of rental material at a customer per end of month on the invoice.
Is there a way how to add this in a report using the report designer?
It would even be better if we could get it using the API.

Please note that this is not the same as the weight of all the items on the invoice as the invoice also contains deliveries and returns within that month.

Looking forward to a reply.

Regards,

Milan van Hoof

#2

Hello Milan van Hoof. Yes, this is possible through the API using the same data that is available through the Shipment Pivot for Jobsite report. I will write up a bit of sample code and send it over to you here in the next day or so.

Regards,
AvontusDev

#3

OK, here is some sample code. You’ll want to run this code after signing in (see our sample project).
There are two variables in here you’ll want to replace, first is the jobsite name and the other is the date.

This list returns everything in the shipment pivot for jobsite, filtered on that date, with the quantity combined into each part. You could think of this list as the total. Screen grab below shows how to filter by date to compare/test.

image

// Get the trading partner by name, and then the jobsite
TradingPartner tp = TradingPartner.GetTradingPartner("Carpenteria");
StockingLocation job = StockingLocation.GetStockingLocation(tp.StockingLocationID, false);

// List of locations (single one in our case)
List<Guid> locations = new List<Guid>
{
    job.StockingLocationID
};

// Get the list of shipment product. This is the same that
// is used by the "Shipment Pivot for Job Site"
ShipmentProductPivotList pivotProducts = ShipmentProductPivotList.GetShipmentProductPivotListForLocation(tp.TradingPartnerID,
    job.StockingLocationID, locations, true);

// Add a new list to filter the items to a specific date
List<ShipmentProductPivotListItem> filteredItems = new List<ShipmentProductPivotListItem>();
        
// 3/3/2009 - Use what date you want here
DateTime maxDate = new DateTime(2009, 3, 3);

// Add items to list
foreach (ShipmentProductPivotListItem item in pivotProducts)
{
    if (item.ActualShipDate <= maxDate)
    {
        filteredItems.Add(item);
    }
}

// Add a new list to store the combined list of products from the pivot
List<ShipmentProductPivotListItem> products = new List<ShipmentProductPivotListItem>();

// For each shipment product in the filtered list created above
foreach (ShipmentProductPivotListItem shipmentProduct in filteredItems)
{
    // If the part from the filtered list isn't in the list then add it
    if (products.FirstOrDefault(x => x.PartNumber == shipmentProduct.PartNumber) == null)
        products.Add(shipmentProduct);
    // Else it is in the list so add to the quantity
    // Note that returns are negative
    else
        products.First(x => x.PartNumber == shipmentProduct.PartNumber).ActualQuantity += shipmentProduct.ActualQuantity;
}

// Order by part number
products = products.OrderBy(x => x.PartNumber).ToList();

foreach (ShipmentProductPivotListItem item in products)
    Console.WriteLine($"{item.ShipmentNumber}\t{item.PartNumber}\t{item.ActualQuantity}" );
                    
Console.WriteLine("");
Console.WriteLine("");
Console.WriteLine("Press any key to continue...");
Console.ReadLine();