We are trying to make a connection to Quantify for our BI tool. We need to see all of the information form the table. How do we achieve that connection and view the tables in a database fashion so we can retrieve our information to utilize in a BI tool.
Hi Chris. Direct connections to the database are not recommended. There’s a sample c# and VB.NET projects on the main site page that you can open and see how the API connects. What you’ll want to do is define the data that you need, then write this out to a separate data store (file, database, etc) for your BI tool to read in. If you can define the data a bit more we can give you some sample code.
Well we want the company, region, branch, staging, job, tag, owner, catalog, pn, description, qty at job qty available, list at job, list available to begin with. In the near future we will need to be able to see all shipments historically.
Great, all do-able with the API. You can start by opening up the sample project, which lists branch offices and jobsites in a command window. Have a poke around this site as well, there’s a lot of sample code that shows shipments and stocking location balances.
Regards
Is there someone that I can send a sample extract that we are looking for. We will need some guidance to get the data in the right format to be inserted into another database that the BI tool will connect to.
Hi Chris. Yes, we can give you sample code on how to export. Just let us know what columns you want, and in what language (C# or VB.NET).
Here is a sample of what we are looking for. We would prefer a VB.net that will extract the data and insert into another database with the insert date as one of the fields as well.
Hi Chris. Here’s some sample code below. You’ll want to copy/paste this into the sample VB.NET project.
I’m not sure where you get ‘Company’ and ‘Region’ from. If they’re parent items for the location, then you can use the .Parent… property of the branch or job to traverse back up the hierarchy.
Code snippets below write out to the console. What you’ll want to do is write out to your database instead. Also, for scaffold jobs after you get the jobsite you’ll want to traverse through all of the tags on the job, just put another nested loop within the job there. You can read this post Weights on Tag Shipments for a Project on how to do that.
' Get list of branch offices
Dim branches As StockingLocationList =
StockingLocationList.GetBranchOfficesAndLaydownYards(False, Guid.Empty)
' Loop through the branches
For Each branch As StockingLocationListItem In branches
' Get the stocked products for the branch
Dim stocks As StockedProductList =
StockedProductList.GetStockedProductList(branch.StockingLocationID,
Guid.Empty, ProductType.Product)
' Write out all of the items
For Each stock As StockedProductListItem In stocks
Console.WriteLine("Branch: " + branch.Name)
Console.WriteLine(Convert.ToString(stock.OwnerName) + " " +
Convert.ToString(stock.CategoryName) + " " +
stock.PartNumber + " " + stock.Description + " " +
Convert.ToString(stock.QuantityForRent) + " " +
Convert.ToString(stock.ListForRent))
Next
Next
' Get list of jobs
Dim jobs As StockingLocationList =
StockingLocationList.GetJobsites(False, JobTreeNodeDisplayType.Name, Guid.Empty)
' Loop through jobs and write name to console window
For Each job As StockingLocationListItem In jobs
' Get the stocked products for the branch
Dim stocks As StockedProductList =
StockedProductList.GetStockedProductList(job.StockingLocationID,
Guid.Empty, ProductType.Product)
' Write out all of the items
For Each stock As StockedProductListItem In stocks
Console.WriteLine("Job: " + job.Name)
Console.WriteLine(Convert.ToString(stock.OwnerName) + " " +
Convert.ToString(stock.CategoryName) + " " +
stock.PartNumber + " " + stock.Description + " " +
Convert.ToString(stock.QuantityOnRent) + " " +
Convert.ToString(stock.ListOnRent))
Next
NextThis helps. It needs to be tabular and I need to be able to populate every record with data from the hierarchy.
For example I need each product on each line to have the Branch, Job and Scaffold tag. I have also attached a screenshot of our hierarchy. I understand how to go up one level but how do I go all of the way up for each part number.
Hi Chris. In this case start with the jobs on the outer loop, which is the lowest level data, then traverse back up into the branches, staging areas, and corporate nodes in loops nested within each other. By the time you get to the lowest level of looping, you’ll be able to write out all of the data you need.
I get as far as the branch but how do I get farther up the hierarchy. I do not see a parent property of the branch to go further up.
One thing to note first is that there’s an object named CorporateNode, which is the hierarchy of all of the blue nodes above the branches.
CorporateNode, StockingLocation, and BusinessPartner (customers and vendors) all inherit from an object named TradingPartner. This is a generic object that represents each item in the treeview.
The StockingLocation object (branch office in your case) has a property named ‘ParentTradingPartner’. This object has a TradingPartnerType which you can use to detect the type, but above a branch it will always be CorporateNode. The ParentTradingPartner also has a ParentTradingPartner object, which will continue to return a trading partner until null (Nothing in VB).
I am part of the way there. I really don’t understand the corporate node object and the trading partner. Here is what I have so far Which gets me to the regional level. How do I get the rest of the way up the hierarchy.
’ Get list of jobs
Dim jobs As StockingLocationList =
StockingLocationList.GetJobsites(False, JobTreeNodeDisplayType.Name, Guid.Empty)
' Loop through jobs and write name to console window
For Each job As StockingLocationListItem In jobs
' Get the stocked products for the branch
' Get list of scaffolds for the job
Dim tags As ScaffoldTagCollection = ScaffoldTagCollection.GetScaffoldTagCollection(job.StockingLocationID, False)
For Each tag As ScaffoldTag In tags
Next
'Get immediate parent Branch or Laydown Yard
Dim branches As StockingLocationList =
StockingLocationList.GetBranchOfficesAndLaydownYards(False, Guid.Empty)
' Loop through the branches
For Each branch As StockingLocationListItem In branches
Dim TradPart = TradingPartner.GetTradingPartner(branch.ParentTradingPartnerID)
Dim stocks As StockedProductList =
StockedProductList.GetStockedProductList(job.StockingLocationID,
Guid.Empty, ProductType.Product)
For Each stock As StockedProductListItem In stocks
NextTo get the node above TradPart you’ll use TradPart.ParentTradingPartnerID
You could do this recursively but if there’s only two or three nodes above that you could simply use three or four different variables.
' Parent above tradpart
Dim firstAbove As TradingPartner = TradingPartner.GetTradingPartner(TradPart.ParentTradingPartnerID)
If firstAbove.ParentTradingPartnerID <> Guid.Empty Then
Dim secondAbove As TradingPartner = TradingPartner.GetTradingPartner(firstAbove.ParentTradingPartnerID)
End IFThat works great for getting the firstAbove but when I writeline secondAbove stated that it is not defined. Is there something I am missing.
Module Program
Sub Main()
' Clear principal cache by logging out
AvontusPrincipal.Logout()
Try
Dim success As Boolean = AvontusPrincipal.Login("username, "password")
If success Then
Console.WriteLine("Login successful")
Else
Console.WriteLine("Login failed")
Console.WriteLine("Press any key to continue...")
Console.ReadLine()
Return
End If
Catch ex As Exception
Console.WriteLine("Error logging in: " + ex.Message)
Console.WriteLine("Press any key to continue...")
Console.ReadLine()
End Try
' Get list of jobs
Dim jobs As StockingLocationList =
StockingLocationList.GetJobsites(False, JobTreeNodeDisplayType.Name, Guid.Empty)
' Loop through jobs and write name to console window
For Each job As StockingLocationListItem In jobs
' Get the stocked products for the branch
' Get list of scaffolds for the job
Dim tags As ScaffoldTagCollection = ScaffoldTagCollection.GetScaffoldTagCollection(job.StockingLocationID, False)
For Each tag As ScaffoldTag In tags
Next
'Get immediate parent Branch or Laydown Yard
Dim branches As StockingLocationList =
StockingLocationList.GetBranchOfficesAndLaydownYards(False, Guid.Empty)
' Loop through the branches
For Each branch As StockingLocationListItem In branches
Dim TradPart = TradingPartner.GetTradingPartner(branch.ParentTradingPartnerID)
Dim stocks As StockedProductList =
StockedProductList.GetStockedProductList(job.StockingLocationID,
Guid.Empty, ProductType.Product)
' Parent above tradpart
Dim firstAbove As TradingPartner = TradingPartner.GetTradingPartner(TradPart.ParentTradingPartnerID)
If firstAbove.ParentTradingPartnerID <> Guid.Empty Then
Dim secondAbove As TradingPartner = TradingPartner.GetTradingPartner(firstAbove.ParentTradingPartnerID)
End If
For Each stock As StockedProductListItem In stocks
Next
' Write out all of the items
For Each stock As StockedProductListItem In stocks
Console.WriteLine("Corporate: " + secondAbove.Name)
Console.WriteLine("Business Unit: " + firstAbove.Name)
Console.WriteLine("Region: " + TradPart.Name)
Console.WriteLine("Branch:" + branch.Name)
Console.WriteLine("Job: " + job.Name)
Console.WriteLine("ScaffoldTag: ")
Console.WriteLine(Convert.ToString(stock.OwnerName) + " " +
Convert.ToString(stock.CategoryName) + Chr(9) +
stock.PartNumber + Chr(9) + stock.Description + Chr(9) +
Convert.ToString(stock.QuantityOnRent) + Chr(9) + Chr(9) +
Convert.ToString(stock.ListOnRent) + Chr(9) + Convert.ToString(stock.TotalPieces) + Chr(9) + Convert.ToString(stock.LocationName) + Chr(9) + DateTime.Now)
Next
Next
Next
' Write lines and pause
'Console.WriteLine("")
'Console.WriteLine("")
Console.WriteLine("Press any key to continue...")
Console.ReadLine()
End Sub
End Module
OK I figured this out so far. But how can I extract it to an excel file or an access DB.
Hi Chris. Sorry for the delay before, glad you got it figured out. For saving to an Access Database I’d use the OleDb objects in .NET. Once you create your connection string and whatnot you’ll use standard T-SQL insert/update/delete commands, i.e. INSERT INTO [TableName] ({fieldNames…}) VALUES (stockingLocation.Name …)
Do you have an example of this type of connection. Do we do it in the API or in Access. Is there a way to do it in Excel or on SQL server. Do you have any documentation on the API so I don’t have to ask a ton of questions on how to interact with the API.
Hi Chris. Feel free to ask as many questions as you’d like. We prefer to use this site instead of API docs.
Reading and writing data to Access, SQL, or Excel is done external to Quantify and depends on what/how you want to do it. You’ll want to do a Google search on “VB.NET Write to MS Access” or something similar and you’ll get a ton of results. I’ve found http://stackoverflow.com/ to be very helpful on this as well.
How would I pull only active jobs and active branches?