Product Master, jobsite master and inventory accross entire company

#1

We are looking to get an extract, csv, for inventory data, item master and branch master to upload to a datawarehouse. We are looking for some assistance to get this project going quickly so we can test and deploy.
Inventory data: YardNumber InventoryDate ItemCode QtyInYard QtyOnSite QtyInRepair TotalQty QtyReSale RentalCommitted ReSalesCommitted QtyDamaged

Item Master:ItemCode ItemDescription Prefix PrefixDescription AllocationCost Unit_Weight ListPrice
Branch Master:YardNumber YardDescription YardAddress1 YardAddress2 YardCity YardStateProvinceCode YardCountryCode YardPostalCode Division Region SubRegion Area Branch BaseCurrency

I want to use C# for this so any assistance would be greatly appreciated.

#2

Hello Chris. We’ll work up a bit of sample code for you. I’m assuming you want to iterate through all active jobs in your database. There’s a bit of sample code that does this already in our Sample C# Project if you want to start looking there. What we’ll do is show you how to fetch the products for each stocking location in this loop, and write the products out that you need.

#3

That is correct we will need all active jobs. and of course lay down yards and anything in transit.

#4

Hi, Chris.shumaker.

Please find some our existing sample code below that can used to fetch the catalog, branches, yards, staging areas and jobsites and stocked parts. Visual Studio intellisense would list all the available properties in the each class. Feel free to get in touch with us for any additional details.

//GET PRODUCT CATALOG
var products = ProductList.GetProductList(ProductType.Product);
foreach (var product in products)
{
    Console.WriteLine($"PART-NUMBER: {product.PartNumber} DESCRIPTION: {product.Description} LIST-PRICE: {product.DefaultList}");
}

//GET BRANCHES, YARDS, STAGING AREAS
StockingLocationList locations = StockingLocationList.GetBranchOfficesLaydownYardsAndStagingAreas(false, Guid.Empty);
foreach (var location in locations)
{
    //PRINT LOCATION DETAILS
    Console.WriteLine($"LOCATION: {location.Name} NUMBER: {location.Number} ADDRESS: {location.TheAddress.FullAddress}");

    //GET PARTS AT THE  BRANCH, YARD OR STAGING AREA
    var stockedProducts = StockedProductList.GetStockedProductList(location.StockingLocationID, Guid.Empty, ProductType.Product);

    //PRINT PART DETAILS
    foreach (var part in stockedProducts)
    {
        Console.WriteLine($"PART-NUMBER: {part.PartNumber} DESCRIPTION: {part.Description} QTY-FOR-RENT: {part.QuantityForRent}");
    }
}

//GET ACTIVE JOBS
StockingLocationList jobs = StockingLocationList.GetActiveJobsites(false, JobTreeNodeDisplayType.Name, Guid.Empty, false);
foreach (var job in jobs)
{
    Console.WriteLine(job.Name);

    //GET PARTS AT THE  JOB
    var stockedProducts = StockedProductList.GetStockedProductList(job.StockingLocationID, Guid.Empty, ProductType.Product);

    //PRINT PART DETAILS
    foreach (var part in stockedProducts)
    {
        Console.WriteLine($"PART-NUMBER: {part.PartNumber} DESCRIPTION: {part.Description} QTY-ON-RENT: {part.QuantityOnRent}");
    }
}

Thanks for posting.

#5

So how do we get the product catalog from the part master. we will need to get any prefix, first string before the dash and then the string after the dash. We also need this in tabular form not inline so that we can easily extract to csv for import into a datawarehouse. We also need to strip any commas from any string. I am not a c# expert so any assistance is greatly appreciated.

#6

Hi Chris. Not sure what you mean by getting a prefix. Is this some sort of special part number that you have?

Each item in the StockedProduct contains all of the details from the product master (description, weight, etc.) if that’s what you are asking? You’ll see this in the intellisense on the “var part” above, part.Description…

If you use tab separated “\t” then you won’t need to remove any commas.

#7

In the part master pivot there is a field of catalog but when I try to pull that the only thing I can see is catalogID. That does not help me.

#8

You can load the catalog to get the name, use Catalog.GetCatalog(catalogID), then from there use the .Name property

#9

How do I got the hierarchy of a jobsite, the blue roofs a jobsite is under in separate columns?
For example: our hierarchy
Merit Shop, Atlantic, Northeast, BranchOffice Name, Jobsite Name