There is a section in the SDK with the above title (in chapter 6, 'Documents) that reads as follows: When an interface that adds line items to a document is executed, by default the new items are inserted first in the document (that is, they receive a smaller line number than existing records). For instance, if a given interface adds lines to an existing order that contains two lines, the new record will appear on line 1, the first existing order item will move to line 2 and the second existing order item will move to line 3. Unfortunately this is true. Following this is an explanation of how to insure that new lines will be added at the end of the document, but as usual, this isn't particularly clear (or at least, not clear to me).
As it happens, I have a procedure that I run most days that may add lines to a warehouse transfer document (form DOCUMENTS_T). I won't go into the whys and wheres of this, but I do know that whenever I run this, lines will be added to the beginning of the warehouse transfer and not at the end. For my purposes, it doesn't matter where the lines are added, but I want to use this example in order to deepen my understanding as I have a procedure for an external client that does something similar, except that there I am adding a line to an invoice, and for reasons that aren't relevant, it's very important that the added line be at the end.
So what does one do? In the case of the warehouse transfer document, I added to the interface for form TRANSORDER_T the field TRANS (saved in INT10), where TRANS is the automatic key of the table and is also hidden. At first I thought that the following would suffice
SELECT MAX (LINE) INTO :LINE FROM TRANSORDER WHERE DOC = :DOC; SELECT TRANS INTO :TRANS FROM TRANSORDER WHERE DOC = :DOC AND LINE = :LINE; INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT10) VALUES (2, '2', :TRANS);
Although TRANS is the A key, the form is sorted by LINE, so I thought it best to get the TRANS of the last line then insert this into GENERALLOAD. Lines that the procedure will add will come after this line.
The interface manager complained that line 2 didn't have a part. This led me to realise that I need to enter all of the necessary fields that already exist in TRANSORDER, as well as TRANS into GENERALLOAD before the added lines. Thus my procedure now contains the lines
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT1) VALUES (1, '1', :DOC); INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT10, TEXT1, INT1, TEXT13, TEXT17, TEXT18) SELECT 1 + SQL.LINE, '2', TRANSORDER.TRANS, PART.PARTNAME, TRANSORDER.QUANT, CUSTOMERS.CUSTNAME, CUSTOMERS.CUSTNAME, WAREHOUSES.LOCNAME FROM TRANSORDER, PART, CUSTOMERS, WAREHOUSES WHERE TRANSORDER.PART = PART.PART AND TRANSORDER.CUST = CUSTOMERS.CUST AND TRANSORDER.WARHS = WAREHOUSES.WARHS AND TRANSORDER.DOC = :DOC; SELECT MAX (LINE) INTO :LINE FROM GENERALLOAD;
I tested this by deleting the last few lines of the existing warehouse transfer, then running the procedure. If the above code works correctly, then the lines that I deleted should be restored to the document and they should be the final lines. Indeed they were added at the end.
What about invoiceitems, especially considering that this table doesn't have an automatic key but rather a composite key of IV and KLINE? The following works nicely to copy the lines
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT1) VALUES (1, '1', :IV); INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT10, TEXT1, INT1, REAL1) SELECT 1 + SQL.LINE, '2', INVOICEITEMS.KLINE, PART.PARTNAME, INVOICEITEMS.TQUANT, INVOICEITEMS.PRICE FROM INVOICEITEMS, PART WHERE INVOICEITEMS.PART = PART.PART AND INVOICEITEMS.IV = :IV; SELECT MAX (LINE) INTO :LINE FROM GENERALLOAD; .... /* then new lines get added without INT10 */ :LINE = :LINE + 1; INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT1, INT1, REAL1) VALUES (:LINE, '2', '11055332YA307S', 6000, 250.0);
This new line does get added at the end of the invoice.
