Want to reference changes in one table as entries in another table

[dsvare]

Hello,

I'm trying to make an intuitive ticketing system that provides clear information to it's users and a way to track a history of updates/changes to the "case". 

There is a "case" table and a "notes" table. I would like any changes made within the "Case" table to reflect as an update in the "notes" table for that case (so it'll be listed as a note within the case). 

As an example - if a user changes who the "case" is assigned to - I'd like a note to appear which states something like "User Email XYZ Changed Assigned Rep from John Doe to Jane Doe - 01/01/22" or something of that nature. 

I've played around with bots and can get a note to trigger on each change which records the date of the change but I cannot figure out a way to pull the change info (before / after values) into the note itself. 

I've also played around with the behaviors - also to no avail.

Any help is appreciated! 

[Joseph_Seddik]

Welcome to the community!

One easy way that does not involve bots:

1. Create a custom edit action that would it self group two separate actions: one to copy the row into a new row in a single-row buffer table, and the second is the existing system's edit action.
2. Create a Form Saved action that would also group other two actions: one to add a new note listing the fields that has been changed, and the second to delete the buffer row. 

[dsvare]

Sorry for the long response. I just want to make sure I'm understanding correctly. 

So, I would add a new table (lets call it "Buffer") 

For step one:
* For record of this table - "Cases"
* Action - Do This - "Data: add a new row to another table using values from this row"
- Within this action list out all the columns within that row so that all the 'old' values will be listed within the buffer table
* Table to add to - Buffer
* Set these columns to - "=" (I believe this will bring over the exact values from the columns to the buffer table)

* Then, create a grouped action and group "Edit" and the new action created above

This, should, trigger a copy of the row in question to be made on the buffer table when the user selects the option to edit the case table.

For step two:

* For a record of this table - "Notes?" 
* Action - Do this - Set the values of some columns in this row
* Set these columns - I assume I'll pull the values of the buffer here (as the "Before" value)

* For a record of this table - "Buffer?"
* Action - Data: delete this row - to remove the row on the buffer table

Questions:

1) Do you happen to have an example (or reference material) that may walk through this? 

2) How would you call on the before / after values within the note? I know we'll be able to reference the buffer table for the "before" values, but how would we pull the after for the note?

3) Is there any concern over multiple users entering notes at the same time with the buffer table and clearing the correct row?

Any details you're able to provide is greatly appreciated. I'm just starting to dive into the world of Appsheets and I can see the potential power within the program, but am still a novice.

Thank you!

[Joseph_Seddik · Accepted solution]

@dsvare wrote:

For step one:
* Set these columns to - "=" (I believe this will bring over the exact values from the columns to the buffer table)

You have to create the table Buffer with the same columns as Cases. Then inside the action, in the value field the expression would be the column's value. This is done by enclosing the column's within square brackets, like this: [assigned to]. Please read this: Column Value Expressions | AppSheet Help Center.

For step two -  first action:

• For a record of this table: Cases
• Do this: Data: add a new to another table using values from this row
• Table to add to: Notes
  • caseID: [caseID]
  • noteBody: for example: 
    
    CONCATENATE(
      "User: ",  USEREMAIL(), ", changed:
      ",
      IF([Assigned Rep] = ANY(Buffer[Assigned Rep]),
        "",
        "- Assigned Buffer from: " & ANY(Buffer[Assigned Rep]) & ", to: "
        & [Assigned Rep] & ", on " & [date] & ".
        "
      )
      IF([Status] = ANY(Buffer[Status]),
        "",
        "- Status from: " & ANY(Buffer[Status]) & ", to: "
        & [Status] & ", on: " & [date] & ".
        "
      )
      // and so on..
    ) 

The condition to trigger this action would be: 

OR(
  NOT([Assigned Rep] = ANY(Buffer[Assigned Rep])),
  NOT([Status] = ANY(Buffer[Status])),
  // and so on..
)

Questions:

1) Do you happen to have an example (or reference material) that may walk through this? 

Expressions: The Essentials | AppSheet Help Center 
Actions: The Essentials | AppSheet Help Center 

3) Is there any concern over multiple users entering notes at the same time with the buffer table and clearing the correct row?

---

The buffer table will be filled upon edits to a Cases record, not with adding a new note, which is in another table. If two users are editing a case (not adding notes) at the same time, then there would be a possibility that some edits could be missed.