Sorry have become a little busy.
When I look at the images of the action you have posted, I don’t understand what you are trying to do because for each column you are setting it appears you have a SELECT() expression listed. So I don’t know if you are trying to do something specific.
So lets back up a bit. AppSheet is a row-based processing system which means when a column is changed the entire row is marked as changed. We developers don’t readily know which columns have changed. In Workflows, we can test the BEFORE and AFTER values of the columns but in Actions we do not have access to the BEFORE column value, in order to capture it for writing into a table.
In your case, I understand you want to be able to show what has changed for a Project on each change and you want to be able to go back in time to see past changes.
There really is only one way to do that. After each save, capture the row state into the Log table. Then in the app compare the before and after of each Log table row for each column to identify and highlight the changed value.
Capturing Log Table rows
Trying to write the rows re-actively, i.e. after a value is changed, will not work - as stated above you do not have access to the BEFORE value to write it. So, you need to be PROACTIVE and write the CURRENT row state after every Save. It will accomplish the same thing with the main difference being you will ALWAYS have at least one project row in the Log table - the initial Project row add.
To construct your Log table, it should have every column from your Project table PLUS at least a Log ID column AND a Prior Row column (for row highlighting efficiency). The dedicated Log ID column makes sure you can have multiple rows with the SAME Project ID. If you are not capturing in the Project table the Date and Time of the row change then you will want to include this column in the Log table as well.
Then to write the Log table rows is quite simple. Create a single Workflow for Adds and Updates of a Project row. That Workflow calls your Action (“add a row to another table using values from this row”) that sets every column of the Log table. Much like what you had already created with these differences:
- Set Log ID to UNIQUEID() expression.
- Set Change DateTime, if needed, = NOW() expression.
- Each Log column can be set like this:
[Log Column X] = [Project Column X]. If you named the columns the same then it will be the same name on both sides. AppSheet will know which tables based on context.
- Set the Prior Row =
MAXROW("Log", "Change DateTime", [Project ID] = [_THISROW].[Project ID]). If this is the first row being added to Log table, Prior Row should automatically be set to blank.
Highlighting Changed Data
Now that you have your Log table rows inserted and each one has listed the Prior Row, you can highlight the changed column(s). This is the tedious part. You will need to add a Format Rule for EACH Project table column included in your Log table. In each Format Rule you will want to do the following:
NOTE: [Column X] is a generic name representing each one of the Project column names.
- Set “If this condition is true” =
[Prior Row].[Column X] <> [Column X]
- Set “Format these columns and actions” = to [Column X]
- Set the Highlight and Text colors options
- If desired, also set the Text Format options
Create a Slice for a Table display of the Log table
You will likely want to order the Log table rows by Project ID and Change DateTime. I recommend creating a slice and then using an ORDERBY() expression in the Row filter condition to properly order the rows.
Create a Table View to display the Log table
Create your change log view as a Table view type using the Slice from above.
With this setup, you can easily create a Project table Action that uses the LINKTOFILTEREDVIEW(). When attached to a Project and tapped, you navigate to the Log Table view filtering by the Project ID and then you will see ONLY the change rows for that project.
I have thrown a lot of info at you and I hope I am not totally off track here. If this sounds like what you are after then give the above a try.