I need a script (or series) to allow my company to export detailed invoice data, including tag header information) into either MS Access, MS Excel, or a csv file so that I can utilize the data to merge with data from our payroll system.
I need the export to include the below fields, which may or may not be all inclusive…
Company
Branch
Laydown Yard
Staging Area
Customer
Invoice Number
Invoice Date
Customer PO / WO Number
Customer PO Approved Limit
Customer PO Reviewer
Customer PO Approver
Customer WO Approved Limit
Customer WO Reviewer
Customer WO Approver
Tag Number
Shipment Number
Part Number
Description
Quantity
From Date
To Date
Days
Daily Rate
Taxes
Please advise. Thank you.
Hi jstanich. Used tab separated instead as there are lots of places in Quantify where a comma can occur.
Attached is sample code for this. This code fetches all of the invoices that haven’t been exported (there’s an ExportStatus for this). After that, it creates a line for each part on each invoice, then saves the invoices to ExportStatus = Exported.
There’s no headers there, you can easily make a line for this.You’ll want to debug this to make sure the columns line up. This should get you started.
Quantify.API.InvoiceExport.cs.zip (7.5 KB)
So far so good…
A few more questions:
Is there a way you could provide this using VB code?
Can you advise on how I can make it so I how I would modify the code to only export data relating to specific ’ Job Site Number’ and a specific ‘Up To Date’?
Can you provide a sample of how the data would look in the export so I can determine what data is actually exported? My company has yet to actually create invoices (yet).
Sorry, would have written this in VB.NET if I knew. You can copy/paste that code into a C# to VB.NET converter. A good one is here: http://www.developerfusion.com/tools/convert/csharp-to-vb/
The sample code exports a file. If you run it on your data you’ll see it exported in the \bin\debug\ folder, there’s a text file there. You’ll want to make sure that you comment out the code that modifies existing invoices and set them to ‘Exported’.
Yes, if you want to filter the job, within the invoice loop add this conditional (VB.NET):
For Each inv As Invoice In invoices
If inv.JobNumber = "YOURVALUE" And inv.UpToDate = "m/dd/yyyy" Then
' Remaining code here.
End If
Next
After converting, in this snippet:
If prod.ScaffoldTagID <> Guid.Empty Then
line += ScaffoldTag.GetScaffoldTag(prod.ScaffoldTagID) & vbTab
Else
line += vbTab
End If
ScaffoldTag.GetScaffoldTag(prod.ScaffoldTagID) & vbTab is underlined in red with the message Operator ‘&’ is not defined for types ‘ScaffoldTag’ and ‘String’.
Please advise.
You’ll want to put use the .Tag property to get the text. You can change that line to this:
line += ScaffoldTag.GetScaffoldTag(prod.ScaffoldTagID).Tag & vbTab
In the snippet
If inv.JobNumber = “YOURVALUE” And inv.UpToDate = “m/dd/yyyy” Then
inv.UpToDate is red underlined with UpToDate is not a member of Invoice
Please advise.
You can use this:
inv.InvoiceDateTime = New DateTime(yyyy, mm, dd)
Thank you so much for all your help. I have been able to create an VB.net application to pull the required data.
Regarding this same export Routine.zip (1.5 KB) , can you advise how to add the following data to the export?
Form Tag: the Priority item, Project item, Step item, Dimension Information (Length, Width, Height…), Status, Notes
From Shipment: the Item Number Unit Weight and the Shipment Date.
Any guidance is truly appreciated.
You’re very welcome, glad to help. To get the scaffold properties you can load the scaffold object, sample below.
Dim scaf As ScaffoldTag = ScaffoldTag.GetScaffoldTag(prod.ScaffoldTagID)
Debug.Print("Length: " + scaf.Length)
Debug.Print(scaf.ProjectName)
Debug.Print(scaf.PriorityName)
And it continues…
Up to now, I have gotten all the invoice data I needed to extract working beautifully.
But now, I need more; for the Invoices in the Invoice collection, I need to include:
Corporate Name and Description (which may be the same as Company???)
Branch Office Name, Branch Office Number, Shipping Address, and Description
Staging Area Name, Staging Area Number, and Shipping Address
Job Site Name, Job Site Number, Shipping Address (I have the Name and Number already)
Again thank you for your support.
Hi James. I’m not sure what you mean by corporate name and description. Is that from Options? For the other stocking location properties, they’re all there. You might want to type in the object name “job” in the code below, then the “.” and you’ll see an intellisense list of properties. This might help you to find them. They’re pretty much 1:1 with the UI, so if you edit a job in the UI you’ll see a list of addresses. Code sample below.
' Global options company name
GlobalClientConfig.OptionsSettings.CompanyName
' Shipping Address
Console.WriteLine(job.Addresses.GetAddressByType(AddressTypes.Shipping).Street)
Console.WriteLine(job.Addresses.GetAddressByType(AddressTypes.Shipping).Street1)
Console.WriteLine(job.Addresses.GetAddressByType(AddressTypes.Shipping).City)
Console.WriteLine(job.Addresses.GetAddressByType(AddressTypes.Shipping).StateName)
' Parent branch, staging area, or laydown. This bypasses all of the parent jobs for the
' job, assuming that it's a group or a sub-grouped job
If job.ParentBranchOrLaydownOrStagingArea.TradingPartnerType = PartnerTypes.StagingArea Then
Console.WriteLine("Staging Area: " + job.ParentBranchOrLaydownOrStagingArea.Name)
ElseIf job.ParentBranchOrLaydownOrStagingArea.TradingPartnerType = PartnerTypes.BranchOffice Then
Console.WriteLine("Branch Office: " + job.ParentBranchOrLaydownOrStagingArea.Name)
End If
' This returns the parent regardless of the type
Console.WriteLine(job.ParentStockingLocation.TradingPartnerTypeText)
' You can also get the parent of a parent
If Not job.ParentStockingLocation.ParentStockingLocation Is Nothing Then
Console.WriteLine(job.ParentStockingLocation.ParentStockingLocation.TradingPartnerTypeText)
End If
' And the the great grandparent
If Not job.ParentStockingLocation.ParentStockingLocation Is Nothing Then
Console.WriteLine(job.ParentStockingLocation.ParentStockingLocation.TradingPartnerTypeText)
End If
I still don’t understand why I cannot get the data I am after.
My structure is:
Company
For an Invoice, I am trying to get the Branch Name, but no matter what I try, even using the code provided up to this point, I am still get the name of the Branch.
What am I doing wrong???
Hi James. Apologies, I steered you in the wrong path a bit. Given the screen grab below, the following code:
' Show each location
Console.WriteLine(job.Name)
Console.WriteLine(job.ParentBranchOrLaydownOrStagingArea.Name)
Console.WriteLine(job.ParentBranchOrLaydown.Name)
Console.WriteLine(job.ParentBranchOrLaydown.ParentTradingPartner.Name)
Prints out this to the console window:
Job A
Staging B
Branch A
My Company
Also, a handy tool is to right click on the “job” in Visual Studio and select “Quick Watch”. This will display a browser for the object. You can expand the stocking locations to see others. Pretty cool feature.
![]()
1 Like
Awesome!!! VERY MUCH APPRECIATED!!! Finally able to retrieve the final portion if the requirement.
Until my next thread…
1 Like
