How would I go about extracting the Tag Number, From Date, To Date, and Total Charge Amount details for each Tag for a specific Invoice? If Minimum Rental and Daily Rental were captured on the same invoice for the same Tag Number, I would expect two separate rows instead of a combined row.
Any help would be most appreciated as I am unable to figure out how to use the API to extract the data, which appears to be contained in the [InvoiceRentProduct] table.
The scaffold tag details for the invoice are contained within a separate collection on each invoice, called the ScaffoldTagPivotList. To obtain the minimum you’ll need to compare with each item (they’re the same, just more detail) in the InvoiceRentProducts collection on the same invoice.
' Loop through the items
For Each item As InvoiceScaffoldTagPivotListItem In inv.ScaffoldTagPivotList
Debug.Print("Tag:" + item.Tag)
Debug.Print("Rent:" + item.RentTotal)
Debug.Print("Avg:" + item.AvgDailyRent)
Debug.Print("From Date:" + item.FromDate)
Debug.Print("To Date:" + item.ToDate)
NextThank you for you reply. I have all the details that I am looking for, but I am still unable to determine if a specific invoice line for a Tag is a minimum rental charge or a daily rental charge. Ultimately, I am looking to build a single description for the invoiced line, such as “TAG 1 - (28) Minimum Rent days from 01/01/2015 to 01/28/2015 @ 0.1775 / day” or “TAG 1 - (28) Additional Rent days from 01/01/2015 to 01/28/2015 @ 0.1775 / day”. I have all the data from what you have provided except the “rental type” text…
Any ideas on how to get what I am looking to do? Is there anything the API exposes to determine if an invoice line is a MINIMUM RENTAL?
Yes, you can do this. This gets pretty complicated as you can have multiple shipments to a scaffold with minimums on all kinds of different dates. A single product can have a minimum and go into arrears on the same invoice. It can also have various rate adjustments.
To get at this data you can loop through the rent products, then load the scaffold for each. Sample below.
For Each invProd As InvoiceRentProduct In inv.InvoiceRentProducts
Debug.Print("IsMinimum:" + invProd.IsMinimum.ToString())
Dim scaffold As ScaffoldTag = ScaffoldTag.GetScaffoldTag(invProd.ScaffoldTagID)
NextNothing is ever easy, is it…?
Perhaps I am going down the wrong direction…
This is what I am ultimately looking to produce…
Customer_Name
Project_Name
PO_Number
Charge_Description
Charge_Amount
Sample Data
THIS IS A TEST
STILL A TEST
1
TAG 1, DEL 1 - (28) Min. Rent days from 01/01/2015 to 01/28/2015 @ 0.1775 / day
4.97
I am able to find bits and pieces of this information, but cannot seem to pull it all together.
In order to get what you need you will need to output multiple lines in your export for each InvoiceRentProduct. If you take a look at the detailed invoice you’ll see. For each transaction on the detailed invoice (delivery, minimum billing, return, rate adjustment, continuation billing) you will need to write out all of the other details. For a delivery with 20 parts and a partial return of ten items, you’ll have 40 lines (20 minimums, 10 potential splits, 10 continuations). This will get even further complicated if you have multiple tags on the same job that bill by PO or work order. To get it all in one place you’ll need to loop through the InvoiceRentProducts and add all of the other details to surround it.
Loop of Job(s)
Loop through Invoices
Loop through InvoiceRentProducts
Match scaffold details with product
Write to file
First off, thank you for pointing me in the right direction. I am able to obtain the information that I need.
Similar to this topic, I am using the below code:
Dim invoices As InvoiceCollection = InvoiceCollection.GetInvoiceCollection(InvoiceExportStatus.NotExported)
For Each inv As Invoice In invoices
If inv.JobNumber = str_Job_Number And inv.InvoiceUpToDate = dt_Invoice_Date.ToString("MM/dd/yyyy") Then
End If
Next
If I have 500 invoices that have not yet been exported, but only 5 are very a specific Job that I need, I am iterating through all 500 invoices to find them. Is there a better way?
You can use the InvoiceList instead. It’s a much more efficient read-only object and as such might not contain all of the properties. What you’d do is load the InvoiceList with a "List (of Guid)"s that contain your jobsites. Then use the InvoiceListItem, or if the property isn’t available then load the invoice in the loop with Invoice.GetInvoice().
Sounds a bit complicated. I will stick to what I have now; I know it works…
Thank you.
I’m back…
Using the InvoiceCollection is taking entirely too long (hours), so I am forced to look into the InvoiceList option
So, can given the below routine, can you advise how I can achieve the same result, keeping in mind the only parameters the User will be able to provide is the InvoiceUpToDate and the JobNumber?
Thanking you in advance…
Dim invoices As InvoiceCollection = InvoiceCollection.GetInvoiceCollection(InvoiceExportStatus.NotExported)
For Each inv As Invoice In invoices
If inv.JobNumber = str_Job_Number And inv.InvoiceUpToDate = dt_Invoice_Date.ToString("MM/dd/yyyy") Then
For Each prod As InvoiceRentProduct In inv.InvoiceRentProducts
' Only include items that have a total rent > 0
If prod.TotalRent > 0 Then
Dim customer As BusinessPartner = BusinessPartner.GetBusinessPartner(inv.JobSite.BusinessPartnerID)
str_Customer_Name = customer.Name
str_Job_Name = inv.JobName
str_Po_Number = inv.Order.Number
Dim scaf As ScaffoldTag = ScaffoldTag.GetScaffoldTag(prod.ScaffoldTagID)
str_Charge_Number = "TAG " & scaf.Tag
If prod.ShipmentItem IsNot Nothing Then
str_Shipment_Number = prod.ShipmentItem.ShipmentNumber
Else
str_Shipment_Number = ""
End If
str_PartNumber = prod.PartNumber
str_PartDescription = prod.Description
dbl_Quantity = prod.Quantity
dt_FromDate = prod.StartDate
dt_ToDate = prod.EndDate
dbl_Days = prod.Days
dbl_Day_Rate = FormatNumber(Math.Round(prod.RentRate / 28, 4), 4)
If prod.IsMinimum = True Then
str_Rent_Type = "Min. Rent days"
Else
str_Rent_Type = "Add'l. Rent days"
End If
dbl_Ext_Charge = FormatNumber(prod.TotalRent, 2)
End If
Next
End If
NextNote: I have tried myself using the below code, but no invoices? are being returned…
' Still want to pass Job Number instead of Job Name but that won't work...
Dim job As StockingLocation = StockingLocation.GetStockingLocation("ERGON JOB", False)
If job.Number = str_Job_Number Then
Dim locations As List(Of Guid) = New List(Of Guid)
locations.Add(job.StockingLocationID)
Dim invoices As InvoiceList = InvoiceList.GetInvoiceList(locations, InvoiceSyncStatus.All, InvoiceExportStatus.All, job.TradingPartnerID)
For Each invItem As InvoiceListItem In invoices
Next
End IfHi James. Each scaffold has an invoice, so the list in your case needs to be the list of scaffolds. You’re correct again that collections are time consuming. They have extra features and if you only need a readonly list of items, use the List.
' Job and list
Dim job As StockingLocation = StockingLocation.GetStockingLocation("ERGON JOB", False)
Dim jobList As List(Of Guid) = New List(Of Guid)
jobList.Add(job.StockingLocationID)
' Scaffolds and list
Dim scaffolds As ScaffoldTagList = ScaffoldTagList.GetScaffoldTagList(jobList, False)
Dim scaffoldList As List(Of Guid) = New List(Of Guid)()
' Populate scaffolds
For Each scaffold As ScaffoldTagListItem In scaffolds
scaffoldList.Add(scaffold.StockingLocationID)
Next
' Get invoices
Dim invoices As InvoiceList =
InvoiceList.GetInvoiceList(scaffoldList, InvoiceSyncStatus.All, InvoiceExportStatus.All, Guid.Empty)Okay, this seems to work in reverse than what I had before…
So how do I extract the items (products) and shipment information for the invoiced tag using this code?
If you want that level of detail you’ll need to load the invoice
Dim inv as Invoice = Invoice.GetInvoice(...)
Within there, you’ll see numerous collections, InvoiceRentProducts will show you the shipment(s). There’s also a ScaffoldTagPivotList (List) that contains details about the scaffolds.
Thanks for you help, but unfortunately, I am still experiencing extremely dismal data extraction times…
Using my original code, based on using a collection, the invoice data extraction of 1085 rows took 40.26 minutes.
Using this new code, based on using a list, the invoice data extraction of 1085 rows took 33.07 minutes.
ORIGINAL CODE:
Dim invoices As InvoiceCollection = InvoiceCollection.GetInvoiceCollection(InvoiceExportStatus.NotExported)
For Each inv As Invoice In invoices
If inv.JobNumber = str_Job_Number And inv.InvoiceUpToDate = dt_Invoice_Date.ToString("MM/dd/yyyy") Then
For Each prod As InvoiceRentProduct In inv.InvoiceRentProducts
' Only include items that have a total rent > 0
If prod.TotalRent > 0 Then
Dim customer As BusinessPartner = BusinessPartner.GetBusinessPartner(inv.JobSite.BusinessPartnerID)
str_Customer_Name = customer.Name
str_Job_Name = inv.JobName
str_Po_Number = inv.Order.Number
Dim scaf As ScaffoldTag = ScaffoldTag.GetScaffoldTag(prod.ScaffoldTagID)
str_Charge_Number = "TAG " & scaf.Tag
If prod.ShipmentItem IsNot Nothing Then
str_Shipment_Number = prod.ShipmentItem.ShipmentNumber
Else
str_Shipment_Number = ""
End If
str_PartNumber = prod.PartNumber
str_PartDescription = prod.Description
dbl_Quantity = prod.Quantity
dt_FromDate = prod.StartDate
dt_ToDate = prod.EndDate
dbl_Days = prod.Days
dbl_Day_Rate = FormatNumber(Math.Round(prod.RentRate / 28, 4), 4)
If prod.IsMinimum = True Then
str_Rent_Type = "Min. Rent days"
Else
str_Rent_Type = "Add'l. Rent days"
End If
dbl_Ext_Charge = FormatNumber(prod.TotalRent, 2)
End If
Next
End If
Next
NEW CODE:
Dim job As StockingLocation = StockingLocation.GetStockingLocation("ERGON JOB", False)
Dim jobList As List(Of Guid) = New List(Of Guid)
jobList.Add(job.StockingLocationID)
Dim scaffolds As ScaffoldTagList = ScaffoldTagList.GetScaffoldTagList(jobList, False)
Dim scaffoldList As List(Of Guid) = New List(Of Guid)()
For Each scaffold As ScaffoldTagListItem In scaffolds
scaffoldList.Add(scaffold.StockingLocationID)
Next
Dim invoices As InvoiceList = InvoiceList.GetInvoiceList(scaffoldList, InvoiceSyncStatus.All, InvoiceExportStatus.All, Guid.Empty)
For Each invoiceitem As InvoiceListItem In invoices
Dim inv As Invoice = Invoice.GetInvoice(invoiceitem.InvoiceID, False)
If inv.InvoiceUpToDate = dt_Invoice_Date.ToString("MM/dd/yyyy") Then
For Each prod As InvoiceRentProduct In inv.InvoiceRentProducts
' Only include items that have a total rent > 0
If prod.TotalRent > 0 Then
str_Customer_Name = inv.CustomerName
str_Job_Name = inv.JobName
str_Po_Number = inv.Order.Number
Dim scaf As ScaffoldTag = ScaffoldTag.GetScaffoldTag(prod.ScaffoldTagID)
str_Charge_Number = "TAG " & scaf.Tag
If prod.ShipmentItem IsNot Nothing Then
str_Shipment_Number = prod.ShipmentItem.ShipmentNumber
Else
str_Shipment_Number = ""
End If
str_PartNumber = prod.PartNumber
str_PartDescription = prod.Description
dbl_Quantity = prod.Quantity
dt_FromDate = prod.StartDate
dt_ToDate = prod.EndDate
dbl_Days = prod.Days
dbl_Day_Rate = FormatNumber(Math.Round(prod.RentRate / 28, 4), 4)
If prod.IsMinimum = True Then
str_Rent_Type = "Min. Rent days"
Else
str_Rent_Type = "Add'l. Rent days"
End If
dbl_Ext_Charge = FormatNumber(prod.TotalRent, 2)
End If
Next
End If
Next
Is there anyway to squeeze more time savings given the data I am trying to extract?
What is it that you’re trying to accomplish? When you print preview the scaffold tag invoice, is all of the data you need there? That invoice should load in a few seconds.
The invoices templates within Quantify are not what my Company is after, so I have been tasked to export the invoiced data to utilize data to fill custom invoiced templates used by my Company.
The image is a depiction of sample data I required for this.
Your code is loading a scaffold for each item in the invoice when it looks like you only want the tag number? You’re likely loading this tens of thousands of times. Instead of loading the parent (which also loads a lot of other stuff), you can load a ScaffoldTagLisItem. This one should be very fast.
Dim scaf As ScaffoldTagListItem = ScaffoldTagListItem.GetScaffoldTagListItem(id)My code is what I have been able to piece together from Q&A on this forum. If what I have is not the fastest way, I welcome anyone from Avontus to offer suggestions to optimizing the code.
As per this latest suggestion for optimization, I replaced
Dim scaf As ScaffoldTag = ScaffoldTag.GetScaffoldTag(prod.ScaffoldTagID)
with this
Dim scaf As ScaffoldTagListItem = ScaffoldTagListItem.GetScaffoldTagListItem(prod.ScaffoldTagID)
and while the data extraction has been decreased to approximately 13 minutes, it still is still required that I optimize to reduce the extraction time to no more than 2 minutes.
As I have described numerous times, for a supplied Job Name (although I still prefer Job Number) and an Invoice Ending Date, I need the extract all the incremental Tag Shipment Header and Item data for each invoice created; PO (Order) Numbers may have one or many Tags assigned to it.
Still begging for your continued assistance.
Hi James. The data you’re grabbing likely has tens of thousands of items. Since you’re fetching it in a particular format and it seems like flattening it out, this will happen. It’s just a lot of data.
If you investigate Linq you may be able to load lists List(Of …) and index those. This may give you better performance, but it’ll be incremental at this point as this much data does take time.