Archives

Having “Fun” With Job Order Numbers

I recently received a call from a company that had “run out” of internal job order numbers.

Warning

As an error message, this one is fairly descriptive. However, 9 times out 10 the person who receives it won’t have permissions to modify the numbering system anyway. Some research on M2MExpert determined the cause. Made2Manage only allows 4 characters for the internal job order numbers (5 if you include the mandatory preceding I). If this was a numeric field, then there would only be 9,999 possible job numbers. The work around was to utilize Base 36 numbering in this field. Rather than just 10 options for each digit (0-9), they added another 26 since each digit would jump from 9 to A through Z for a total of 36 different options per digit. Theoretically, this raises the number of combinations to almost 1.7 million. I know M2M’s document indicates 34 options because they say that I and T are skipped, but this isn’t true with Internal Job Order Numbers, which is the subject of this article.

This company was set to Alpha Column 4 and the number indicates which starting digit from right to left can be base 36. For example, the last number this company could allocate was IZ999 since the second digit is the only one which allowed letters. Incidentally, IZ999 is literally the number 36,000. Z represents 35 and since it is in the thousands place, the number is 35,000 + 1000 (999 plus the starting 0) is 36,000.

You might ask, why would someone set the Alpha Number to 4 in the first place? Well, M2M uses the same setting in it’s own educational company, so how would anyone know that they should have their company set differently? So, what is the answer to this problem? There’s only one practical solution which is to lower the Alpha Number setting. To do so choose the Utilities Module, Company Setup, Codes & Tables, System, and Document Numbering (Company Specific) and you’ll be greeted with this comforting message box:

Warning

When you see something like this, stop and call support.

Incidentally, you can also type ALT-T and go to the SYSEQU screen. Click the browse button and navigate down to JOMAST.FJOBNOI. The greatest number of job order combinations is available when you select Alpha Number 1. This makes sense because all of the digits switch to base 36.

So, why did I explain all of that? Well, I decided that this would be a fun SQL exercise. Wouldn’t it be interesting if you determine how long you had before you ran out of job numbers so this wouldn’t sneak up on you? No? Well, humor me anyway, I think it’s fun.

To simplify this whole thing, we are going to use a User Defined Function (UDF). Normally, I would not recommend adding one to your M2M Database because it could possibly interfere with migration. Fear not dear readers, we will delete it when we are finished. I think now would be a good time for the disclaimer.

Important: All information on this site is provided “as is” for informational purposes only.
In particular, all code samples are provided “as is” without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

If you should decide to use any of the code from this site, make sure you test it thoroughly on a test company (or better yet a test server) before you ever use it on your live data.

First, use the following code to create the User Defined Function.

create function dbo.fn_base36(@mystr varchar(8000))
returns bigint
as
BEGIN
set @mystr = reverse(@mystr)
declare @len int, @onechar char(1), @pos int, @numvalue bigint
declare @arrystr varchar(100)
set @arrystr = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
select @len=len(@mystr)
set @onechar = ''
set @pos = 1
set @numvalue = 0
while @pos<=@len
	begin
		select @onechar = substring(@mystr,@pos,1)
		select @numvalue = (charindex(@onechar,@arrystr)-1)*power(len(@arrystr),@pos-1)+@numvalue
		set @pos = @pos + 1
	end

return @numvalue
END

The following code actually checks your tables to estimate when you will run out of internal job numbers. For the sake of simplicity, we are only checking these, rather than all 3 types. After the code, I'll briefly discuss the code. If you'd like further explanation, feel free to ask questions in the comments. Remember that you will have to replace the company number with the company number you wish to test this on. I ran mine on a practice company which was numbered 12.

DECLARE @last_job_no varchar(8000)
DECLARE @alpha varchar(1)
DECLARE @jonos_left AS int
DECLARE @base36 AS varchar(5)
DECLARE @numeric_left AS int
DECLARE @jonos_past_year AS int

SET @last_job_no = rtrim(ltrim((SELECT S.fcnumber
                FROM m2mdata12.dbo.sysequ S
               WHERE fcprompt = 'Next Internal J.O. Number')))

SET @alpha = (SELECT S.fnbase36di
                FROM m2mdata12.dbo.sysequ S
               WHERE fcprompt = 'Next Internal J.O. Number')

SET @base36 = (SELECT LEFT(@last_job_no, 4 - (@alpha - 1)))

SET @Numeric_Left = (SELECT RIGHT(@last_job_no, @alpha-1))

IF @alpha = 1
BEGIN
SET @jonos_left = (SELECT (dbo.fn_base36('ZZZZ')-dbo.fn_base36(@base36)))
END

ELSE IF @alpha = 2
BEGIN
SET @jonos_left = (SELECT ((dbo.fn_base36('ZZZ') * 10 + 9) -dbo.fn_base36(@base36) * 10 + @Numeric_Left))

 END

