Jump to content

Need help with Auto entry Serial Number


Dragon Lady
 Share

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

Recommended Posts

Hello everyone - I am new to this forum, and I hope someone can help me :)

I am building a mail order data base for my company.

We need a unique invoice sequence# comprised of the following

If Month=3 Year=06 Number= auto entry=0000+1

The return of the invoice number needs to look like this: 03060001

They are also asking that at each month's end. the first 2 characters roll over to the next month.

I have spent days trying to find a solution for this. Any suggestions warmly welcomed!

Edited by Guest
Link to comment
Share on other sites

... but the first 2 characters are the month...Anyway, i'm thinking that you go with a calculation field that combines a serial field and an unstored calculation resulting in text.

I.e. Month(Get(CurrentDate)) & Right(year(Get(CurrentDate));2) & SerialField

Hmmm, you could do it with a self join relationship, where the criteria is an unstored calc field on parent table resulting in a date, the calc itself being Month(Get(CurrentDate)) & Year(Get(CurrentDate)) and resulting in number.

On the child end of the relationship you have an auto enter number = Month(Date) & Year(Date), where date is an auto enter date field that enters on creation of the invoice record. Now finally, to trigger your reset portion of the script, you would use the following check:

If [Count(SomeField) = 0]

Perform Script [ResetScript]

End If

*SomeField is a field in your child table that will always have some content in it...*

Hope this helps...and i haven't confused you, and those no flaw in my logic, and someone else hasnt answered this already... Lol, see my disclaimer.

~Genx

Link to comment
Share on other sites

Hi! thanks for your solution:

Month(Get(CurrentDate)) & Right(Year(Get(CurrentDate));2)& newSerial# Invoice

did return what I needed: 3060001

However, any ideas on how I can get the zero to appear on single digit months? to return: 03060001

Edited by Guest
Link to comment
Share on other sites

Hi Kathy, and Welcome to the Forum.

Beware, comment will probably streamline this.

I would use Two fields

[color:blue]Serial ([color:red] Auto Enter - Serial, [color:green]Number) Start with [color:blue]0001 increment by [color:blue]1

[color:blue]c_Serial ([color:red]calc, [color:green]Text Result) =

Right("00" & Month(Get(CurrentDate)); 2) & Right(Year(Get(CurrentDate));2) & Serial

HTH

Lee

Link to comment
Share on other sites

Beware, comment will probably streamline this.

No, but I don't think it's a good idea to auto enter "0001" into a NUMBER field.

I'd leave SerialID as a number, auto-entering from 1. Then:

Right ( "00" & Month ( Get(CurrentDate) ) ; 2 )

&

Right ( Year ( Get(CurrentDate) ) ; 2 )

&

Right ( "000" & SerialID ; 3 )

Link to comment
Share on other sites

Hocked him .... LOL

Actually, I had started out using your Serial Calculation, then switch it to see if the Number would hold, when it did, i just used it instead of the [color:blue]Right ( "000" & SerialID ; 3 )

Link to comment
Share on other sites

Just Replace the occurrences of [color:blue]Get(CurrentDate) with [color:blue]Your Date Field of Choice and it will still work.

However, if you are using a date that isn't the Current Date, then the Auto Enter - Serial, could be something different than in date order.

as an example, lets say you created three invoices today, and the order is 1, 2, 3. Lets say that the Sales dates involved were 3/1/2006. 3/3/2006 and 2/24/2006. Your numbers would be

0306001

0306002

0206003

HTH

Lee

Link to comment
Share on other sites

Hi Kathy :wink2:

I can't help but wonder ... and maybe this is no longer an issue but:

In the olden days, a computer's date/time could stop working. It seemed to be because of the CPU battery? I'm no hardware person and I haven't heard of that happening for 6-7 years now. But basing something as critical as IDs on a date strikes me as potentially dangerous. If this serial is the basis for your relationships, someday it might bite you. I don't know whether you are served or not, but I believe creation dates pull from the workstation. What if a User changes their computer date? OR what if this solution is moved overseas and begins to use International settings? I believe in this instance it probably wouldn't matter but I would certainly test it). And the reason the business wants this number is to make sense of it. If they move their base office to Great Britian then they will be unhappy with it there. Will all of your attempts to structure it (and the date) hold? Particularly when using text dates?

If you are served, you might use Get(CurrentHostTimeStamp) instead to at least cut down on the possibility of error. I just wanted to express my concern. Again, I may be overly fussy - I've been accused of that before. :crazy2:

LaRetta

Link to comment
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.