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:
-
Go to Actions in your app editor.
-
Find your Transfer Tool To: action for the Tools table.
-
Change the Do this setting from App: go to another view within this app to App: edit this row.
-
Delete the expression currently in the Target field (it's not needed for the Edit action).
-
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:
LinkedIn
Twitter
