Getting an error of There is already a row with the key: ' ' with UNIQUEID() set in keys

[MKapfer]

I have a simple Tools inventory app. The error occurs when I try to transfer a tool to from one person to another person with an action button. If i do it without the action button, i do not get an error and it works fine. I'm not sure what information is needed so I will screenshot my tables & the action set up. Please let me know what other information I need to give. 
Table 1 key columnTable 2 key columnTable 3 key column
Picture of the error receivedThe action button set upBot trigger conditionStep/Process action

[Kirk_Masden]

Perhaps you could combine UNIQUE(ID) with something else, such as the user ID with CONCATENATE().  There's no limit to the length of an ID.

CONCATENATE(UNIQUE(ID), whatever you decide to add).

I do this in an app of mine when I copy records.  If you use a special character to divide the original ID from the copied one, it's even possible to use SPLIT() to find originals with copies or vice versa.

[MKapfer]

I still get the same error

[Kirk_Masden]

I'm not sure what the problem might be.  If you add text to one key and not to the other (the original) they should be different and therefore that should prevent the error.  But, perhaps I've misunderstood the problem.  I hope someone else can help.

[MKapfer]

I'll give it another try maybe i did something wrong

[Kirk_Masden]

If the "key" in your spreadsheet or data is the same, that would be the problem.  I would need to look at your app to figure out what is happening but it is true that "keys" have to be unique.  When I have copied records in the past, I have sometimes set things up so that a datetime is appended to the original key.  That insures uniqueness, although keys can get rather long.

[MKapfer]

That's a really good idea, I will try the date/ time append to the key on Monday.

[MKapfer]

here is a Screen Shot repro of the error. 
I can add Screen Shots of how the actions are set up if needed, and This particular tool was added before i made the change to the UniqueID initial value which is why it has the UUID, not the TIMESTAMP attached to this tool.

Tools table overviewTools table key [Tool ID] Initial value setupTool selection screen where 'Transfer Tool Button 'is visibleScreen/View that appears once you click Transfer Tool ButtonSelected & Clicked Save on a new employee to transfer this tool TO and the error applied

[Kirk_Masden]

How are you setting the [Tool ID] column when you copy a record?  I think that UNIQUE() should be adequate to set the [ToolID] initially.  Then, when you copy the record, something like concatenate([Tool ID],NOW()) could be used for the for the [Tool ID] of the copied record.  Or, if you really need this [Tool ID] column to be a single "Tool ID" (common to different records that use the same tool), you can't use it as your "key";  You need to make a separate "key" column.  Then, you can have two records with the same Tool ID.

[MKapfer]

So i tried UNIQUEID() and your CONCATENATE idea of UNIQUIEID() with timestamp NOW() and what happened was the tool ID for that specific tool stayed the same so if it was say '123abc' when i went to transfer that tool to a new person it would say There is already a row with the key '123abc' and even with those different Appends it would give me the same error. 

I'm not sure if there is something wrong in how i set up the 'Transfer' actions. 

IF i remove the action 'Transfer Tool Button" I can manually transfer the tool and it logs correctly but then I run into a new set of issues. 

[Kirk_Masden]

I'm afraid I may be misunderstanding your situation so let me explain what I am doing in an app of my own.  As the name of this action indicates, it copies a record from one table to another.  The tables are different but the newly copied record will eventually be moved from the Kankaku table to the Archive table, and will have to coexist with the original record.    For the most part, I merely copy the individual fields from the the original record as is ([Word] from the Archive table equals [Word] in the Kankaku table, etc.).  

 

However, the [Key] fields (which functions as the "keys" in both tables) cannot be the same; for them to coexist on the same table in the future they must be different.  So, here's what I do:

The action uses the "key" from the Archive table as its base and then adds additional text, making it different (unique).  The particularly expression I'm using is for my own needs and isn't one I would recommend to you but this general approach might be useful to you.

If I have misunderstood your situation and you are trying to do something quite different, my apologies.

[SkrOYC · Accepted solution]

Here is what my secret AppSheet Mentor AI assistant says are my comments: 

Show More

Okay, let's get this sorted out! It looks like you've set up an inventory app to track who has which tool, and you want a simple way to transfer a tool to someone else while also logging that transfer. The good news is that your automation setup for logging the transfer looks correct! The error "There is already a row with the key..." usually pops up when AppSheet thinks you're trying to add a new record with a key that already exists, instead of editing an existing one.

Here's what's likely happening and how to fix it:

The Likely Issue:

The action you created ("Transfer Tool To:") uses LINKTOFORM("Tool_Transfer_Form", "Tool ID", [Tool ID]). While LINKTOFORM can sometimes open a form for editing if the view name matches the table's default form view, its primary purpose is to start a new form entry with pre-filled values. It seems AppSheet is getting confused and trying to create a new tool record using the existing [Tool ID] when you save the form, hence the "duplicate key" error.

How to Fix It:

The most straightforward way to edit an existing row is to use the built-in "Edit" action type. Let's adjust your "Transfer Tool To:" action:

1. Go to Actions in your app editor.

2. Find your Transfer Tool To: action for the Tools table.

3. Change the Do this setting from App: go to another view within this app to App: edit this row.

4. Delete the expression currently in the Target field (it's not needed for the Edit action).

5. Save the changes to your app.

Why this works:

• The App: edit this row action type specifically tells AppSheet, "I want to modify the current row."

• It uses the default system-generated form view for the Tools table, ensuring AppSheet knows it's an edit operation, not an add operation.

• Think of it like pulling out the specific file for "Simple Hammer" from a filing cabinet (App: edit this row) versus grabbing a blank form and writing "Simple Hammer" at the top (LINKTOFORM potentially causing confusion). AppSheet needs to know it's working with the existing file.

What about the Automation?

Your automation ("New Bot" triggered by "Tool Possessor Changed") seems correctly configured!

• Event: It triggers on Updates to the Tools table when the Current Owner changes ([_THISROW_BEFORE].[Current Owner] <> [Current Owner]). This is perfect.

• Action: It correctly adds a new row to the Transfer Log table, capturing the Tool ID, the timestamp (NOW()), the previous owner ([_THISROW_BEFORE].[Current Owner]), the new owner ([Current Owner]), and generating a unique Log ID (UNIQUEID()) for the log entry itself.

This automation should work perfectly fine once the main Edit action is corrected as described above.

Key Configuration Check (Good Practice):

Just double-check these settings for your Tools table:

• The Tool ID column is correctly marked as the Key (the key icon is checked).

• The Tool ID column has UNIQUEID() set in the Initial value field (which you have).

• The Tool ID column is NOT editable (the Editable? toggle should be OFF, or an Editable_If condition should evaluate to FALSE). Keys must never change once a row is created.

In Summary:

Change your "Transfer Tool To:" action type to App: edit this row and remove the Target formula. This should resolve the duplicate key error by ensuring AppSheet correctly handles the operation as an edit.

Give that a try, and let me know how it goes!

References:

• Actions: The Essentials (See App: edit this row action type)

• What is a key?

• LINKTOFORM() function

• Automations: The Essentials

Show More

My take:
Change your LINKTOFORM() for a Linktorow() with a form view.
LINKTOFORM() is to add new rows
LINKTOROW() is to open a view (a form view in this case) for an existing row, hence the name

[MKapfer]

Oh my goodness, THANK YOU everyone for the help. Changing from linktoform to LINKTOROW solved my error and it works as intended