Workflow Step to Execute action on set of rows - Skipped or Erroring Out

[greggillam]

I have a bot that triggers on updates to an events table. The events are created either individually or in bulk using @Steve's looping method. Events created in bulk are all tagged with the same ID number (labeled "Cadence ID") in a separate column so I know the user added them in bulk. I'm trying to allow the user to make changes to one of the events created in a bulk sequence and have those changes apply to all events within that sequence (i.e., all events that are tagged with the same Cadence ID).

Edit Event Form: When the user is editing an event with a Cadence ID, a button will show at the end of the form (enum with a single response option; show_if expression ISNOTBLANK([Cadence ID])) asking the user if they would like these changes to apply to all events in the cadence.  If they select yes then the bot is triggered.

Parent Dataset: The parent table has an expression for whether updates are allowed: index(Current User Access[Demand],1). This checks the user's email against a separate access table to determine permissions. I have ALL_CHANGES permission and I've tested the bot in the app editor and in the live app with the same results. The parent table also has a security filter to pull in only events with a date >= today()-7.

Edit All Rows in Cadence Bot: This bot looks for updates in the parent table and has a run condition of [_THISROW_AFTER].[Edit Cadence]="Yes". Bypass security filters in on. The 1st step in the bot process is to run action on rows with a referenced rows filter of: filter("data_demand",and(in([cadence id],data_demand[cadence id]),date([Start Date/Time])<=date(data_demand[Start Date/Time]))). When I test filter criteria it seems to pull in the correct row IDs. The referenced action is to set the values in the row columns of each event with a matching cadence ID. The 2nd step in the bot process clears the initial edit cadence field (the field the user selected when first editing the form, to indicate they wanted the changes to apply to all events.

Testing Outcomes:

1. I've tried running the action off of a form submission instead of a change bot to no effect.
2. The second step in the bot completes which seems to indicate that the filter criteria for the ref rows (bot process step 1) is where the issues lies. I've tried modifying this criteria multiple times to no effect. I've also tried adding a virtual column to the parent table which lists all related unique IDs for any cadence event, then using that virtual column as the ref rows filter criteria. No change in outcome.
3. When I check the automation monitor the bot appears to be triggering fine. I checked the audit history but wasn't able to discern any problem from there.
4. When i click to monitor the bot I get a blank table, which is strange but may be due to the security filter. It doesn't seem relevant because the bot does run to completion just without the first step triggering.

Thanks in advance for any help with this! 

[WillowMobileSys]

If you are finding that the Bot is running fine and that the second Step in the Bot is running and that no errors are being reported, then the likely problem is that your action is not selecting/finding rows to be processed.  

In looking at your FILTER() function, the criteria is not quite right I think.  It needs to tie back to the Parent row you are using but when column names are the same between the two tables you MUST use the [_THISROW] qualifier to prevent ambiguity.

You currently have the below:

filter("data_demand",and(in([cadence id],
                         data_demand[cadence id]),
                         date([Start Date/Time]) <= date(data_demand[Start Date/Time])))

I believe it should be (note the removal of "data_demand" in the Date function, its invalid and likely prevents the function from selecting any rows:

filter("data_demand",and([cadence id] = [_THISROW].[cadence id]),
                         date([_THISROW].[Start Date/Time]) <= date([Start Date/Time])))

I don't precisely understand the relationship between these two tables and why you might be testing by Date.  But if all you really want is to get the list of all the child rows, there normally, in a proper relationship, would a column under the Parent named something like [Related data_demands].  To send all children rows through the action for processing you simply need to specify this column name

[Related data_demands]

[greggillam]

Thank you for your response. I should have mentioned that the rows are all in the same table. There may be any number of rows in a cadence but they all have the same cadence ID in a particular column. I need to find all rows that have the same cadence ID as the row that was edited.

Also, strangely, I get the Unable to find column '_THISROW', error when trying your suggestion.

[WillowMobileSys]

In that case, the expression with the red highlighted text should work.  Please give it a try and let us know if it helps.

[greggillam]

I get the Unable to find column '_THISROW', error when trying your suggestion.

[WillowMobileSys]

There is an extra parenthesis after [_THISROW].[cadence id] i failed to remove when editing:

filter("data_demand",and([cadence id] = [_THISROW].[cadence id],
                         date([_THISROW].[Start Date/Time]) <= date([Start Date/Time])))

Also, now that I know these are all in the same table, I am not certain if the order of the date logic is what you need.  Please verify it. 

[greggillam]

I caught the extra parenthesis and removed it before testing the expression. The order of the date logic is correct. Any other suggestions?

[WillowMobileSys]

Can you show the action in which you are using the expression?  And include the full expression you have in the expression assistant just as a double-check.

[greggillam]

filter("data_demand", and([cadence id]=[_THISROW].[cadence id],
[_THISROW].[event date]<=[event date]))

I have a virtual column that formats the date/time to just date so simplified the expression by just using that.

[WillowMobileSys]

I don't see anything wrong with what you are showing.  

FILTER is just a shorthand way to write a SELECT.  Maybe try the SELECT() version of the expression:

SELECT(data_demand[<<key column>>], and([cadence id]=[_THISROW].[cadence id],
[_THISROW].[event date]<=[event date]))

 Replace "<<key column>>" with the actual key column of the data_demand table.

[greggillam]

I have actually already tried this with no change in the outcome. 

[WillowMobileSys]

Which outcome?  Do you mean you still get that "unable to find column [_THISROW]" error or something else?

[greggillam]

Yes, same error.

[WillowMobileSys]

You are using the "custom action" feature (for lack of an actual name).  I don't use it, but as I understand it, the "custom action" feature allows one to quickly setup an action for the Bot but it doesn't go into the normal action pool.

I created one yesterday similar to what you have setup and used the [_THISROW] property with no trouble but I didn't do anything complicated.

The only thing I can think of as to why you are getting the error, is that the expression doesn't think there is a Source table defined.  For the type action you are creating there is a Source table, the one the Bot is triggered on, and then you are transitioning to the Referenced table.  The source table needs to be defined on the Event and the Process, if there is one,  and they need to match.

Double check the Event and the Process to make sure the table is defined on these components and match. 

Beyond that, the only other thing I can suggest is to create a formal action and insert that into the Bot instead of using the "custom action" feature.

[greggillam]

Ok so I unlinked the actions and process and confirmed they all had data_demand as the source table. I just tried creating a new bot from scratch with the same exact criteria and it's no longer throwing the [_thisrow] error. That's one problem solved at least.

Now to the core issue - when I run the bot the first step is skipped or atleast does not update any other rows within the filter. The second step completes fine. Any thoughts here? Thanks again for working through this with me.

[JPAlpano]

This might be similar to the issues I had with the same workflow setting.
Check if this thread helps you.
Run Action on a set of rows - Form Saved vs Automation bot 

[greggillam]

Thank you. Your issue does sound similar. I read through the thread and to summarize (correct me if I’m wrong) it seems you solved the issue by unlinking your action to the bot, changing the action name, selecting the new action name as the referenced action back in your bot, then saving. Is that correct?

[JPAlpano]

Something like that.
But I never linked the action in the beginning in the first place.

My issue was similar to you, the BOT doesn't seem to read the FILTER() row references.   So then I created an Action that does the same.
Then that's the time I went back to the Bot and turned on linking.

That's when I was able to  select the Action I already made.

[greggillam]

@WillowMobileSys Any other thoughts here?

[greggillam]

Hoping to raise this again. Upon checking the audit logs, it seems that the referenced rows filter is picking up the correct matching rows, but for some reason the referenced action is not firing. Could it have to do with the Are updates allowed? expression on the source data. I have all_changes edit access but I'm not sure if actions are handled differently. Or could it have to do with the Only if this condition is true field on the referenced action (which is set to true). Any insight would be appreciated.

[WillowMobileSys]

Sorry.  Have been extremely busy.

Yes, if "Are updates allowed" is not set to allow editing then any edits an action attempts on an existing row would fail.  But this should surface in the logs as an error message something to the effect of "Updates are not allowed" kind of message.  You can test this easily by turning on "Updates" and re-running your test.

My advice here is if you need to allow any changes to rows in a table by user OR automation, then always have "Updates" allowed.  If the goal is to prevent a user from making any changes to the row, then disable the system Edit action.  You do that by setting the "Only if this condition is true"  property to FALSE.  The Edit action will not display anywhere in the app but automation can still apply updates.

[greggillam]

Thanks for the reply. I have a slice determining edit access to the table based on useremail(). Multiple different teams use the app and some need to edit rows outside of this automation, so setting the edit action only if condition to false wouldn't work. I updated the are changes allowed expression on the table to be if(in(useremail(),current user access[email]),index(Current User Access[Demand],1),"Adds_and_Updates") thinking that if the bot is not being run through my user profile then it would default to adds/updates. Same result though - none of the referenced rows were updated.

[greggillam · Accepted solution]

The issue here was that in my referenced action I assumed the (bot) triggering row values would be the only ones considered when the action was run. I had to update the set row value columns to select() expressions.