Change Data Workflow

[Remon_Toma]

Hello,

i have table called “Supplies”
i created a new behavior for this table which is execute an action on a set of rows. i configured that behavior and it works pretty good when i click on it.
the issue is i want this action to be automatically triggered when some data changed in this table, so i made a new workflow, chose update only under when that happen, and under do this i chose change data and chose the behavior i have just created.

but when tested it gives error and when changing any data this workflow is not working.

any ideas ?

[WillowMobileSys · Accepted solution]

It would help to know what the error is.

Also, you can check the Log for maybe additional information. At the top of the Workflow there is button named “Log”. Click on that and scroll down to find the row where the Workflow was attempted to execute.

If an error occurred it will have red text, green test if it ran successfully. Click on the binoculars icon in that row to get more details. If an error occurred you will be given more details.

[Remon_Toma]

Thanks for that i did not realize about the log
now i could understand what’s going wrong. thanks again.

[sharad_gupta]

I have a related question… how can i compare an updated value to the old value. Like change in price. So i can get the change in price by calculation.

[Phil]

See https://help.appsheet.com/en/articles/2792618-before-and-after-values

[sharad_gupta]

I tried it… but it seems like to trigger the workflow. I need is the change row in another table with the highlighted updated cell value. So that anyone can notice that update.

[Phil]

Can you explain what you are trying to achieve in more detail?

[sharad_gupta]

I am using action with workflow CHANGE DATA in updates only condition. So when user change data in my project table. Workflow trigger the action and copy the whole row with change data to log table.
I am getting the satisfactory results also. But the problem is i am getting the update values. I want here the
1· if condition
If there is no change in column value then it uploads the old value to the log table and if there is a change in column then it uploads the “old value, New value”.
(User can change any value in project table. Thats why i need this function to identify the change in the log table.)

2. The change column will be highlighted or some sort of format rule to the change column only.
   (I want this format rule to restrict only to the table view of log table.)

[Steve]

The only way you’ll be able to get the old value is by finding the previous version of the row in your log table. Otherwise, the old value is already gone and unavailable to the workflow action.

[sharad_gupta]

I have the old values store by the log table itself. I am using lookup formula or maxrow formula to get the last value in the VC.
But i am new, so i need help to put that in if formula.
And for highlighting one of my friend suggest me to use format rules with the condition “[VC].[column]<>[column]”.
It is working but it formats the last row not the new add row. And also i want to restrict that formatting in table view only.

[Steve]

So a huge issue you’re going to have is performance, both because you’re using a virtual column, and because you’re using format rules. Both are very expensive, and will just become more so as your data set increases in size. My recommendations:

• Presumably, your log table contains a column for the row key of the original (non-log) row that will be the same for all versions of the row stored in the log. I’ll call this column Original Key.

• Add a new regular (non-virtual) column to the log table called (e.g.) Prior Version to contain a Ref to the immediate-preceding version of the row, with an Initial value expression of:

  MAXROW(
    "Log Table",
    "_ROWNUMBER",
    ([_THISROW].[Original Key] = [Original Key]])
  )
  

  Replacing Log table with the name of the log table.

• Add a new regular (non-virtual) column to the log table called (e.g.) Changed Columns to contain an EnumList identifying the columns that have changed from the prior row version, with an App formula like:

  LIST(
    IFS(NOT(IN([Prior Version].[Column 1], LIST([Column 1]))), "Column 1"),
    IFS(NOT(IN([Prior Version].[Column 2], LIST([Column 2]))), "Column 2"),
    ...
    IFS(NOT(IN([Prior Version].[Column N], LIST([Column N]))), "Column N")
  )
  

  Replace Column 1 (note it occurs three times) with the name of the first column you want to compare, Column 2 with the second column, and so on. I’ve used IN() to do the comparison because = and <> are not strict comparitors and would make for a much more complex and confusing expression.

• In your format rule, the If this condition is true expression for (e.g.) Column 1 would simply be:

  IN("Column 1", [Changed Columns])
  

