Please Santa, Can I Have an Error Log in a SQL Table for Christmas.

Christmas Story

A Christmas Story

At the time of this writing, the M2M error log is still being kept in a text file, not surprisingly called error.log. I understand that this is probably the most expedient way for M2M to capture it. I suspect it’s a lot easier code-wise to simply append to a text file rather than create a database structure and use SQL to insert error records into it.

Additionally, if your SQL Server goes offline then SQL error logging would of course do the same. However, I also suspect that if your SQL Server goes down, the error.log file won’t have much in the way of relevant data anyway.

Why do I care which format this file is in? For the most part, I want this change for the reporting possibilities. I can theoretically write reports on the text file using Crystal, but if the format was an SQL table, the reporting possibilities are limitless. Off the top of my head, the following are some of the reports I’d like to create.

  1. Wouldn’t you like to know how many errors you are logging each week and to be notified if they sharply increased?
  2. Wouldn’t you like to be immediately notified of certain errors? We all know that M2M throws many superfluous errors, but I would like to be notified via e-mail of those which are critical.
  3. I would like to see which users are generating the most errors and of what kind, so I can suggest better ways for them to use M2M or to suggest they pursue online training.
  4. I would like to use T-SQL to remove the cascading errors from the log before looking at it. Cascading errors are those errors which are simply a result of the first error and they can most often be ignored. In my opinion, they needlessly clutter the log.

These are just a few of the things I’d like to do with this information. There is a change request for this issue, number 11346, which covers this issue but it has been in “Awaiting Approval” status for over 4 years.

I urge my readers, both of you :), to add your companies to this change request to motivate M2M to implement this change.

It's Beginning to Look a lot Like Christmas

Christmas Lights

I’ve always loved Christmas Lights, but some people go overboard.

M2M can’t be decorated like that, but you can add a touch of holiday spirit. Follow the directions below and you can have your active cell indicator switch daily from green to red, the colors of Christmas.



Want to try it? Simply follow these steps:

  1. Download this Zip file and unzip it. Essentially the colors.prg will change the color of the active control on M2M screens for every user. It flips them back and forth between red and green if run in the month of December. If run in any other month, it changes the color back to the default yellow.
  2. You should try any code first on your test server. Now is 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.

  3. Copy the Visual FoxPro program files colors.prg and colors.FXP to your report program file directory. For example, mine is L:\M2MData\Reports\Prgs.
  4. Next we have to create a “blank” report in Made2Manage. Open the RPMAKE screen, the simplest way to do so is to click Transfer and quickly type RPMAKE and then enter.
  5. Click Add, enter a report ID such as “Colors” and a description such as “Holiday Colors.”
  6. Add a “Default Data Source” by clicking Browse and navigating to colors.prg and then save the report.
  7. Color Screen

  8. Click on the Selections tab and click Add. Enter anything at all for selection name such as “Trash”. M2M reports will not run without a selection, but it doesn’t need to be valid.
  9. Color Screen

  10. As a test, open the report you created by clicking Transfer and then COLORS and run the report. Since the report isn’t printable, you will get the following screen.
  11. Color Screen

  12. Click OK to close, exit Made2Manage, and log back in to re-set your settings.
  13. Open any form you normally would such as the Sales Order (SO) screen and click modify. The background of the control will now be light red. If you run the report and re-start M2M again, your highlighted control will be green.
  14. How can you make the colors automatically switch every day for the month of December? Follow the instructions found in my Command Scheduler article to run this report each night.
  15. When adding to Command Scheduler do not forget to set the frequency to “Daily.”
  16. Color Screen

Besides being fun and festive for the holiday season, this type of customization can have a practical use as well. For example, one of the companies I work with doesn’t use calendar months but instead uses a system they call Red Fridays. The Red Fridays are determined before the year begins and each month ends with one. These can be found on the Accounting Years screen (AYRS). Anyway, I’ll adapt the code to change the background color to red on the last day of the period by querying the GLRULE table, and default it to yellow for the rest of the month.

