Issue with Data Retrieval Between Steps in an AppSheet Automation

[JeeHill]

Hello everyone,

I'm working on creating an automation in AppSheet, but I'm stuck on an issue with retrieving data between steps.

Here’s my workflow:

1. Trigger: When a row is added or updated in the "Tasks" table.
   Data change type: Adds / Updates.

2. Steps:

   • Step 1: Add a document ("AddDocument")

     • Action: Add a new row to the "Documents" table.
     • Values:
       • File: [DropProof]
       • Name: [Job Reference], "_", [Client Id].[Name]
       • Type: "Proof"

• Step 2: Add document links ("AddDocumentLinks")

  • Action: Add a new row to the "DocumentLinks" table.
  • Values:
    • EntityType: "Task"
    • TaskId: [Id]
    • Test: [AddDocument].[Id]

• Step 3: Reset the "Proof" field

  • Action: Update the column DropProof to an empty value.

My problem:
I can’t retrieve the Id of the document created in Step 1 when working on Step 2.
When I use [AddDocument].[Id], AppSheet returns the Id of the current row in "Tasks" (instead of the document's Id).
The same happens when I try [AddDocument].[Name], but it throws an error:

"Error in expression '[AddDocument].[Name]': Unable to find column 'Name'".

Does anyone have an idea how to resolve this issue? Thanks in advance for your help! 😊

[lynchk21 · Accepted solution]

Try this:

1. Add a [taskID] column to the 'Documents' table
2. In step 1, set the Documents[taskID] to the [ID] of the row in the 'tasks' table that triggered the action
3. In step 2, use a LOOKUP([AddDocument].[Id], "Documents", "taskID","Id") to retrieve the [Id] column from the "Documents" table

[JeeHill]

Here’s a message you can use:

---

Hi lynchk21,

Thank you so much for your solution—it works perfectly! I appreciate the time you took to help me out.

I do have a follow-up question: In my initial setup, I noticed that [AddDocument].[Id] didn’t retrieve the value from the "Id" column of the record created in the "Documents" table during the "AddDocument" step. Do you have any idea why that might be the case?

Thanks again for your support!

Best regards,
Jee

---

[lynchk21]

Without seeing your data structures, I am not sure. Two things though:

1. You can only use the reference structure [reference column].[other column]  with a REF type. Is [AddDocument] a REF column to the 'Documents' table in the 'DocumentLinks' table?
2. I don't see where the Bot would be able to get the need [addDocument].[id] to be able to use it in Step 2. The Bot is operating off the 'Tasks' table, so the values immediately available at those in the 'tasks' table. Without a LOOKUP(), you would need to start from your tasks table and reference down to the 'documents' table to get that value. 

Sorry if that doesn't make sense - If you need more info, provide some screen shots of all your data tables and I would be happy to look!

[JeeHill]

Thank you for your reply and for pointing that out. I think I might have misunderstood how to use [StepName].[VariableName] based on the Google documentation. From what I understood, when creating an entry in a table during a step (in my case, the "AddDocument" step that adds an entry to the "Documents" table), [StepName] becomes the object for that new entry. I thought I could access the columns of the created entry by using [StepName].[ColumnName].

In my example, I assumed that after the "AddDocument" step, I could access the "Id" of the document I just created using [AddDocument].[Id]. Interestingly, in the Expression Assistant of the next step, under the "Example / Columns" tab, I can see "AddDocument" listed as a REF column dynamically created for the automation.

I based my understanding on this page from Google's documentation:
https://support.google.com/appsheet/answer/11463007?hl=en

However, I’m not sure I fully understand how this works or how I’m supposed to use this feature. Could you help clarify how this is meant to work or what I might be doing wrong?

[lynchk21]

Oh I see, I have never read that documentation or attempted to use the [StepName].[VariableName] approach. Looking at the documentation, it seems like you might first need to use the 'Return values' step to then be able to use the [Step] to access them. It is not entirely clear to me for the article. However, if this approach is faster and more reliable, it might to worth digging into. Thanks for sharing!