Special Sequential Job Numbers Field

[Strouss]

I would consider myself an intermediate user/designer. What I am trying to do is beyond me and I need the code or calculation for it.

I am creating a jobs table. The job number field can't be just a serial number field. We have multiple jobs per customer. What I am trying to create is a field that uses the customer number and appends a sequential job number to it separated by a hyphen.

When I paste the customer number "0001" into the customer number field I want a script that will take that number, paste it into the job number field, find how many records that number occurs in and appends found number of records + 1 to that number seperated by a hyphen.

If a customer number is entered as 0001 then I need the job number to read 0001-001 for the first job for that customer, 0001-002, 0001-003 for additional jobs and so on.

Thanks in advance for your time.

[Philland]

Try this,

Create a self relationship based on the client number field

Create a number field (for your serial number) with an auto-enter calculation,

Count(RelationshipName::Client Number Field)

Then if you need this displayed as a single field, create a calculation field whose result is text, with the calc,

Client Number Field & "-" & Serial Number Field

Phil

[Strouss]

Thank you, that worked great.

Now how can I make it so that the number is set in stone. Currently if I delete one of the previous records it will change all of the subsequent job numbers. Beyond record creation I need the job number to be stagnant or fixed, un modifiable. It's OK if the job numbers skip because I removed one of the records.

[Philland]

Is your serial number field set up as a number field with an auto-enter calc or is your serial number field set-up as a calc field itself?

If it's a number field with an auto-enter calc it should only evaluate on record creation and not be affected by subsequent deleting of a record (thank you Queue).

And you should set the field as Do Not Allow Entry In This Field in the layout.

Phil

[Strouss]

I changed the error and it works now. Thanks again