Incidentally, if you decide you want to change your users default color back to yellow, simply substitute the yellow.prg and yellow.fxp files (after copying them to your report prgs folder) in your Holiday Colors report and run it. This will return everyone back to normal.

A big thank you goes to Ray at NightScaping for his help with the VFP code.

So, what do you folks think?

Well, We’re Movin’ on Up. M2M Migration

The Jeffersons

Movin’ on Up.

In a previous post, I indicated that I would post my migration procedures. Made2Manage offers instructions to set up a test server and basic migration directions, but they don’t offer recommendations of what exactly should be tested during migrations. This is unfortunate, because only they know where the issues are likely to be.

Should I pay for migration assistance?

I’ve performed migrations both ways. The only time I contracted with M2M for migration was my jump from Version 3.6 to 5.51. Migrating from Visual FoxPro to SQL is not something to be taken lightly. If you are in that situation, I would recommend getting assistance with it.

If you are migrating from any 5.XX version, it all comes down to the extent your M2M is customized, and budgetary concerns. If you administer a completely standard Made2Manage instance, then theoretically you could skip the testing process I am about to recommend. In that case having an experienced Made2Manage or third party consultant on-site during migration is beneficial. If something goes wrong during migration, then you have an expert on site with immediate access to all of Made2Manage’s resources. At the Vegas conference, Made2Manage distributed a document advertising an Upgrade and Upsize Service to version 6.0. To give you an idea of cost, they advertised a cost of $6200 for those already on any 5.X version and $11,700 for those on a prior version. These prices do not include weekends, holidays, or travel expenses. Also, the services don’t cover customization migration or the migration of customized reports.

If you have customizations, then the migration consultants will be of considerably less help if something goes awry. In my experience, most of them are not trained to customize Made2Manage. If you have customizations, ideally you should have the programmer participate in the migration process or someone who can substitute for them.

Therefore, if you have customizations and a reasonably competent administrator, you should do the migration and testing yourself. Migration is a great opportunity to learn more about M2M and how your business uses it. The process I use encourages the administrator to communicate with his users about any problems they have with M2M.

Migration Procedure

  1. Go to M2MExpert and get a copy of the CUSTRP Report This report will determine which reports have been customized and in what way, as well as any new custom reports you may have. After you run this report, use Visual FoxPro to check your UTRPSESS table as the sessionid field records the last time each M2M report was run. This includes any custom reports. Use this information to discuss with your employees which customized items they no longer need. I’m a big proponent of deleting things you don’t use anymore, but make sure you keep a permanent back up because users often change their minds. After this process you know which custom report items you need to test after migration.
  2. Assess your customized code. Are you using FastForms, Interactive Form Editor (IFE), VBA, or 3rd party reporting programs like Crystal Reports? If so, are you sure what that customized code actually does? Often documentation is an afterthought and if the customizations are old enough, the customized functions are mistaken for normal M2M function. Take time to investigate your customizations so there are no nasty surprises during testing. Also, you must thoroughly test your custom functionality as it is much more likely to break than standard M2M code.
  3. Set up a test server with an exact copy of your live data and all of associated folders. Keep the database back up files so you have a constant restore point as you will be migrating the data more than once.
  4. Before you migrate, run and print the following reports with the default settings: Sales Order Backlog (RPBKLG), Inventory Evaluation (RPIVAL), Work in Process (RPWIP), General Ledger Trial Balance (RPGLTB), AR Aging/Status (RPARAG), and the AP Aging/Status (RPAPAG). If any of the reports generates an excessive number of pages, print the last 5 pages of the report.
  5. Perform the migration according to the instructions. Assuming you were successful, apply any applicable service packs. You may also need to upgrade any of your optional modules as well.
  6. Make a SQL back up of your migrated data, taking care not to over-write your pre-migrated back up. You may need to restore to that point when doing user testing.
  7. Re-run the reports listed above and make sure that they match the pre-migrated data. If so, you have proven data integrity and you can move on to the next step. If not, you’ll need to investigate the cause of the differences.
  8. Open M2M and do some preliminary checking for correct functionality. Create a sales order, shipper, and invoice to check functionality. Make sure to check areas in which you have customizations.
  9. If you have customizations which actually write to the database, you need two test machines or you can use a test company (with identical data) on your live server for comparison. If your custom code is writing to your database, you not only have to do the first data integrity test I mentioned above, but you also have to perform the exact same actions on both instances, and then integrity test them again. You add, modify, and delete the same records to both to ensure that the end results are the same. Only through this extensive testing can you be sure that your customizations will successfully migrate.
  10. Now you’re ready for user testing. Recruit a person from each department with instructions to perform at least one example of every procedure they perform in Made2Manage. Typically, I create a spreadsheet with a separate page for each department to document each test, the result, and a description of the fix required if there was a problem. I sit with each person while they’re testing and fill out their spreadsheet for them so they can focus on testing.
  11. Obviously the next step is to address any problems with the migration. This includes data integrity, customization behavior, as well as training issues for your users. Often your users will require some training due to additional functionality provided by the migration, and now is the time to address that.
  12. After you’ve addressed any issues, the next step is to perform the migration again. This time document every step, creating a checklist to follow during the real migration. You do not want to find yourself working through a problem at 3am Saturday morning. This is not the time for improvisation.

