Creating job/reference numbers for new entries

[Barbara Pivetz]

I have just downloaded the 30 day trial version for evaluation, and I have a few questions.Here is my situation:

My company (a consulting firm) has a paper filing system which we MUST use in addition to keeping our databases. The software we currently use (based on MS Access) does the following for us:

In addition to keeping a database of the various pieces of information pertaining to each of our client�s jobs (such as cost estimates, invoice #�s, dates, amounts, auditing results, etc.), upon entry of a new job, the software will generate a job # for us to use for easy reference and organization in our paper filing system.

Our software creates a job number containing the following pieces of info, in the following format:

First, a three letter code for whichever CLIENT the job is for. For example, if the client for a new job we�re entering is ABC Company, our code, ABC, will be the first three letters of our job number. Each one of our clients has a different 3 letter code.

Second, a three letter code for that client�s PRODUCT. Each of our clients might have any where from one to twelve (or more) different products. For example, if ABC Company�s products were widgets, wangles, and whatzits, their 3 letter product codes might be WID, WAN, and WHA, respectively.

Third, a single digit numerical code that refers to the type of job it is. This would always be one of the numbers 1-7, and this number also corresponds to what color folder to use in our paper filing system.. We currently deal with seven different types of jobs.

Fourth, a single digit number which corresponds to the last number in the job�s year. For example, all 1999 jobs have a 9 here, all 2000�s a 0, all 2001�s a 1, etc.

Fifth, a three digit sequential number ( 001, 002, 003, etc.) that is incremented by 1 each time a new job is created with the same client, product, and job type. This gives each job # its uniqueness, because we can have many jobs that are for the same client and product and job type in the same year.


Each part of our job number is separated by a dash.So, the format of our job numbers looks like this: ABC-WID-2-1-003
Where ABC is the client, WID is the product, 2 is the job type (and folder color, i.e., green), 1 is for the year 2001, and 003 means that this is the third �type 2 (green folder ) job� created this year for ABC�s widgets.

The part that is complicated is that there must be a new sequence of numbers starting at 001 every time the client and/or product and/or job types are different. If we have for example, a total of 900 jobs for the year (for all our clients put together), we don�t just want them all numbered 001 through 900 (in one big pack). We want them separated by client, product, and job type as outlined above, and then numbered sequentially starting at 001 within each job type for each of the client�s different products.

Can I accomplish this type of automatic job number creation and sequencing with just the basic expressions, expression builder, and action scripting features of the program, or will I really have to get into XBasic programming (or hire a programming consultant) in order to do it? If more detail is needed, I can provide it, but I hope this gives the general idea.

The nature of our company�s work requires that we have a paper file system, and that they are color coded and sorted in this way for quick and easy filing. We look up a client�s job # in our database, cross-reference it to our job #, and then we can pull the paper files quickly. I can�t deviate too much from our current system, but I definitely think Alpha has major improvements to offer in the database management, querying, and data reporting areas compared what we are currently using (an Access based system � ick! ).

Any thoughts on whether or not Alpha Five will work for me in this way will be appreciated. Thanks!

Sincerely,

Barbara Pivetz
The GMA Group, Inc.
[email protected]

[Robert Adler]

RE: Creating job/reference numbers for new entries

Barbara:
Access must be using a substr command to create parts 1 & 2 of the string. Part three must be keyed as a color or a number. Part 4 is the last position of the date. can be obtained by converting the date dtoc() conversion. then use substr to get the last position. The sequence number is tied to part 3. Build a table and attach it to the customer. the key to this table is part3 with part5 as the data field. read the table based on part 3. read the data increment by one. save result and put new value at end of your key string. You will have to insert code to handle the i/o on this table.

this code can be inserted in the key fields can write event. should be calculated after the other fields are created/entered.
you could put this in the fields field rule.

this product provides many different approaches to the same result

I hope this helps.

bob adler

[Stephen Williams]

RE: Creating job/reference numbers for new entries

I believe it would be possible to create what you need by

1. Create seperate fields for each piece of the job number that you mention.
2. Fill these pieces using field rules as appropriate (lookups to customer data, auto-increment by field rule or function as you prefer, etc)
3. Create a field for the final job number and make this a calculated field of piece1+"-"+piece2+"-"+piece3 etc

OR
use xbasic and a user defined function

OR...

One bad thing with Alpha Five is the number of possible ways that will work. Sometimes you cobble together a solution that creates problems as you failed to account for some needs elsewhere in the application. But I don't think Alpha is unique in this struggle.

[Barbara Pivetz]

RE: Creating job/reference numbers for new entries

Thanks for your help! I'll post something later to let you know how it's going.

[Barbara Pivetz]

RE: Creating job/reference numbers for new entries

Thanks again! When I have the final solution, I'll post some info. Hopefully it'll help someone else too. Keep your fingers crossed...

[Pat Bremkamp]

RE: Creating job/reference numbers for new entries

Barbara,

We have a similar kind of complicated serial number for our product that indicates date (month and year), machine number,and an incremental number that starts over at 0001 each month.

The incremental number is the tricky part. Everything you want to do is easy to set up except for that, because you have to code your own increment routine; the autoincrement routine won't work. We did that by creating a table and a form for job setup where initial setup data is created from simple table lookups using field rules (no coding). We put a field in that table that calculates the next number that will be used (using expressions and functions-no coding), then copy the setup data with the next job number to the master table as each job is started.

The only coding you will need is a routine (we use a button) to reinitialize for the new year.


Good Luck.