# Date and sequential - based quote number

This topic is 7500 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

Hi all:

I've got to create a quote numbering system based on an old (unmodifiable) paper system. I understand the logic, but the syntax of the calculation is eluding me. The format of the quote number is as follows:

"Q-4-1-701"

Where:

"Q" is constantly the letter "Q"

"4" is the last digit of the year (i.e. 2004)

"1" is the month (i.e., January)

"701" is a sequential number for that year (i.e., the 701st quote of 2004)

The customer wants to have the sequential number strictly sequential, meaning no skips. He's got several different numbering schemes like this, but this is the worst, as it is dealing with Date information, and I'm just wandering in the dark. Maybe I should have another cup of coffee, or turn on the lights.

-Stanley

##### Share on other sites

Stanley,

I've run across this before and one way to do this is to use the following fields:

QuoteDate - Date entry field, not auto entry

MonthField - Month(QuoteDate)

YearField - Year(QuoteDate)

Sequence - Number field

QuoteNumber is a calculation of "Q-" & Right(YearField,4) & "-" & MonthField & "-" & Sequence

Create a self relating relationship based on the YearField and then create a script to create new records that sets the Sequence field to Max(SelfYear::Sequence)+1

I'm sure there is a more elegant solution, but this will get you started in the right direction. The reason the QuoteDate field is just an entry field and not auto-entry is that the customer will at some point decide that it is easier to enter the date they want and not what your solution wants to do for them.

Mike

##### Share on other sites

hey stanley ...

There are some problems structuring the 'quote number' in this way, and I'm sure you're aware of all of them -- especially if the 'quote number' is used as a relational key. So, I'd come up with a relational key structure totally independent of any user-modifiable value, and give the client what he wants for the 'quote number'. I might try to make a case for using the 2-digit year value in the 'quote number' instead of just the last year digit so a quick look at the 'quote number' will reveal which decade the quote is from

I'd script the "New Quote" routine. This will ensure that the value at the end of the 'quote number' is sequential. In a Utility file with only 1 record, create a number field ("qnumber") that will be incremented by the script as each quote is created. A constant-to-constant relationship between your Quotes file and the Utility file will handle grabbing the current value and incrementing the number.

For the left side of the quote, you have to determine if the client wants to reference the actual date the quote record is created, or a manually entered date. For the former, an Auto Enter by Calculation option for the 'quote number' field definition will do the job. In this set-up, you'd reference a date field with the Auto Enter Date Created option. If the 'quote number' is based on a manually entered date, I'd use a Set Field step following a Pause Script step during which the user will manually enter the date value.

In either case, the following calc inserted into a text field will create the 'quote number':

"Q-" & Right(Year(date), 1) & "-" & Month(date) & "-" & NumToText(constant | Utility_constant::qnumber)

Don't forget a script step to increment the 'qnumber' field after the 'quote number' is assigned to the current quote record.

##### Share on other sites

Thanks all.

I've just been on a six-hour emergency call (corrupted container field disrupting entire FM Server system) and only have the energy to glance at the responses before passing out, but this all looks like what I'm after.

Thanks again

-Stanley

##### Share on other sites

This topic is 7500 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account