Create a counter based on the another entity

Create a counter based on the another entity

  

Hi,

I have 2 entities: 

Sales order

Id

Description


Sales order lines

Id

SalesOrderId 

Description 

Line


I want to increment "Line" based on the SalesOrderId for each time I add a new Sales Order Line record. My idea was to assign the value of the last line to a local variable, but I cannot get the last line id. Can you help me on this?   

Adela Georgiana Simion wrote:

Hi,

I have 2 entities: 

Sales order

Id

Description


Sales order lines

Id

SalesOrderId 

Description 

Line


I want to increment "Line" based on the SalesOrderId for each time I add a new Sales Order Line record. My idea was to assign the value of the last line to a local variable, but I cannot get the last line id. Can you help me on this?   


Hello Adela,

If you increment the Line column as you add new records, why not change to an auto-number like ID's usually are?

Best regards,

Rafael Valente

Rafael Valente wrote:

Adela Georgiana Simion wrote:

Hi,

I have 2 entities: 

Sales order

Id

Description


Sales order lines

Id

SalesOrderId 

Description 

Line


I want to increment "Line" based on the SalesOrderId for each time I add a new Sales Order Line record. My idea was to assign the value of the last line to a local variable, but I cannot get the last line id. Can you help me on this?   


Hello Adela,

If you increment the Line column as you add new records, why not change to an auto-number like ID's usually are?

Best regards,

Rafael Valente


Hi Rafael! 

It won't work with Autonumber. For example, I have 2 orders ( Order ID 1 and 2), each order can have many lines in OrderLines entity, lines should start from 1 for each Order.

Order 1

Order1 Line1

Order1 Line2

Order1 Line n


Order 2

Order 2 Line1

Order 2 Line2

Order 2 Line n


Solution

Oh, i understand now.

So, you create record in Sales Order Line table and when you create these, you already have the Sales Order Id, right? So, try to query your Sales Order Line table with something like:

"SELECT {SalesOrderLine}.* 

FROM {SalesOrderLine}

WHERE {SalesOrderLine}.[SalesOrderId] = @SalesOrderId"

So if you get 0 records it means, you are creating the first record for this Sales Order, so you know the Line will be 1. If you get more than 0 records, you can just take the Line value of the last record, add one and create the new record.

Solution

Rafael Valente wrote:

Oh, i understand now.

So, you create record in Sales Order Line table and when you create these, you already have the Sales Order Id, right? So, try to query your Sales Order Line table with something like:

"SELECT {SalesOrderLine}.* 

FROM {SalesOrderLine}

WHERE {SalesOrderLine}.[SalesOrderId] = @SalesOrderId"

So if you get 0 records it means, you are creating the first record for this Sales Order, so you know the Line will be 1. If you get more than 0 records, you can just take the Line value of the last record, add one and create the new record.


It works amazing! Thank you a lot, Rafael! 

Adela Georgiana Simion wrote:

Rafael Valente wrote:

Oh, i understand now.

So, you create record in Sales Order Line table and when you create these, you already have the Sales Order Id, right? So, try to query your Sales Order Line table with something like:

"SELECT {SalesOrderLine}.* 

FROM {SalesOrderLine}

WHERE {SalesOrderLine}.[SalesOrderId] = @SalesOrderId"

So if you get 0 records it means, you are creating the first record for this Sales Order, so you know the Line will be 1. If you get more than 0 records, you can just take the Line value of the last record, add one and create the new record.


It works amazing! Thank you a lot, Rafael! 

You're welcome! =)

Happy i could help.