Unique Numbering By Field

[timmy]

I have a database of projects. Each project has a project number. I want each project of a given project number to have a unique number.

So there would be records with project number 0101 and unique number 1, 2, 3 ,4, etc.,

Also projects with number 0102 would have unique numbers 1,2,3,etc.,

How in the world do I do this?

Thanks in advance, you guys rock.

Tim.

[Chuck]

It sounds like you have projects and then you have jobs. If this is the case, I would suggest that you use different databases for each, which would then make it easier to handle your unique job number task. Each project would have a unique number and a field which keeps track of the next job number. When a new job is created for a project, it references what the next number would be, appends that it its own id number and increments the project's next job number by 1.

If this isn't the case, or such a solution won't work for you, try creating a calculation field that extracts the project number, such as Left( Job_Number, 4 ) as it would be given your examples. Create a self-join relationship based on this calculated field and use the Count function to determine what the next number for the job would be. For instance, if you don't have any current jobs for that project, the count in the relationship would be zero, so you would append a 1. If there have been four jobs for this project (01011, 01012, 01013, 01014), then this relationship would give a count of 4, and the next job number would append a 5.

Chuck

[timmy]

Hey Chuck,

Yeah. You're right, I actually do have 2 databases running. One of them is a database of projects and the other is of jobs. Jobs may have the same project number. So the first solution you talked about seems viable. Could you be a little more clear about how to execute this? Thanks so much,

Tim.

[Chuck]

Tim,

It's good that you have these in two different files, since a limitation of the single file solution I outlined is that if you delete a job then the numbers will be off.

I'm going to assume that you're in the Jobs database when you are creating the new job. In your Projects database, create a number field called Next_Job_Number with an auto-enter data of 1. It needs to be one to start with assuming that there aren't any existing jobs for the project. If there are, then you need to either script what the field should be, or if there are only a few, enter it manually.

In the Jobs database, after a job has been assigned to a Project (i.e., given a project ID which allows it to view data for the assigned project) run the following script:

Set Field [ Job_ID, Projects::Next_Job_Number ]

Set Field [ Projects::Next_Job_Number, Projects::Next_Job_Number + 1 ]

This process can also be fully automated if you begin from the Projects database (so that the user clicks a button in Projects for New Job and everything is taken care of), but the process is a bit more complex and uses relationships in some interesting ways. If you're interested, I can go over this too, but I want to make sure that this part is understood before I try to put in any other details.

Chuck