This should be common but I can't figure out how to do this correctly:
I have an Complete Order Set - Key Order Number (is linking key between both tables)
With an Order table that has multiple Order Numbers to multiple order lines
With an Order Lines table (one to many)
I want the line id to increment based in relation to the qty of lines in the order.
For example:
Order No: 25569
Will have 5 lines to it
I want the first line to have the line id: 001
2nd line id: 002
3rd line id: 003
4th line id: 004
5th line id: 005
If I then go onto Order No: 359846
The 1st line id should be 001
2nd line id 002, etc.
and if I go back to order No: 25569 to add additional lines it will look to see what the last line number on that order was and go to the next line no: 006 and then if there is another line no 007, etc.
Where and how should I go about getting this to happen.
I was doing it in the table rules for line id: but I don't know if I should do Field Type as Calculated and then what calculation should I use? I tried last() but it says I can't use that function.
I've tried in Data Entry and default mode using this : PREVIOUS("line","OrderNo")+001
Bbut when I go to the next order it continues the Line id sequence it does not rely on the how many lines are per order.
It ends up looking like this:
Order No: 25569
1st line id: 001
2nd line id: 002
3rd line id: 003
4th line id: 004
5th line id: 005
If I then go onto Order No: 359846
1st line id: 006
2nd line id: 007
and if I go back to order No: 25569 to add additional lines it will look to see what the last line number on that order was and go to the next line no: 008 and then if there is another line no 009, etc.
I want it to look like this:
Order No: 25569
1st line id: 001
2nd line id: 002
3rd line id: 003
4th line id: 004
5th line id: 005
6th line id: 006
7th line id: 007
If I then go onto Order No: 359846
1st line id: 001
2nd line id: 002
Any help would be appreciated.
Jennifer Payne
I have an Complete Order Set - Key Order Number (is linking key between both tables)
With an Order table that has multiple Order Numbers to multiple order lines
With an Order Lines table (one to many)
I want the line id to increment based in relation to the qty of lines in the order.
For example:
Order No: 25569
Will have 5 lines to it
I want the first line to have the line id: 001
2nd line id: 002
3rd line id: 003
4th line id: 004
5th line id: 005
If I then go onto Order No: 359846
The 1st line id should be 001
2nd line id 002, etc.
and if I go back to order No: 25569 to add additional lines it will look to see what the last line number on that order was and go to the next line no: 006 and then if there is another line no 007, etc.
Where and how should I go about getting this to happen.
I was doing it in the table rules for line id: but I don't know if I should do Field Type as Calculated and then what calculation should I use? I tried last() but it says I can't use that function.
I've tried in Data Entry and default mode using this : PREVIOUS("line","OrderNo")+001
Bbut when I go to the next order it continues the Line id sequence it does not rely on the how many lines are per order.
It ends up looking like this:
Order No: 25569
1st line id: 001
2nd line id: 002
3rd line id: 003
4th line id: 004
5th line id: 005
If I then go onto Order No: 359846
1st line id: 006
2nd line id: 007
and if I go back to order No: 25569 to add additional lines it will look to see what the last line number on that order was and go to the next line no: 008 and then if there is another line no 009, etc.
I want it to look like this:
Order No: 25569
1st line id: 001
2nd line id: 002
3rd line id: 003
4th line id: 004
5th line id: 005
6th line id: 006
7th line id: 007
If I then go onto Order No: 359846
1st line id: 001
2nd line id: 002
Any help would be appreciated.
Jennifer Payne
Comment