Now, you may be asking yourself, “Does David really follow all these steps that thoroughly?” The answer, dear reader, is yes I do. In fact, in preparation for the last migration I performed, which was from 5.51 to 5.6, I migrated the data 5 times. It was necessary because we were migrating from IFE customizations to VBA/FastForms customizations and there were many issues to deal with.

So, let’s assume this whole process was successful and your company is successfully operating on the new version of Made2Manage. You’re finished, right? Not as far as I’m concerned, you still have to address what I think of as the “Hook” effect.


Imagining things makes them so.

If you haven’t seen “Hook,” or don’t remember the scene, Peter (Robin Williams) is starving having trained and exercised all day. The kids bring him dinner but all the bowls are empty. Essentially, if they use their imagination the food appears and everything is wonderful. What does this have to do with Made2Manage? Well, I can’t tell you how many times over the years that I’ve had users complain post migration that the new version of M2M either does or doesn’t do something that the previous version did. They will swear up and down that they’re right. In the real world however, believing something does not make it so.

The only way to settle these claims is to have a test machine available which is still running the previous version of M2M. I typically keep this machine available for a few months after a migration for this purpose.

I’m sure many of you have been involved in M2M migrations, how did you do it?

Inquiring Minds Want to Know About your use of FastForms

I’m trying to determine which M2M topics you folks want to read about. We can all use VBA, but since FastForms is an optional module less people may be interested in reading about it.

Please specify your level of interest in M2M FastForms.

View Results

Loading ... Loading ...

Some Quick Topics

Here are some things I thought were interesting this week:

A Few Good DBA’s This is a humorous take on Jack Nicholson’s rant in “A Few Good Men.” I’ll include an excerpt below as the site requires free registration to view the article and I know many of you won’t bother.

Manager: I want the truth!

DBA: You can’t handle the truth! Son, we live in a world that has SQL Servers. And those SQL Servers have to be guarded by SQL DBA’s. Who’s gonna do it? You? You? I have a greater responsibility than you can possibly fathom.

You have that luxury. You have the luxury of not knowing what I know:

And my existence, while grotesque and incomprehensible to you, it protects SQL Servers…You don’t want the truth. Because deep down, in places you don’t talk about at parties, you want me protecting that SQL Server. You need me on that SQL Server.

We use words like honor, T-Sql code and loyalty…we use these words as the backbone to a life spent defending something. You use ’em as a punchline. I have neither the time nor the inclination to explain myself to a man who rises and sleeps under the blanket of the very protection I provide, then questions the manner in which I provide it!

