Unique ID Generation to Existing Database

Question

Hello,

I would like to add new IDs into existing database.  I would like to skip existing IDs and add new IDs in the name alphabetical order.

Here is the sample existing database:

IDName1AAA2BBB3CCC7DDD10EEE11FFF

This is the sample new data:

IDNameNEW1GGGNEW2HHHNEW3 IIINEW4    JJJNEW5    KKKNEW6    LLL1AAA2BBB

The desired output is this: (new IDs are added as 4, 5, 6, 8, 9, 12)

IDName1AAA2BBB3CCC4GGG5HHH6III7DDD8JJJ9KKK10EEE11FFF12LLL

I also attached a sample workflow.

Sincerely,

Kazumi

Sample Unique ID Generation.yxzp

nick_schimweg · Accepted Answer

Hi Knozawa,

I wasn't able to download your workflow. Please take a look at the workflow I've attached, let me know if it makes sense. As always, I'm sure there is a better way to do it however this was the first thing that came to mind.

NicoleJohnson · Accepted Answer

Take a look at the attached, I believe this achieves what you are looking for?

1. Find matching Names

2. In the existing database, find the "gaps" by using the Generate Rows tool to find all ID's between the minimum and the maximum, then join to the existing database to find which ones do not exist in the existing database.

3. Join by Position to the new data to match however many records are needed to fill gaps in the existing ID's.

4. Union to matching names to create a list of existing plus new between the original minimum & maximum ID's, then sort ascending.

5. Union to any remaining ID's from the new data set, and then use the Multi-Row tool to generate new ID's for remaining records.

Let me know if this works or if you have any questions! :)

NJ