4 Replies Latest reply on Apr 8, 2009 6:56 AM by Orlando

# Record ID Calculation

### Title

Record ID Calculation

### Post

I have one table called Orders and another with Items.  I would like the Item ID  to be a calculation from the Order ID

i.e. If the Order ID is 563 then I would like the first item ID to be 563-1 and the second 563-2 etc…. can anyone help with this calculation? Thanks

• ###### 1. Re: Record ID Calculation

Hi Sewlong

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.

• ###### 2. Re: Record ID Calculation
Thank you! I think this will solve my problem!
• ###### 3. Re: Record ID Calculation

Orlando

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!

• ###### 4. Re: Record ID Calculation

Hi Sewlong

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.