Hi all, I am working on a PO sheet for my work. I am wanting the PO number to be generated with the current date and then a 2 digit number after it. Starting with 01. I would like for the value to reset to 01 at the beginning of each day with just the date part of the PO changing to the new date. Any help or suggestion would be great. Thanks in advance.

Example 5030201 for first po today, 5030202 for second,5030203, 5030204 etc. Then tomorrow would like for it to then be 5040201 and so on.

Simple. 2 fields required with one relationship.

CreationDate, AutoEntered Creation Date

SequenceNumber, Number, AutoEntered Calc (Max ( Self by CreationDate::SequenceNumber) + 1)

PONumber = Substitute ( datetotext ( CreationDate ), "/", "" ) & Right ("00" & SequenceNumber, 2)

The Substitute function strips the "/" out of the date and the Right function pads the number so that you get a leading zero if it is a single digit number.

Sorry kinda new at this.......How exactly would I go about getting it to perform this.... as a script or field calculation etc.... any help would be greatly appreciated.

As I stated those are fields and relationships.

Sorry to keep bugging you, but I keep having a problem with the sequence number field. When I go to put it in it tells me if can't find the field ( Self by CreationDate::SequenceNumber) ..... I know it is probably something I am missing or doing wrong. Any help would be great. Thanks again

The "Self by CreationDate::SequenceNumber" is a related field. You need to create the relationship "Self by CreationDate" first. This is a realtionship to the same file joined by the Creation Date field.

Hey thanks for all your help and I think I am almost there. I am getting a message when I enter the Auto entered calc in the sequence field that says Field could not be used here it would cause a circular definition. Any help?: I thought I still might not have my relationship set up correctly??? But if in the auto entered selection I can choose the relationship Self by creation and it inputs it just like you have stated. Well if nothing else I have learned a great deal. Thanks for all your help and any more you can send my way.

One more thing that might help is that it is generating the po fine except for that the sequence number does not reset when the date changes.