I’d rather you just said thank you and went on your way.

Otherwise, I suggest you pick up an understanding of SQL Server and start writing some code. Either way, I don’t give a damn what you think you’re entitled to!

Second, I came across this Microsoft Site with free SQL videos. They about using SQL Server Express 2005, but that hardly makes a difference. There’s a ton of good, basic SQL information to be had.

Finally, I received a call from a user last week stating that they could not run the Invoiced Sales Report that I mentioned in my last post. The user received the following error:

Date Outside Error

This of course relates to my previous post about the improper use of error messages. For the most part, users don’t read them, and they serve to do nothing more than impede productivity. This one is no different.

I told the user that he should simply hit OK, and run the report again. The second time, the report ran without incident. Now I can understand why the error is triggered, which is most likely to conserve resources and prevent the user from starting an extremely long, unstoppable, and useless report if the wrong dates are accidentally keyed in. However, shouldn’t the error say something like “Are you Sure?” and then run the report if the user answers yes?

By Your Command... Scheduler

I got an email from someone regarding this post regarding my preference for Crystal Reports. The question is, “What is Command Scheduler and what is wrong with it?”

First, in this blog I try to emphasize the importance of two way communication. If you don’t understand something, or you think I’m crazy, please post a comment and tell me so. Nobody knows everything about M2M, SQL, or anything else for that matter, so put on the white belt and ask.


Every time I read about Command Scheduler, I think of Battlestar Galactica

What is Command Scheduler?

Command Scheduler, and it’s robotic twin Command Processor, automate various processes in Made2Manage. You can schedule reports, custom programs, queue refreshing, and even re-indexing using these tools. This is especially important for those still using Visual FoxPro as you can re-index your tables every night when nobody is using the system.

Command Scheduler

Command Scheduler

The interface is not very user friendly so Made2Manage offers a class on its use. To demonstrate the flaws with this module, I’ll set up a report to run once a week with it. Suppose your accountant wants the Invoice Sales Report (RPIVSL) automatically run every week and printed to a network printer.

Invoice Sales Report

Invoice Sales Report

I highlighted the Run Later button because advanced users click that to schedule their reports. This triggers the scheduling screen, and the screen shot below is set to run Sunday evening at 10pm and repeat each week at the same time.


Seems pretty easy, right?

What’s the catch?

  1. The Command Processor requires a dedicated license in M2M, unlike the UPS Automation Program, and since licenses cost around $5,000 each, it’s an expensive little task manager. In addition, normally users can open a second copy of M2M on the same machine, though this will tie up two licenses. However, if you try to open up a new M2M instance on the computer which is running Command Processor, it simply opens a second Command Processor. Therefore, a computer dedicated to that role will be required.
  2. The Command Scheduler has limited interval choices and the shortest is once per day. If you are regenerating a que, you may need it to occur more frequently.
  3. Dated reports do not work properly. (see below)

So, you come in Monday and if you set everything correctly, your Invoiced Sales Report for the week ending 11/23/2008 will be waiting for you. Everything seems to be working fine until your accountant checks the printout next week and discovers that it is also the Invoiced Sales Report for week ending 11/23/2008. In fact, if left unchanged the Command Processor could run for the next 10 years, and will ALWAYS output the exact same data out of it. Why anyone would want the same 10 year old sales data exported every week is beyond me, but it made sense to the engineer who wrote this code.

There is a change request for this and it is slated to be fixed in Version 6.1, but nobody knows when that might be released. Notice that the change request is now almost 7 years old and is listed as an “enhancement.”

Now in all fairness, a skilled admin can compensate for this limitation by changing the advanced filter to run for a calculated date range. However, I gave up this route years ago and went with Crystal Reports which makes this whole process so much easier.

I, for One, Welcome Our New .Net Overlords (Reporting)

