Kimberly, are you able to post some sample data from your source file?
Do you have the sales order's line key in the source file?
If not, how are you tying the item on the invoice to an item on the sales order because if I recall correctly, importing against a sales order into SO_InvoiceHeader does not actually link a given imported line to the original line on the sales order. You can review the SO_InvoiceDetail's OrderLineKey field to confirm this.
One approach is to use some "perform logic" which is really just ProvideX statements to leverage the BOI to either copy the sales order's lines to the invoice right after specifying the sales order number or use the BOI to attempt to retrieve the order line key by trying to match the item code to a line in SO_SalesOrderDetail but this isn't perfect if they repeat an item code more than once. If you copy the lines, you will still need logic to make sure the now "existing" lines are written to instead of adding more when importing the lines from your source file, which if you don't have the SO_InvoiceDetail's LineKey, then you will end up adding lines unless you use perform logic to attempt to locate the correct line key based on the item code, which of course is subject to the same problem as before if items are repeated.
I would recommend to start by trying to get the sales order's LineKey in your source file as this will make it easier to work with.
Be aware that the invoice detail's LineKey is not equivalent to the sales order detail's LineKey. It is possible they will match in certain scenarios but not always.
As others have said, it is a lot easier if you can normalize the source file though.
Also, it is possible to leverage the BOI to lookup an existing invoice number when a subsequent header line is read with the sales order numbers out of order but it requires quite a bit of effort.
Things changed a bit when Sage introduced the feature to allow invoicing multiple sales orders but I think Kevin is referring to the CopyLinesFromSalesOrder method but it has been replaced with three other methods to deal with multiple orders. The following Sage City post has a lot of details on equivalent methods for POs.
Receipt of Invoice adding lines using BOI with multiple POs - Sage 100 Business Object Interface - Sage 100 - Sage City Community------------------------------
David Speck II
Tennessee Software Solutions
------------------------------
Original Message:
Sent: 12-16-2021 15:11
From: Kevin Moyes
Subject: SO_InvoiceHeader VI Import Help
Having the lines in your invoice source data with quantity shipped = 0 should be enough... that is how we usually deal with this issue, but I seem to recall seeing @David Speck II post a technique to set a flag during the import to do this automatically (which I have not taken the time to test, so I can't comment on details).
------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------
Original Message:
Sent: 12-16-2021 14:25
From: Kimberly Davis
Subject: SO_InvoiceHeader VI Import Help
Thank you for the response Kevin! That is the conclusion I came to with my testing. Would you happen to know how to get the back order quantity to calculate on lines for the sales order that are not in the import. We are basically importing a partial invoice, I can calculate and assign the proper back order quantity, or the system does it as well, when it the invoice updates for lines that I am partially invoicing. However, it just zeros out the back order quantity on the lines that I am not invoicing with my import and closes the line on the sales order. Is the only answer to pull all the lines in from the sales order into an import file and assign the back order quantity even if I am not invoicing them?
------------------------------
Kimberly Davis
Lehman Wesley & Associates
Original Message:
Sent: 09-07-2021 11:07
From: Kevin Moyes
Subject: SO_InvoiceHeader VI Import Help
For Next to work, the source data needs to be sorted. When any H field changes, it triggers Next, with no checking for previous invoices against that SO. To deal with this, a two step import can work (into UDT, then from ODBC source based on the UDT data), or sort the file (script) before the import.
------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
Original Message:
Sent: 09-07-2021 09:59
From: Jeff Schwenk
Subject: SO_InvoiceHeader VI Import Help
No clue on how to write a VI script to do this. If there is no solution for this, you may have to sort the files based on SO number, then use the next function based on the SO number field. When the SO number changes, a new invoice number will be assigned.
------------------------------
Jeff Schwenk
FORMER 90M Board Member -140,000,000 is a TERRIBLE thing to waste!!!
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
Original Message:
Sent: 09-07-2021 09:38
From: Kimberly Davis
Subject: SO_InvoiceHeader VI Import Help
It is right now, but ultimately, it may not be since the source file is just a concatenation of several files.
------------------------------
Kimberly Davis
Lehman Wesley & Associates
Original Message:
Sent: 09-04-2021 00:12
From: Jeff Schwenk
Subject: SO_InvoiceHeader VI Import Help
Is the header file sorted by SO Number?
------------------------------
Jeff Schwenk
FORMER 90M Board Member -140,000,000 is a TERRIBLE thing to waste!!!
Bottomline Software, Inc.
Waynesboro VA
540-221-4444