ELSE IF @alpha = 3
BEGIN
SET @jonos_left = (SELECT ((dbo.fn_base36('ZZ') * 100 + 9) -dbo.fn_base36(@base36) * 100 + @Numeric_Left))

END

ELSE IF @alpha = 4
BEGIN
SET @jonos_left = (SELECT ((dbo.fn_base36('Z') * 1000 + 9) - dbo.fn_base36(@base36) * 1000 + @Numeric_Left))

END

ELSE
BEGIN
SET @jonos_left = NULL
END

SET @jonos_past_year = (SELECT count(J.fjobno) FROM m2mdata12.dbo.jomast J WHERE j.ftype = 'I' AND j.fopen_dt >= DATEADD
      (YEAR, -1, getdate()))
IF @jonos_past_year = 0
BEGIN
SET @jonos_past_year = 1
end

SELECT @jonos_left AS JobNumbersLeft,
       @alpha AS AlphaNumber,
       @last_job_no AS LastNumberIssued,
       @base36 AS base36,
       @numeric_left AS NonBase,
       @jonos_past_year AS JobOrdersPastYear,
       (@jonos_left/@jonos_past_year) AS NumberYearsLeft

Let me preface this by saying that I could have achieved this with less code, but I leaned toward verbosity to make it easier to follow. The first 6 lines of the code declare the variables used. The next 4 groups, set the values for 4 of the variables. The last job number assigned (@last_job_no) and your alpha number (@alpha) are queried from the database with the two select statements. The base 36 portion (@base36) and the numeric portion remaining (@Numeric_Left) are separated from your @last_job_no based on the alpha setting in your database. In the above example of IZ999 the @base36 would be Z (I is ignored because it never changes) and the @Numeric_Left would be 999.

The next 5 sections are if statements and calculate the job order numbers left (@jonos_left) based on your alpha setting. Basically, the value of your last job order number is subtracted from the maximum value for that alpha setting.

Next I set the count of your job orders from the previous year (@jonos_past_year) with the select statement. If you don't have any job orders from the previous year, I set the value to 1, so we don't divide by zero.

The final select statement displays everything for you and divides the number of job order numbers left (@jonos_left) by the job order numbers from the previous year. This estimates the number of years you have left before you run out of numbers. Cool, huh?

Finally, use the following code to remove the UDF from your database.

IF EXISTS (SELECT *
           FROM   dbo.sysobjects
           WHERE  id = Object_id(N'[dbo].[fn_base36]')
                  AND xtype IN (N'FN',N'IF',N'TF'))
  DROP function [dbo].[fn_base36];

So, how many years do you have left before you run out of internal job order numbers?

Related posts:

4 comments to Having “Fun” With Job Order Numbers

  • Scott

    Something that you may want to consider is the ever changing volume of job orders (or any other record). Hopefully your company is growing, and therefore used more numbers last year then the year before and will use even more this year.

    Depending on the rate of growth (or shrinkage), your time remaining may be drastically effected. If you really wanted to get fancy it would be nice to have a single report (maybe Crystal?) that would show the estimated time remaining for all records. You could also display usage over previous time periods and show estimated end of available numbers based upon previous or expected percent growth. By using Crystal and supplying executable SQL code to make everything work, a complete novice could get their statistics. While my company does not use job numbers, it would be interesting to find out when we will run out of other records like sales orders and purchase orders.

  • I agree that the volume of job orders may change over time, but I wanted to present this information as simply as I could.

    I thought about using Crystal Reports. I would have been happy to do so, but according to my previous poll, many folks aren’t fortunate enough to own it. In the future, I may supply Excel Spreadsheets for download since nearly everyone has it.

    As for querying the other types of job order numbers, it would require a modified User Defined Function since they are not truly Base 36. Those do omit I & T so the calculations are different and more complicated. As I said, I was attempting to make this article as simple as possible for Admins who are relatively new to T-SQL.

  • Andrew

    I am confused. Apparently I’m on alpha 3 and I’d like to understand this. How did you come up with this?

    ELSE IF @alpha = 3
    BEGIN
    SET @jonos_left = (SELECT ((dbo.fn_base36(‘ZZ’) * 100 + 9) -dbo.fn_base36(@base36) * 100 + @Numeric_Left))

    END

  • Let me give you an example. Let’s say you were Alpha 3, and you had the number IZA22. The first thing you do is separate the alphanumerics (base 36) from the numbers (base 10). So, you end up with “ZA” and “22”, remember the I is ignored.

    When you feed ZA into the User Defined Function, you get: 1270. However, the ZA is in the hundreds place. Therefore 1270 has to be multiplied by 100. Afterward, you add the numerics.

    (1270*100) + 22 = 127022

    I hope that helps. Please don’t hesitate to ask if you don’t understand something.

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>