1 Reply Latest reply on Feb 25, 2010 9:27 AM by philmodjunk

    Base 36 (Alphanumeric) Serial Numbers variation?



      Base 36 (Alphanumeric) Serial Numbers variation?




      I have an Orders table (basically the quote / invoice portion of the database) and a Work Orders table. The work orders table is used for the manufacturing plant to produce products. The work order table does have a unique auto enter serial number that the user does not see. What they see is a field that carries across the Order number. On occasion we require releasing multiple work orders against a single order. We would like to take the strictly numeric order ID and increment it with A, B, C...for the multiple work orders as needed. For example order ID 47000, requires multiple work orders, which should look like 47000A, 47000B, 47000C. How would I set this up to be auto generated serial number?


      Also, on occasion we need to redo an order due to an error on our part. In this case we would like take the order ID and suffix it with "SOS" in the work order table. How would we do this so that it still relates back to the order ID table? 

        • 1. Re: Base 36 (Alphanumeric) Serial Numbers variation?

          First of all, don't allow modification of the "unique auto enter serial number that the user does not see"  use this field for all relationships. This preserves the links to all related records in other tables.


          Make a second field that is initially assigned the value from the hidden serial number field and write scripts that can modify this field by appending additional text to the end of it. Put this field on all layouts where users need to see this information.


          Then it's just a matter of creating a script that modifies this second field consistently. The exact steps here can vary depending on where you are when you need to trigger this script.


          It would seem that you've pulled up an order in the Orders table and need to issue an additional work order.


          A button for "new work order" might look like this:


          If [ Not IsEmpty ( WorkOrders:: OrderID ) /* at least one work order exists for this order */]

             IF [ Count ( WorkOrders:: OrderID ) = 1 /* there is only one work order */]

                 Set Variable [$Suffix ; Value: "A" ]


                 #There are at least to WO's compute the next letter in the alpha series

                 Set Variable [$Suffix ; Value:  Char ( Code ( Right ( Last ( WorkOrders:: OrderID ) ; 1 ) ) + 1 ) ]

             End If

          End If

          Set Variable [ $OrderID ; Orders:: OrderID ]

          Go To Layout [Work Orders (Work Orders ) ]

          New record/Request

          Set Field [WorkOrders:: OrderID ; $OrderID ]

          Set Field [WorkOrders:: OrderIDLabel ; $OrderID & $Suffix ]

          Go To Layout [original layout]