Incrementing, from 1, based on another column

muffinbubble

Hi, I have a table which simplified is a sales orders lines table, with two columns, a reference to the sales order (this is a FK) and an order line. The order line column should increment from 1 to x but be unique to that sales order ID.

For example:

SalesOrderID    OrderLine
1                          1
1                          2
1                          3
2                          1
2                          2
3                          1

And so on. Currently the order line is totally random and doesn't start from 1. I can set a min/max but it needs to start from the SalesOrderID.

Is this possible? So in effect, the logic for each generated line will ask "Is this a new sales order ID, therefore the OrderLine will generate 1. Further down I randomly generate the same sales order ID, but as a row already exists once with an OrderLine of 1, increment 1 to it, so the OrderLine becomes 2. If a new sales order ID is generated which doesn't exist, we start from 1 again.".

Edit: I did try a generic Python script but it seemed to only allow you to run tests on that column and not get values of others.

Eddie D

Hi, thank you for your forum.

From the information you have provided, I cannot identify a method to achieve your goal using the generators provided within the tool.

One possible solution, would be to create a csv file or SQL Table that can be used as a reference for the example data you wish to generate.

Many Thanks
Eddie

muffinbubble

Hi Eddie, I managed to work around it by running a SQL script after generation (grouped on sales order ID and then ROW_NUMBER for the order line.