Skip to main content

I think I have painted myself into a corner here. Appreciate any advice.

We’ve migrated from another system that generated autonumbers for our Leads.

I’ve imported all the  leads them into my Leads table. It is critical I keep the old data - it can’t be changed as all our  work for these clients has been against that specific ID number.

You can see from my Screenshot that column A has the ID number (now a text field), column B has the customer name, and column C is a joining of the two (but just a text field for now, no formula/concatenation used).

I have tossed in a 4th autonumber column to try and create a formula that carries on the numbering from column A (Lead Number). But now I’m stumped. Having an ‘m’ at the beginning of the Lead number does not help - otherwise I presume a simple sum formula to add the lead number to the autonumber would suffice. 

It’s like I need historical numbers to be text, but all future records to be a formula.

Any ideas? Thanks in advance.

 

Hey ​@boppa,

If I’m following, you do not need to follow the exact same lead numbering convention (field A) for future leads, but only for historic ones. Right?

If so, you could have an additional field which could be called “Lead ID” which could be the following formula:
 

IF({Lead Number}, {Lead Number}, "n"&{Autonumber})


In this way, the Lead ID for all historical leads will be the original Lead Number (starting with “m” as in your original system), and all new leads will have a new (but similar) numbering convention. The ID for these new leads will start with an “n” (as in “new”, and similar to “m”), and will have a number.

If you still need the ID + Lead Person field (called “Lead Text” on your screenshot) you can fully replace such field with the following formula:
 

{Lead ID}&” - “&{Lead Person}


Please let me know if I’m missing something! Also, feel free to grab a slot using this link. I’d be happy to help.

Mike, Consultant @ Automatic Nation 
YouTube Channel ​​​​​​​

Try: 

  1. Create a new formula field that’ll output the correct autonumber ID for the next record you create
    1. Probably going to have to deduct a number from your current ID given that your current autonumber is 29k and you basically need to hit 172?
  2.  Use a formula to combine the ‘m186’ bit with that ID, and use a REPT to add in the zeros

I’ve set something up here for you to check out, and here’s the formula I used:

IF(
  {Old ID},
  {Old ID},
  'm186' & 
  REPT(
    '0',
    5 - LEN(ID & '')
  ) &
  ID
)

This system will fail once you hit 5 digits of autonumbers though, if that’s a concern then you’d attempt to get the ID to match 600172 instead of 172, which would give you 6 digits instead, etc etc

Hit me up if you could use some help setting it up

Thanks ​@Mike_AutomaticN - this worked a treat and gave me some flexibility in adjusting what I needed. Thankyou also ​@TheTimeSavingCo for your detailed response - I can think of a few places I can use this in the future.

Terms & ConditionsAccessibility statement