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

    Record ID Calculation

    Sewlong

      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
          Orlando
            

          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
            Sewlong
               Thank you! I think this will solve my problem!
            • 3. Re: Record ID Calculation
              Sewlong
                

              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
                Orlando
                  

                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.