How to new unique identifying number without losing prior- to-date identifiers?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
I'd like to update the unique identifiers tied to product production. The product numbers came in like this: 2015001234 2015001235 2015001236  However, I'd like to now use a new numbering system which is derived in another fashion based on ((YY)(MM)(6-digit number)). 
How do I implement the new numbering system without loosing all the previous numbers that were already assigned and distributed prior to a particular day (i.e. April 4, 2016). After that point use the new number? I tried this, but I think I am mixing formula filed types: 
If ([Date Created]<Date(2016, 4, 4), [Record ID#]+2015001000, Right(ToText(Year(Today())),2)&Right(ToText(Month(Today())+100),2)&Right(ToText(1000000+[Record ID#]),6))
Any direction would be greatly appreciated.
Photo of Kevin

Kevin

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Hi Kevin, 

Try this:

if([Date Created]<ToDate("04-04-2016"),totext([Record ID#]+2015001000),

Right(ToText(Year(Today())),2)&Right(ToText(Month(Today())+100),2)&Right(ToText(1000000+[Record ID#]),6)


)
Photo of Kevin

Kevin

  • 0 Points
Hi Eric- Thanks for responding. Unfortunately, I am getting the following error message:


The expression [Date Created] on the left hand side of the operator "<" is of type date/time while the expression ToDate("04-04-2016") on the right hand side is of type date. The operator "<" cannot be used with these types of expressions.

(I'm using a field type - "numeric formula")

Any other ideas?
if(todate([Date Created])<ToDate("04-04-2016"),totext([Record ID#]+2015001000),

Right(ToText(Year(Today())),2)&Right(ToText(Month(Today())+100),2)&Right(ToText(1000000+[Record ID#]),6)
Photo of Kevin

Kevin

  • 0 Points
I feel this is so close, but I continue to get the following error message.

"Formula syntax error
Please check the syntax of your entire formula. Possible culprits are a mismatched set of parentheses, missing quotes, or an extra bracket.

if(todate([Date Created])<ToDate("04-04-2016"),totext([Record ID#]+2015001000),
Right(ToText(Year(Today())),2)&Right(ToText(Month(Today())+100),2)&Right(ToText(1000000+[Record ID#]),6)  

I'll try to review. Any ideas?