Are you running a script to create your individual Items? If so you will already have a field with the Order ID and you could setup a second number field that you set upon creation, and this will have the individual record number for that grouping of items. And then have a calculation that put the two numbers together to produce your final outcome.
To get the next number in yoru script you want to set a variable and use the Max function to determin the highest number in your Items table, from the Orders tabel, and then add 1 to that figure. So your Item creation script would look something like this:
Set Variable [ $OrderID ; ORDERS::UniqueID ]
Set Variable [ $NextNo ; Max ( Orders_ITEMS::Number ) + 1 ]
Go to Layout [ Items Layout ]
New Record / Request
Set Field [ ITEMS::OrderID ; $OrderID ]
Set Field [ ITEMS::Number ; $NextNo ]
And then your calculated fields would simply be:
OrderID & "-" & Number
I hope this helps, if anything needs further explanation then just ask.
Thank you! I think this will solve my problem!
I finally had a chance to work on this project, do you know of how I could do this by calculation?
For example on the Order layout I have a portal that shows the items, so I would like it (if possible) to calculate the number?
I guess ultimately I can figure it out if I know how to get the number of items that are already related to the order?
Thanks again for your response!
What you want in this case is a self join between two instances of your ITEMS table, linked by the OrderID.
ITEMS - sj_ITEMS
OrderID -- = -- OrderID
I would strongly recommend keeping with the idea of a number field to store the sequence number for those related record, but if you are not setting it via a script you can use a Auto-Enter Calculated value, in define database select your field and click on the 'Options...' button and select 'Calculated value' from under the Auto-Enter tab, with the following calculation:
Max ( sj_ITEMS::Number ) + 1
Then you make the concatenated OrderID a calculated value, as mentioned in my previous post.
This should do the trick.
The reason I recommend having a static number field and not making it a calculation is the calculation will continue to reevaluate itself when a new related record is created, and you will end up with some strange number along with your final ID changing.
Let me know how you get on with this.