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

    Record ID Calculation



      Record ID Calculation


      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


              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.