In a previous post I recommended the use of Crystal Reports and listed some of the advantages of doing so. One of the primary reasons I am looking forward to the Made2Manage .Net future is my fervent hope that VFP Reporting will go away. Visual FoxPro reporting must die. In this article, I would like to elaborate on the reasons why I feel this way. Some of these are inherent to VFP, but the rest have to do with M2M’s implementation of it.

  1. VFP Report creation is counter intuitive. When you try to create a new report in the RPWIZ screen, you have to use a VFP report creation wizard. However, there are two steps of the wizard that you must skip or your report will not work. Each time I create a new report, which I have avoided doing for some time, I must look up which steps to skip. According to M2MExpert, you must skip steps 2 (grouping) and 5 (sort order). There are simply too many steps and you have to remember to do things in certain steps or you cannot continue. “Don’t rename the Query.qpr file.” “Preview the layout to make sure data exists, and then save to your m2mdata\reports\template directory.” Why can’t it default to the correct directory? I can’t tell you how many times I forgot that. The reporting classes teach that you need to map out your report with all the tables and fields you intend to pull in beforehand as it is much more difficult to add them later. This is impossible because users are always asking for more to be added to custom reports.

    The VFP Report Wizard isn’t very polished either. For example, you can’t drag and drop field relationships as you can on nearly every other reporting program in the universe.


    Welcome to a 1990’s interface.

  2. VFP reporting is not WYSIWYG so you can’t develop in the presentation layer. In VFP, you must run the report (or print preview) to see changes and the printed report doesn’t always match what is on screen.

    VFP Layoutl

    VFP is edited in this view.

  3. This is particularly inconvenient when you are trying to perfect font settings and the position of objects on the report.

  4. Speaking of objects, don’t ever change grid line position. If you try moving grid lines on reports such as Purchase Orders, Sales Orders, Invoices, etc you’ll never get lines to join properly again. Good luck trying to make perfect corners. It’s incredibly frustrating.
  5. M2M VFP lacks graphing and charting options. Now, I’ve heard Made2Manage Techical Solutions people (customization coders) say many times over the years that there is nothing you can’t do with VFP if you’re an expert with it. As far as I know, you cannot quickly create graphs or charts with it, and people are visual.
  6. VFP does not offer “Page X of Y” abilities. You can print the page number on your reports, but you cannot indicate the total number of pages. With particularly long reports, this is a source of irritation for users.
  7. M2M offers sorting for a maximum of 3 fields. This can be a problem for complex reports where you are trying to get very granular with the data.
  8. Many existing reports are missing data environments. This makes it much more difficult to add fields and tables to existing reports. This has been an issue for as long as I can remember and it’s never been addressed. A couple of months ago a user needed me to quickly add a field to the Purchase Order Status Report (RPPSTA) to assist her with a project, and I replied that it would be done in a few minutes. Unfortunately, I had a surprise coming when I pulled up the data environment.

    VFP Layoutl

    RPPSTA’s missing data environment.

  9. I was still able to edit the VFP prg file and quickly finish the request anyway, but some of those prg files can be 20 pages or more of code. This is the case with the Sales Order Summaries Report (RPSOSM) which is also missing it’s data environment.

  10. Why can’t you copy and paste Additional Filters on M2M Reports? I’ve had complaints from users who need to share reports and therefore need to use the same filters. In fact, when you highlight a filter you’ve written and hit CTRL-C, it disappears and you must re-enter it.
  11. Can anyone tell me why the parameters reset whenever report selections are changed? For example, you’re using the Sales Order Summaries Report (RPSOSM) and you’ve set your start and end dates. After that, “Open Sales Orders” is selected instead of “All Sales Orders.” Immediately the date controls go blank and need to be set again. A relatively minor problem, but frustrating none the less.
  12. Made2Manage does not have the ability to prematurely stop reports. I think every person who uses M2M has made a mistake in requesting a ridiculously large report. I myself have accidentally started a report, usually omitting a start and end date, which would run for 30 minutes. I’ve seen sales order administrators accidentally print every quote in the system. M2M just churns out quote after quote and there’s no way to stop it without using CTRL-ALT-DELETE. There is a change request to which you can add your company if you’d like them to fix it. It’s been in “awaiting approval” status for over 4 years.
  13. Related to the last issue, large reports take a long time to view each page. For example, when viewing page 400, and the next page is selected, M2M literally has to render all 400 of those pages before it can display page 401. Other programs that I’ve used don’t have this drawback.
  14. My favorite red box error. ‘Nuff said.
  15. User permissions in Made2Manage are generally problematic (more on that in a later post) and that includes report permissions. Basically with every report you create, you must manually assign permissions for each user who accesses it. This isn’t a big deal if two people use it, but when managing hundreds of users across several M2M databases, this wastes a ton of time.
  16. Made2Manage reports aren’t always consistent in font and style, especially between the different modules.
  17. Wouldn’t it be nice if you could globally change fonts on your reports in a similar way that web pages are managed? When a properly designed website requires font or style changes, only a single file (or a few files) need to be changed and the entire website is uniformly changed. I don’t fault M2M for this omission as I’ve never used a reporting product which offered this, but I still think it would be enormously helpful. Do you have any vendors who use M2M? It’s immediately apparent because their reports look like yours. I’d just like to have more control over the look of M2M standard reports.

