Assigning unique IDs within column cateogry

[speisenkarte]

Hello world!

I’ve been at this for too long so I’m asking for help. Let’s say I have a table of rescue dogs where each row is a different dog, and dogs are categorized by different shelter locations. I want assign each dog a unique incremental ID specific to the shelter it is at. If I have 2 dogs at a shelter in Nebraska and 3 in Colorado, I’d like Nebraska’s dogs to be dynamically ID-ed as Dog-1 and Dog-2, and for Colorado’s to be Dog-1, Dog-2, and Dog-3.

I’ve gotten somewhere in two directions. First, I made incremental row numbers by finding the index element of an array of RowIDs (which would work if the table were state-specific). Second, I’ve made an array, essentially [AllStates], removed the row’s State from [AllStates] to show what states the dogs are not in [NotInStates], then removed [NotInStates] from [AllStates] to [InStates] which is an array where I can count the total number of dogs for that state category.

Am I on the right track? I tried replicating [InStates] but with RowIDs so I could flex ‘Find Index Element’ for later template column incorporation but I’m too inexperienced with javascript to understand how to manipulate an array.

Thanks in advance-

[GrumpyRick]

I did something similar for a client with just a Location ID column and an incremantal column starting at 0001 that increases by 1 with each new row. Then I added a template column as the "ID"column with the “location ID-number”. Could then pull an array of all in that column and filter based on the location ID.

[speisenkarte]

Would that not then create IDs for a location category starting with values larger than 1? The goal is to restart the ID at 1 for each location.

[Darren_Murphy]

Interesting challenge.

Just a couple of points of clarification. You start by saying that you want unique by shelter, but then you talk about states. So is it actually by shelter, or by state? For example if you have 2 shelters in Colorado, do the dogs in each of those shelters have their own set of ID’s, or is it a single set of IDs for the state, shared across both shelters?

You also mention that you want these ID’s to be dynamically assigned. Does that mean that it doesn’t matter that an ID for a given dog can change? eg. The big black dog in Colorado is Dog-3 today, but tomorrow it is Dog-5.

Do you have separate tables for each of Rescue Dogs, Shelters and States, or is everything in one table?

[speisenkarte]

Ah - I made a mistake in my analogy, thanks for asking for clarification. I meant to say shelters everywhere so [Shelter], [NotInShelter], [InShelter].

Yes, the ID should be dynamic. In this case, the counts of dogs at the shelter changing are very infrequent.

This is all one table of dogs with a column defining the shelter

[Darren_Murphy]

Do mind providing a screenshot of the table, just to give me a good visual?

I have a few thoughts, but I want to make sure it’s clear in my mind first, so I don’t end up solving the wrong problem.

[Darren_Murphy]

Try this:

• Add a RowID column to your table.
• Create a multiple relation column that matches the shelter name with itself
• Create a Lookup column targets the RowID column via that relation. This will give you an array of RowIDs
• Use the Find Element Index column to find the index of the RowID in the array of RowIDs
• Use that in a template column to assign the ID to each dog.

Here is what that might look like:

Note that array indexes are zero based. If you want to start at 1, you could use a math column to add 1 to the index.

[speisenkarte]

Will do! I’ll have to follow-up later once I have access but until then, it is essentially:

RowID: Location: DesiredID
0 Shelter1 Dog-1
1 Shelter1 Dog-2

2 Shelter2 Dog-1

3 Shelter2 Dog-2

4 Shelter2 Dog-3

[speisenkarte]

Absolutely stellar-

Working result attached