Log book for all actions in app

sharad_gupta
Participant II

Hello everyone,

I have a table of project list and another of project log. In which any changes occur to project list goes to project log. Like, status “add” username “xyz” other details of project list. I am doing it with the combination of action and workflow. The problem is when it comes to update in workflow, it sends the new values to the project log with status “update”. But i want the compare value in the log table. And also the red color in the change cell. So that i can easily get that ok! This person changes this value.

Solved Solved
0 20 2,475
1 ACCEPTED SOLUTION

Based on discussion in your other thread, I have realized that the expression above needs amended to this instead:

AND( ISNOTBLANK([Prior Row].[Column X]), 
     [Prior Row].[Column X] <> [Column X]
)

I believe this will cover both the case if [Prior Row] is blank as well as if [Prior Row].[Column X] is blank.


Revisiting showing the highlighted column ONLY for your table view the full expression would then be:

AND( CONTEXT("View") = "My Table View",
     ISNOTBLANK([Prior Row].[Column X]), 
     [Prior Row].[Column X] <> [Column X]
)

View solution in original post

20 REPLIES 20

What is the compare value?

Can you elaborate on what you mean by this?

When a user change any old value in project table like project status or complete task or anything in the table it will update in the project log table. Now it update the whole row of detail of that project in which only one row is update else remains same. So i want that when the user open the project log value he should get the highlighted value with the all values so that he can identify the change.

Sorry. Its still not clear.

" in which only one row is update" did you mean to say “only one column” here? Is the problem you are trying to point out that all columns in the row are updating when only one column was changed?

If so, it appears thats what your Action is implemented to do.

I understand this to mean that you want to select a column and see only the changes by that column?

Kind of.

One change in column update the whole row. This is fine. But i also want that the change value should comes with different color so that easily noticable.

Coloring things in the display is easy. There is a feature known as Format Rules that you can apply to highlight data.

First you need to work out your data design. How you are going to save the changes and how you are going to retrieve them in the app. I am still not clear on how you plan to accomplish these two things.

I am trying this by using action of copy this row detail to another table row, and the workflow change data for update. Any update in the table trigger the workflow which use the action. Thts how it send the row data to the project log.

Any help will be appreciated

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:

  1. Set Log ID to UNIQUEID() expression.
  2. Set Change DateTime, if needed, = NOW() expression.
  3. 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.
  4. 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.

  1. Set “If this condition is true” = [Prior Row].[Column X] <> [Column X]
  2. Set “Format these columns and actions” = to [Column X]
  3. Set the Highlight and Text colors options
  4. 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.

Bonus

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.

Thank you for your detailed help.
I am using Select function in action for copy the value of column from the project list to the log. The action is the same as you mentioned above. By workflow i am activating the action by update only condition using CHANGE DATA.
I am getting all the values perfectly.
When i am using your “prior row” formula creating a virtual column, first it automatically turned into ref column. And in format rules it is also comparing the column data perfectly(using test section).

The problem is only that it is formatting the before row, Not the new row.

And also the change in formatting is happening in all the log related views. How can i restrict those changes to my table view only.

I would not recommend a Virtual Column for the setting of the Prior Row column. I would set a “normal” column in the Action that adds the Log record. The reason is because Virtual Columns are re-calculated on every Sync. As your app grows and more Log table rows are added, the app will become slower and slower. The Prior Row value only needs to be set ONCE at the time the row is inserted.

Can you provide the details of the Format Rule you are using?

I believe you should be able to restrict the Format Rule to only the one table view. You would add to the Format Rule expression something like this:

AND(CONTEXT("View") = "My Table View", <<rest of expression>>)

Based on discussion in your other thread, I have realized that the expression above needs amended to this instead:

AND( ISNOTBLANK([Prior Row].[Column X]), 
     [Prior Row].[Column X] <> [Column X]
)

I believe this will cover both the case if [Prior Row] is blank as well as if [Prior Row].[Column X] is blank.


Revisiting showing the highlighted column ONLY for your table view the full expression would then be:

AND( CONTEXT("View") = "My Table View",
     ISNOTBLANK([Prior Row].[Column X]), 
     [Prior Row].[Column X] <> [Column X]
)

Thank you so much let me try… hope this will work😊

The view formula is working but the format rule still working for the old row, not for the new updated row.

I am still using the VC. When the formula will work, i will switch that to the normal column. Is it ok?? Or it should be normal for this formula to work??

I can’t think of any reason why a VC wouldn’t work for now.

We need to look at one of your Format Rules to confirm the expression. Then we should look at how you are assigning the Prior Row value.


I created a little tester app for myself and it appears to be working good for me.

You are using a change time stamp, and i am using a “date modified” column with the “now()” value. Is that could be a problem??

No, when it comes the app display its only being used to order the rows. It would not be affecting the formatting - unless you are using it it your format rule. I am not.

To see why yours is getting formatted the way it is, it would be good to see:

  1. An image showing an example expression you are using in your Format Rules
  2. A screenshot of your table view with the Log row ID and prior row ID columns showing

sharad_gupta
Participant II

Good New It is working fine now when i changed that VC into normal Column. App is taking that column into the Ref type. Is it ok??

Otherwise all things are good now.

Thank You

sharad_gupta
Participant II

Can You help with the formula which gives the
if any change in column then column value would be “old value, New value”
If no change then column value would be as the same as before.

sharad_gupta
Participant II

I am getting 2 Problems while formatting.

  1. The blank column Problem. I can solve that by to “Require” button on.
  2. When i am switching the tab from Log to another View, Formatting removes and when i sync the app, it appears again. After switching it automatically went off. dont know why

In my test app, I am not seeing an issue with a column being blank or set with spaces. As @Steve has pointed out, there has been a long standing issue where if the left hand side is blank, the operator always returns true. I suspect this might have been fixed along the way as I am not seeing that problem in my tests and I have tested with various combinations of blanks and spaces.

I did make a slight modification to the Format Rule expression. In my test app I am adding a Log row on the initial Project Row save and then on every change thereafter. That initial Log row does not set [Prior Row]. I took advantage of that and the observation in the paragraph above to end up with an expression that applies the Format Rule ONLY when [Prior Row] itself is NOT blank…like this:

AND( CONTEXT("View") = "My Table View",
     ISNOTBLANK([Prior Row]), 
     [Prior Row].[Column X] <> [Column X]
)

Yes I noticed this as well. It’s a bug and I am opening a bug item for it.

Top Labels in this Space