Alright, I’m done complaining. Do any of you have anything to add?

As God is my witness, I thought turkeys could fly!

Well, Thanksgiving is just a week away. Every year about this time I remember one of the funniest episodes of any television show in history. Of course, I am referring to the WKRP in Cinncinati’s Turkey Drop and I still laugh every time I see it.

Turkey Drop

It’s a helicopter, and it’s coming this way. It’s flying something behind it, I can’t quite make it out, it’s a large banner and it says, uh – Happy… Thaaaaanksss… giving! … From … W … K … R… P!! No parachutes yet. Can’t be skydivers… I can’t tell just yet what they are, but – Oh my God, Johnny, they’re turkeys!! Johnny, can you get this? Oh, they’re plunging to the earth right in front of our eyes! One just went through the windshield of a parked car! Oh, the humanity! The turkeys are hitting the ground like sacks of wet cement! Not since the Hindenburg tragedy has there been anything like this! – Les Nessman

Anyway, since this is primarily a professional blog, I thought I would list those things I am thankful for in regards to my career.

  • I’m thankful that I made the move to Dallas, and have found myself in a challenging work environment which motivated me to take my skills to a new level.
  • I’m thankful to certain folks at Made2Manage who made it possible for me to find this job, even though I have been hard on them at times over the past decade.
  • I’m thankful to Brent Ozar and Jeff Atwood for encouraging me to start my own blog.
  • I’m thankful that I work with El Jeffe and The Nuff, who are the best team members I have ever had.
  • I’m thankful for the hard working and helpful SQL experts at SQL Team and SQLServerPedia as they have made learning SQL so much easier.
  • I’m thankful that Made2Manage has not outsourced their support, and I hope they never do.

So, what are you thankful for?

Having “Fun” With Job Order Numbers

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


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:


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
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
		select @onechar = substring(@mystr,@pos,1)
		select @numvalue = (charindex(@onechar,@arrystr)-1)*power(len(@arrystr),@pos-1)+@numvalue
		set @pos = @pos + 1

return @numvalue

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
SET @jonos_left = (SELECT (dbo.fn_base36('ZZZZ')-dbo.fn_base36(@base36)))

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


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


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


SET @jonos_left = NULL

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
SET @jonos_past_year = 1

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.

           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?

What Reporting Methods are You Using?

I’d like to get a better idea of what reporting options my readers are using so I will know which of them I should cover regularly. Feel free to comment on why you use your preferred reporting option(s).

Please check all the reporting options you use.

View Results

Loading ... Loading ...