Manufacturing Database

Is it Possible to create a project # (autonumber) and then use that number to create other autonumbers in other tables with different prefixes? Example below.

Purpose is to make it easier to track all products and work details for a project without having a different auto number for every step, just a different identifier for each step of the same project.

Project Request # 12345
Design Request # DR-12345
Sample Request # SR-12345
Sample # S 12345
Quote Request # QR-12345
Sales Order # SO 12345

Well, of course it’s possible. You don’t specify your database type, or backend language, or anything specific, so any answer you get will be pretty general. But if you were to have a job table, or document, and the original project number were associated with that, then each of the other documents/tables would simply reference that.

For example, assume a SQL type database, you might have a jobs table, with fields like the following:

id int auto_increment primary_key,
client_id int foreign_key,
title text,
description text,

… so that would be created when any step of the process is initiated, and as each step of the process initiates, they’ll simply use this id as their job_id. They’d likely still have their own internal id field, like so:

table sample_requests
id int auto_increment not_null primary_key,
job_id int foreign_key,

then, when you reference that (via PHP or perl or whatever, you’d build the tracking number as you see fit - the prefix would indicate the job step (SR), and the job number would let you reference specifically which sample request record to fetch.

Are you likely to have multiple sample-request or samples for a given job? In that case, you could give each a secondary tracking number, by adding a secondary key, like S-12345-164 (for example). That 164 on the end would be the sample record’s unique id. This would allow the job id to be used in multiple samples, but the id field would render each unique.

Without more specifics, this is a thought experiment, but the short answer is yes, it is very feasible.

Hey Snowmonkey,

Thanks for the reply.

Sorry for not providing full disclosure, but I have only worked with access and I am not a developer or plan to be one.

I am only wanting to improve the ERP system we are implementing as a company so it works better and is better for the customer experience with us.

The example you provided is exactly what I am looking to do, including the secondary key. The trouble is that the IT team developing the program said that it is not possible?

What ERP are you using? Is it custom built for your company?

1 Like

We are using Datatex to develop the ERP which I expect is based on everything they have done in the past for textile manufacturers. []