How to Update PO# when duplicate a PO?

[MTG]

I have a PO# in this format 560800000. "5" stays the same for all PO, "6" is the year, "08" is the month, the five 0s are auto-enter serial number. I defined the PO# as "5 & Mod (Year(Get(CurrentDate)); 2000) & Month Function & PO Suffix". The month func. is if month < 10, add 0 in front, else use the two digit month. PO suffix is just a serial# (00000), auto increment by 1.

I'm working on duplicate record script, so that all the info from the PO will be copied, but the PO#. I want to auto reset the PO# to the next value. I use duplicate record/request script step, but the PO# won't be updated. Tried set next serial value, but I can't really do that because the PO# field is defined as text with auto-enter calc.

Any suggestions? Your assistance is highly appreciated.

[Ender]

What's the point of building such a complex PO number? Your PO Suffix alone should be sufficient as a unique ID.

[MTG]

I just follow the PO# convention from my institution. If I design it myself, I would make my life easier.

[T-Square]

Methinks this numbering scheme is highly flawed regardless, and you might just want to point out those flaws to someone. For example, what's with that initial '5'? And, what happens in 2010, when the digits reset (can anyone say Y2K all over again?)?

Even if you have to stay with this scheme, please consider just having a simple ID field that is an Auto-Enter serial number, and use a secondary field (either stored or calculated) for this PO#. And then, DON'T use this field for any relationships--just display.

I will assume that your institution resets its PO Suffixes at the beginning of some time period (such as calendar or fiscal year). To derive a PO Suffix for the current year, you can use a selfjoin relationship based on record-creation year to build a simple calculated field:

Right("0000" & Count(CurYearPOs::PO_ID); 5)

And your formula could be simplified:

"5" & Mod (Year(Get(CurrentDate)); 2000) & Right( "0" & Month(Get(CurrentDate)); 2) & PO Suffix

But, really, you might take this opportunity to introduce some sense to the system. Why hide all this info in a code, when you can just display it directly to the user in human-readable form?

David

[MTG]

Thank you so much David.

My institution has a big project in process, so what I'm doing is only going to be a temperary solution. Anyone has any idea of how I can increment this PO# by 1 from the last record?

Thanks in advance for any hint on this.