Suggestions Needed: Unique IDs

[Qiro]

What's the best way to have a unique ID that isn't immediately available?

For my onboarding platform each person is assigned a unique ID, but they aren't given that ID until late in the process. For example: John Smith may be given the ID "johnsmith" but they won't get that ID at the time that their record is created.

I need the ID to be the key for the table, because it's referenced all over the app in multiple different tables, and making it a computed key with another ID column breaks the referencing.

What I'd like to achieve is assigning a UNIQUEID() to each record, then allowing the user to replace it with the correct ID later, but the UNIQUEID needs to be hidden (because it will only cause confusion to those that don't understand the system) until replace with the correct ID.

Any suggestions on how to make this work? Or better approaches? I'm open to ideas.

[peterson]

Hi there! 
Well, the ID will be instantly created once you save the record and it can't be changed but there is a possibility ( I don't recommend)

You can create one column called ID_Label and you in the ID column you use [ID_Label] as your formula.

And set the initial value of ID_Label as the concatenation of nome and last for example, and after that the person edits this column, the ID will be changed. But THERE IS AN ISSUE...

If the person creates a record and changes the ID it won't work as the reference at all for the ID is different. Another idea is you could create one [count_change] column and set the ID_Label as edit id the [count_change] is < 3 , this way the first change will be the save and the second the ID change, and the user will never be able to change it again.

PS: THIS CAN BE A PROBLEM IF THE USER USES THE SAME ID AS ANOTHER USER.

[Qiro]

I found a partial fix with your extra column idea, I set it as a computed key and applied an expression that uses either the ID column, or the UNIQUEID depending on whether the ID column is populated.

Unfortunately, that only half fixes my issue. The ID column still needs to be the key, because that's how an individual is referenced throughout the whole app, but that ID column needs to be editable, so it can be entered or updated.

[peterson]

Dude, you can't edit the key column, it shouldn't be edited under any circumstances. 

The idea that I gave you can fix your issue. If you need some extra help direct message me.

[Qiro]

I couldn't get the setup you'd suggested to work, unfortunately.

Which column in your example is the key? I ask because you can't set a formula in a key column, and anything related to a key column seems to remain uneditable.

The ID in the real app is a string of letters and numbers that can't be used to identify anyone without a reference. For example: A3CISLWKDMRGER

[peterson]

Yes you're right. ID's cannot be expressions. My bad! I forgot it. 

But you don't need to use this ID. Just by the ID you mentioned, I guess you're using the appsheet database, aren't you? 
Well, you can create an ID column and set some expression in the initial value, it won't be editable but can be better than a random sequence of letters and numbers, and it'll depend on the data you're entering. if they're people I recommend you use the email as the ID, or maybe the concatenation of Name_Lastname as the ID. If this is a product, you may use its name and unity as ID ex: "Coffee Good 500g Gourmet"

[Qiro]

Everything is in Google Sheets tables.

Unfortunately the email isn't a perfect solution, whilst it's static, it's not the way they're referenced in other parts of the system, that's all anonymised and the only connection we have is ID.

This is why I'm struggling so much with the limitations of AppSheet, the ID is the only way to find a person in the system, but they don't have an ID until some time after they are added to the system.

As things stand, the only solution I can see is to go into the data and manually edit it, which isn't a solution at all. Even actions are forbidden from editing keys or any columns related to keys 😫

Incidentally, this is all solvable by using SELECT() but this breaks the referencing, which is a feature I'm trying very hard to keep, as the user can see all the data relating to a person in one place.

The only way I can think of to save the referencing, is to split the table into [People who have not got an ID yet] and [People who have an ID]. This would work fine, but moving a person from one table to the other? Might be ugly. Unfortunately, slices don't fix this because they use the source table for its schema, rather than creating something standalone.

[WillowMobileSys]

You'll find MANY posts here in the Community about this topic...but to keep it straight and simple....

I STRONGLY recommend that you have a dedicated column used as the Key.   This column is assigned a random value at the time the row is created using something like UNIQUEID() - a value that is NOT tied to ANY of the data whatsoever.  This is known as a "dataless" key. Once assigned, this value should never change for that row.

If you do that, you will NEVER have any issues with Key values in the data structure.

A bit of background...as you have mentioned Keys are used in references - i.e. links between rows in different tables.  These references are the structure of the data.  You can change all of the other data on the row, but by using a "dataless" key the structure never changes - it becomes a solid reliable data foundation.

Many will suggest to use as a Key email address, SSN, Employee ID, Student ID, etc...but ALL of these have a potential to change...and yes even the SSN can change in extreme circumstances.  Avoid using any values have even the slightest potential to change because if they do need to change it will be a major effort to update all references across the system!

[Qiro]

That's the approach I have used for every AppSheet app I have made to date. The reason this is different, is because I'm bringing previously separate application functions together, and relying heavily on referencing.

The main issue was created by the child data, which is manually input. This is because it comes from a PDF generated by a different (unchangeable) system. This system ONLY provides the ID of the person, and not their name or any other identifiable data. 

If I don't use their ID as their key, I can't get the reference to work. This is either because I don't have a deep understanding of how referencing works in AppSheet, or because it simply won't work without the ID as the key.

I think I have found a solution, as is often the case after a night's sleep.

Just abandon AppSheet referencing and do it manually! That isn't to say I hadn't tried this before, but I think I've managed to get it working this morning.

[WillowMobileSys]

---

@Qiro wrote:

If I don't use their ID as their key, I can't get the reference to work.

---

Yes, this likely means you are not setting up the references correctly.  But it seems you have it working so good for you.