As your log file grows, it will consume more and more memory, which could be detrimental to the operation of both the app and the device. Consider using a security filter to limit the number of log rows made available to the app. You may even want to consider a separate app for viewing the log.

[WillowMobileSys]

Steve:

Add a new regular (non-virtual) column to the log table called (e.g.) Changed Columns to contain an EnumList identifying the columns that have changed

It appears you and I had slightly different approaches to deal with the changed columns.

Log book for all actions in app Questions

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 Workf…

Referring to your post, is there any reason you know of why you couldn’t in the Format Rule for [Column 1] simply use the expression below as the condition?

[Prior Version].[Column 1] <> [Column 1]

The complaint was that it is the column in the prior row that is being highlighted, not the “current” row and I am not sure why that would be.

Having not implemented this, I just want to be sure I am not missing something here in my understanding.

[Steve]

Did’t realize 'til after I posted that you’d been tackling this exact issue elsewhere. Didn’t mean to step on your toes!

WillowMobileSystems:

is there any reason you know of why you couldn’t in the Format Rule for [Column 1] simply use the expression below as the condition?

Because <>, like =, isn’t a strict comparison: if the left operand is blank, the expression is TRUE regardless of the right operand. If both the prior and current column values are blank, the expression is TRUE and the format rule would indicate the column has changed when it has not.

WillowMobileSystems:

The complaint was that it is the column in the prior row that is being highlighted, not the “current” row and I am not sure why that would be.

I can’t explain this, and would request evidence.

[WillowMobileSys]

Steve:

Because <> , like = , isn’t a strict comparison: if the left operand is blank, the expression is TRUE regardless of the right operand. If both the prior and current column values are blank, the expression is TRUE and the format rule would indicate the column has changed when it has not.

Understood, but this can be countered by including an ISNOTBLANK() check into the expression - correct? - which I should have thought of and included in the first place…

[Steve]

WillowMobileSystems:

this can be countered by including an ISNOTBLANK() check into the expression - correct?

Yes, but you have to check both sides:

IF(
  ISNOTBLANK([A]),
  ([A] <> [B]),
  ISNOTBLANK([B])
)

Versus my approach:

NOT(IN([A], LIST([B])))

Both are confusing. Mine’s shorter.

[WillowMobileSys]

Steve:

Yes, but you have to check both sides:

Umm, but wouldn’t you miss the change from Non-Blank to Blank? I guess we also need to worry about blank to non-blank.

Steve:

Both are confusing. Mine’s shorter.

Shorter on the backend but NOT shorter on the FRONT end - i.e where the log row is set! My turn to

[Steve]

ISNOTBLANK([A])	ISNOTBLANK([B])	<>	Changed?
TRUE	TRUE	TRUE	TRUE
TRUE	TRUE	FALSE	FALSE
TRUE	FALSE	TRUE	TRUE
FALSE	TRUE	n/a	TRUE
FALSE	FALSE	n/a	FALSE

[WillowMobileSys]

LOL! I completely missed that you were using an IF() instead of an AND().

[sharad_gupta]

Log book for all actions in app Questions

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])…

This approach Solves the problem with some problems.

Log book for all actions in app Questions

I am getting 2 Problems while formatting. The blank column Problem. I can solve that by to “Require” button on. 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

Is the second approach suggested by Steve can help me in this???
Or this is something else Problem?

[Fabian_Weller]

I had the same problem: Action “Data: execute an action on a set of rows” worked when I click on it in a Detail view. But it didn’t work when I attached it to a Workflow “Change Data”.
The reason was: I had enabled Required_If and Reset_On_Edit expression that was TRUE, when I’m in “Label_Form” View:
CONTEXT(View)=Label_Form

I had to change this expression to:

AND(
  CONTEXT(View)=Label_Form,
  CONTEXT(Host)<>Server
)

Now the Action is working also via Workflow, because the columns don’t get deleted.