replacing digits within a string of numbers

[Allegheny]

Each customer in my database has a unique 4-digit customer number. In an invoice number, the two numbers preceding this customer number change based upon when the order was placed and how many orders have been placed in a given year (for example, Invoice #224567 would indicate the second order of 2002 for customer 4567). I want to be able to change just the second digit to reflect a third order for the year 2002 (in the example, the new invoice # would be 234567). Using "Replace" in a script doesn't seem to be the right way to go. Any suggestions?

[CobaltSky]

There seems to be a fundamental limitation in the system you're envisaging, insofar as you are allowing for no more than nine orders per customer within any calendar year. I assume that your business is such that this will not be a problem?

Notwithstanding the above issue, what I suggest you do is:

1. Create a calculating field called 'CustomerYear' with the formula: CustomerID & Right(Year(InvoiceDate), 1)

2. Create a self-join relationship called 'CustomerSerial' which links the CustomerYear field to itself.

3. Create a number field called 'InvoiceSerial'

4. Create an unstored calculation field called 'NextInvoiceSerial' with the formula Max(CustomerSerial::InvoiceSerial) + 1)

5. Define InvoiceSerial as a lookup to copy values from the 'NextInvoiceSerial' field based on the CustomerSerial relationship, and set "If no exact match use" with the number 1 entered into the text box.

6. Create an unstored calculation field called 'InvoiceID' with the formula: Case(IsEmpty(CustomerID) or not IsValid(invoiceDate), "", Right(Year(invoiceDate), 1) & InvoiceSerial & CustomerID)

That's it. You need only include the 'CustomerID', 'InvoiceDate' and 'InvoiceID' fields on your layouts - the other fields operate behind the scenes.

When InvoiceDate and customerID values are entered, the appropriate Invoice ID number (with the second numeral incremented according to how many invoices have been generated for the current customer in the current year) will appear automatically in the "InvoiceID" field.

This will generate numbers from 214567 to 294567 for your first nine orders per customer per calendar year, as per your example. I'm afraid that after that, (as previously noted) things will go awry and the next number generated will be 2104567.