Odtnhj

I have a table with Document Numbers and items. We have integrated our software to a third party accounting package. The import to the third party does not take our document line numbers, however, it posts into their backend in the same order.

For Example:

My Software:

The import to the Third Party Software (only takes limited fields as they do not manage stock/batch controlled stock):

My goal is to create a new column in the second table to add row numbers for each change in Document numbers. This will allow me to create a unique key that is what I need to link the two databases' tables together.

If I understand the issue correctly, what you want to do is to start with [Row. No.] 1 for every new value in [Doc. No.], and increment the field [Row. No.] by 1 as long as [Doc. No.] is the same as the previous row. One way to accomplish that could be the following:

//load the table, in this example it is an inline table 
//but you would load it from another source
 table2:
 load * inline [
 Doc. No.,Description,Qty,Batch,Value
 Doc 1, Item1, 1, 10
 Doc 1, Item1, 2, 10
 Doc 1, Item1, 3, 10
 Doc 2, Item2, 1, 20
 Doc 3, Item3, 1, 30
 Doc 3, Item3, 1, 30
 Doc 3, Item3, 1, 30
 ];

//define an empty table that into which values can be "concatenateloaded"
 newTable2:
 load * inline [
 dummy
 1
 ];


//define starting values to be used in the loop
 let rownodigit = 0;
 let lastdocno = '';


//Iterate over each row in the table, and use the values to build 
//the new table row for row. If the table is large this would 
//require a significant amount of time...

FOR rowno = 0 TO noOfRows('table2')-1
 let docno = peek('Doc. No.', rowno, 'table2');
 let desc = peek('Description', rowno, 'table2');
 let qty = peek('Qty', rowno, 'table2');
 let batch = peek('Batch', rowno, 'table2');
 let value = peek('Value', rowno, 'table2');

 //determine what value the [Row. No.] field is to be given
 if docno=lastdocno then
 rownodigit = rownodigit + 1;
 else
 rownodigit = 1
 endif

 //build the table by generating a new row into the new table
 concatenate (newTable2) 
 load 
 '$(docno)' as [Doc. No.],
 '$(desc)' as [Description],
 $(qty) as [Qty],
 $(batch) as [Batch],
 $(value) as [Value],
 $(rownodigit) as [Row. No.]
 autogenerate (1)
 ;

 let lastdocno = docno; //store the value of docno into a new variable
 //for comparison in the top of the loop in 
 //the next iteration
NEXT rowno

drop field dummy; //this field was only needed to create the temporary table
drop table table2; //drop the orgiginal table, otherwise we would have 
 //a lot of synthetic keys

//now fix the table and create the [Key] field
 table2:
 load *, [Doc. No.]&[Row. No.]&[Description] as Key
 resident newTable2
 where len([Doc. No.])>0 //this avoids the blank row generated 
 //by the first dummy insert
 ;

//this was a temporary table that we generated and it should now be dropped
drop table newTable2;