Skip to main content
Solved

Creating an ID number

  • April 14, 2022
  • 4 replies
  • 597 views
C
Forum|alt.badge.img+2

I have a FME worbench that is reading an excel file and merging it with data in a SDE geodatabase. For each record in the table I need to create an ID field that consists of a consistent initial 3 letters depending on the company, then a 2 digit year, and then 4 digits. It would need to be unique as well.

  • I would need to find the last 4 digit number used by the company and add one to it.
  • I would then need to put this all together into one ID field attribute.

Any ideas of how to do this would be appreciated!

Best answer by redgeographics

Okay, this was a fun challenge!

I've set up a small sample, attached here as a template (FME 2021.2)

Screenshot 2022-04-15 at 10.21.08I've created some source data, just the id's of a fictional existing dataset:

RED220001
RED220002
BLU220001

So a new record being added in 2022 for company RED would have to get ID RED220003

 

I use an AttributeSplitter to split the original ID into 3 new attributes: company, year and number. Then sorting and sampling so that I only keep the record with highest number per company per year. Join that to the new data (company is being set by a user parameter, year is coming from a timestap) and then generate a new id:

@Value(company)@Value(year)@PadLeft(@Evaluate(@Value(number)+1),4,0)

 

 

 

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

4 replies

redgeographics
Celebrity
Forum|alt.badge.img+59
  • redgeographicsCelebrity
  • Celebrity
  • 3699 replies
  • Best Answer
  • April 15, 2022

Okay, this was a fun challenge!

I've set up a small sample, attached here as a template (FME 2021.2)

Screenshot 2022-04-15 at 10.21.08I've created some source data, just the id's of a fictional existing dataset:

RED220001
RED220002
BLU220001

So a new record being added in 2022 for company RED would have to get ID RED220003

 

I use an AttributeSplitter to split the original ID into 3 new attributes: company, year and number. Then sorting and sampling so that I only keep the record with highest number per company per year. Join that to the new data (company is being set by a user parameter, year is coming from a timestap) and then generate a new id:

@Value(company)@Value(year)@PadLeft(@Evaluate(@Value(number)+1),4,0)

 

 

 

FME rocks! \m/
C
Forum|alt.badge.img+2
  • chris28
  • Author
  • 32 replies
  • April 18, 2022

I'm so glad you like a challenge! Thanks so much!! A couple of questions regarding your workflow. You rename the id field and then split it and then remove it. You then create it again and give it a value. Just so I understand - what is the purpose of removing the attribute? If you have multiple other features going in you wouldn't want to remove it the attribute I assume? Also, when you are calculating the value of the id field using @Value(company)@Value(year)@PadLeft(@Evaluate(@Value(number)+1),4,0)

So +1 gives you the next number, 4 is the number of numbers, but what is the 0 for? (Sorry is this is an obvious answer)

redgeographics
Celebrity
Forum|alt.badge.img+59
  • redgeographicsCelebrity
  • Celebrity
  • 3699 replies
  • April 19, 2022

I'm so glad you like a challenge! Thanks so much!! A couple of questions regarding your workflow. You rename the id field and then split it and then remove it. You then create it again and give it a value. Just so I understand - what is the purpose of removing the attribute? If you have multiple other features going in you wouldn't want to remove it the attribute I assume? Also, when you are calculating the value of the id field using @Value(company)@Value(year)@PadLeft(@Evaluate(@Value(number)+1),4,0)

So +1 gives you the next number, 4 is the number of numbers, but what is the 0 for? (Sorry is this is an obvious answer)

I'm removing the attribute to avoid confusing myself down the road 😅 Or to be more exact, to be absolutely certain the new attribute ID I'm creating is not accidentally filled with incorrect data. That shouldn't happen, but I prefer to be absolutely certain in these cases.

 

The 0 is a parameter of the @PadLeft function and specifies to pad with 0s. The output of the Counter has no leading zeroes, so that's why I'm padding it.

FME rocks! \m/
C
Forum|alt.badge.img+2
  • chris28
  • Author
  • 32 replies
  • April 19, 2022

I'm removing the attribute to avoid confusing myself down the road 😅 Or to be more exact, to be absolutely certain the new attribute ID I'm creating is not accidentally filled with incorrect data. That shouldn't happen, but I prefer to be absolutely certain in these cases.

 

The 0 is a parameter of the @PadLeft function and specifies to pad with 0s. The output of the Counter has no leading zeroes, so that's why I'm padding it.

Thank you. This was exactly what I needed. I appreciate the extra info. This is the greatest community for getting assistance when you're just learning.

Community Stats

32,066
Posts
121,745
Replies
39,692
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.