Auto enter Serial Number

[gregorytan]

HI

I created a Job Record Sheet and using the Auto enter Serial Number for my Serial Number on every Record.

I set the Auto Serial Number as 2009/0001, so the next Job Record sheet 2009/0002 and so on. I had no problem on this.

The problem here is that sometime, in one Job we have differ edition and we need to have the same Job No with a Alphabet add to the back of the Job No. eg Job Record Sheet 2009/0002 for one edition, Job Record Sheet 2009/0002A for another edition and so on. Is they is a new Job Record the Job no start 2009/0003

Have anyone know how to sole this ? or any other way I can do it.

Thanks

gregory

Edited January 30, 2009 by Guest

[bcooney]

A key field should be meaningless!

If you want a meaningful number for your Jobs, then manually enter it. It should not be the field used in relationships.

In your case, I'd break apart your scheme into three separate fields. I might even have a new table for Editions (so I could see all the Jobs for that Edition).

[KirkR]

Another approach might be to create a second field for edition, and a 3rd field, which is job# concatenated with Edition.

In fact, I might consider another concatenation for your job number. The auto-enter number includes year; what happens next Jan 1?

Create a YEAR field, and concatenate this with the serial ID for display purposes.

SO, the concatenated field might be - YEAR & "/" & pk_jobID & editionfield

This also affords some great flexibility in identifying how many jobs get exceptions (editions) by counting the editions field, and finds based on year.

Further refined .....................

The auto-enter serial number is going to update every time you create a new record - no easy way around that and keep referential integrity intact, nor do you really want to.

You will need to add another field, with the job record ID in it, but NOT an auto-serial number field. It will be an auto-entry field, with the calculation set for entering the content of the auto-serial number field. Check the box on auto-entry to "do NOT replace existing value of field (if any)".

When you create an EDITION, don't create new, but duplicate the existing record (there is a script command for that). The Serial ID will change, but all the other content will remain the same, INCLUDING the newly created JOB ID field. You can then edit whatever content you expose to the user, and your original job record remains intact - nice for comparing EDITION deltas.

The concatenated field that takes YEAR, JOBID and EDITION, should use the auto-enter calc field for JOBID, and NOT the auto-serial number field. That way, the JOBID number will remain the same for duplicated records, just the EDITION character will change.

You'll end up with records for JOBS and additional records for EDITIONS, each with their own data, editions being a change or superset of JOBS. If there are 4 editions to a job, the auto enter serial number field will have 4 IDs (Not to be seen by users), but the JOBID will be the same (result of duplication and not change checked in field auto-enter), and the concatenated field will have the YEAR prepended, and the EDITION post-pended to the JOBID.

That should get you there.

Summary:

pk_JOBID - autoenter serial number

Date field - auto-enter creation date

JOBID field - auto-enter calc from pk_JOBID

EDITION field - maybe a custom value list of A-Z? for ease of entry.

WholeJOBID - the year from date field & JOBID & Edition

According to the stored format for DATE, the LEFT, MIDDLE or other parsing command to get just the YEAR from the date field will have to be determined.

Listings sorted by pk_JOBID (not that a user would ever see this field), would show record creation order

A match field or find command for a JOBID would get you all editions, as the EDITION field is separate. Use the WholeJOBID to display a FIND of JOBID (again, JOBID would be another field that the user would never have to see, at least in reports).

[comment]

I might even have a new table for Editions

It seems quite clear that Editions are not the same entity as Jobs, and therefore deserve a table of their own.

[gregorytan]

Dear all

I had created the file and test on it. Notice that when create new record both JobID run ok, and when do a duplicate only the pk Job ID number change. But when you duplicate more than one time and you create new record the job id jump.

Attach file for your to take a look

Thanks

gregory

Auto_Serial_Number.fp7.zip

Edited February 1, 2009 by Guest

[LaRetta]

I have read and re-read this thread and, for the life of me, I cannot understand why you have complicated your IDs in this way. Your JobID field won't increment because it is based upon your PK_JobID and it is set to auto-enter calculation (do not replace existing value) so it CAN'T update when you duplicate the record - serials will not duplicate and will increment ONLY if you use the FM serial function.

You have one ID depending upon another depending upon ... uh, your primary serial should NOT have the year within it. I encourage you to step back and re-think what you are doing and what you are trying to accomplish. I worry that you are twisting yourself into pretzel and your IDs will NOT hold up in multi-user environments, if at all ... as you are experiencing in your simple test.

Have the year be separate field - you've had some great advice already ...

LaRetta

Edited February 1, 2009 by Guest