ID column not holding - incorrect line showing to edit

[Chris_Bean1]

Hi folks

I have an app for drivers that shows their deliveries for the day. They can only see the lines on the sheet that shows their name, and there is an ID column for each line -  this is done by UNIQUEID() column definition when a manager adds a line. 

Sometimes, when the drivers go into the line on the app and hit the edit button, the details change to another line with their name on it, ie it appears that the ID column isn't being respected when they hit edit. It only happens with some of them and I can't figure out why. The fix is simply to type another ID into the line, but I want to figure out why it's happening in the first place... 

Any help appreciated as I'm getting frustrated messages from drivers...

Thanks

Chris

[Rafael_ANEIC-PY]

Hello @Chris_Bean1, can you double check if your column containing UNIQUEID() is set as the KEY column for that table? that sounds like maybe it's the drivers column the one that is marked as key, causing erratic behavior.

[Chris_Bean1]

Hi Rafael, it is indeed set as the key, yes.

Thanks

[Chris_Bean1]

See screenshot

[mateo]

Hi @Chris_Bean1 
Is the EDIT action system or your own action? Could you attach screenshots with this behaviour? I mean before hitting and after hitting the edit button.

[Chris_Bean1]

Hi there, this link should show behaviour. I received the video from a driver - it shows the details in the view pane then when he hits edit it shows different details. 

https://drive.google.com/file/d/1FXgU81WDjLmOu_f4m4wvYJaC_QL_iN7t/view?usp=sharing

[mateo]

Maybe try to hide EDIT button and set those columns driver needs to edit in detail view as quick_edit columns?

[Chris_Bean1]

Trying this - I've done it for one driver and will await feedback

[Rafael_ANEIC-PY]

Your key column that contains uniqueid() should be set as a TEXT type, not as number.

Can you share a screenshot of those two rows in your spreadsheet that were confused by your app?

[Chris_Bean1]

Ok I'll change the type to TEXT on the apps - I've fixed the two rows, but I'll send a screenshot next time it happens... 

Does changing to text mean that only text is read? At the moment I get spreadsheet users to put a number in, and app users have the ID column autogenerated.

[Rafael_ANEIC-PY · Accepted solution]

You can write numbers in "Text" type columns, but when interacting with any expressions or calculations they will be interpreted as text if no conversions are made beforehand.

I believe that should solve your problem, when AppSheet runs into these kind of "confusing rows" problems it is often a "Key" issue, and since you had a uniqueid() (which is a combination of text and numbers) on a "Number" type column, I imagine something similar to this happened:

[Chris_Bean1]

Ok thanks - to be clear, the ID column on the driver apps is only 'Reading' the ID, not creating it; I take it was 'reading' it incorrectly because the column type was wrong? 

[SkrOYC]

In general, yes.

Some advice:

• Always put your Primary Key / ID column as the first column of your tables. Name them differently on each table. I use TablenameId as primary key and IdTablename as foreign keys (ref columns).
• Always use UNIQUEID() for those columns and hide them, there is a "ShowIf" config on each column for that. It's column type should be Text. About that, a little rule of thumb I use, Number columns should be the ones that you would like to somehow agregate, if you don't mind agregating those numbers, it shouldn't be Number. For example, if you have a Year column you may be tempted to set is as number, but you don't want to sumup years, right?
• Don't touch your database. That's what it is, a database, forget about the common usage you are used to on worksheets. It defeats the whole purpose of the app if other people have access to it and modify data directly on the worksheet, even if the only person doing it is you. I just open them when I want to overwrite a value that is there by accident and I have an EditIf rule that prevents changing it from the app

[Chris_Bean1]

Thank you. All points noted. Sadly, users have to interact with the database directly; luckily this hasn't caused any issues so far... 

[SkrOYC]

They don't have to, that's the way you designed it and you have options to correct that mistake.

Give us clues on why and we might help you overcome it

[Chris_Bean1]

It's what I believe is called a 'wetware' issue - my accounts administrator and directors are all over 50 & technophobic, so getting them this onto a shared g sheet, with apps and smartphones, proper internet etc etc, from a completely paper-based system, has been sufficiently challenging. 

However, I absolutely do not dispute that as the workforce evolves, there is a possibility I could move to a proper database + appsheet system. Is there a specific database that is particularly well integrated with Appsheet? 

[SkrOYC]

Well, is a hard situation for sure, although not impossible,

@FTong is a user that has been upgrading it's work process to embrace new tech.

Also, if you make a desktop-friendly app, generally with dashboard views matching a list + detail view, your users could see it as a better way to interact rather than the old school worksheet on desktop.

About the later, there are a lot of post discusing that in the forum

[Steve]

---

@Chris_Bean1 wrote:

The fix is simply to type another ID into the line, but I want to figure out why it's happening in the first place... 

---

That you can type anything into the key column when editing the row suggests the column you're changing isn't the key column, or you're working with a new row, not an existing row.

Please post a screenshot of the columns for the table (from Data >> Columns in the app editor) that includes at least the key and label columns.

Please also post a screenshot of the view (in the app emulator) the users press the edit button.

[Chris_Bean1]

Hi Steve

From the thread above: 

And the edit action shows in this video 

https://drive.google.com/file/d/1FXgU81WDjLmOu_f4m4wvYJaC_QL_iN7t/view?usp=sharing

Thanks

Chris

[Chris_Bean1]

Ok. I've changed all the apps to TEXT in the column type for the ID columns and tried one app on quickedit. Let's see if the issues re-occur... 

Thanks for the quick help on this everyone, much appreciated! 

Chris

[SkrOYC]

That might help but the problem behind all of it would be hidden, and you have to solve it anyways