Re: Cannot "Run action on rows" in a bot

jc22 · 10-17-2024 01:01 PM

Hi! I'm new to AppSheet. I've looked everywhere in the docs and Google to find an answer to the following and have been unsuccessful. So posting my first question. Thank you in advance for any help!!

My goal is to have the user select multiple rows and choose an action to send an email based on all the selected rows data. Because multiple rows (potentially up to 25) need to be added to a table, I have worked out an automation to wait (5 min) until the process finishes before sending the email.

Because I don't want the bot to send an email for every row that is added, I am trying to set a column (SentToCarrier) to true in the Users table as a condition for the bot to know the email has been sent and now all the rows can be deleted.

Everything is working except that last part. I cannot get the action to set the column to true from within the automation.

SETUP:

Leads table
Users table with reverse reference to BotTriggers table [Related BotTriggers]
BotTriggers table with reference to Users table [UsersRef]

WORKFLOW:

User selects multiple leads and chooses an action to send an email
This grouped action updates the Leads table and the BotTriggers table which triggers a bot
This automation waits for all the rows to be added then sends an email based on the data from the rows
At this point, I need to set the SentToCarrier column (on the Users table) to true so that when the bot runs on all the other rows, it will not keep sending emails (I need only one email).
Then the bot deletes all the rows

I get an error on the "Run action on rows" step which says "Cannot get key values when Parent or ChangeImage are null." I'm guessing my expression in Reference rows is wrong. I've tried a lot of things using Filter and Select.

I also tried REF_ROWS("Users", "Related BotTriggers") which is what the expression helper has, but it says "Cannot compare List with Text in ([Related BotTriggers] = [_THISROW])"

Can you help me know what to put in the Referenced rows field on the "Run action on rows" step in the bot? The table referenced is the Users table.

Thank you!

WillowMobileSys

@jc22 wrote:

I get an error on the "Run action on rows" step which says "Cannot get key values when Parent or ChangeImage are null." I'm guessing my expression in Reference rows is wrong. I've tried a lot of things using Filter and Select.

Make sure that you are selecting the ROW KEYS. I believe this error shows when some other value is passed. Anything dealing with references ALWAYS requires the row key to be selected or passed.

@jc22 wrote:

I also tried REF_ROWS("Users", "Related BotTriggers") which is what the expression helper has, but it says "Cannot compare List with Text in ([Related BotTriggers] = [_THISROW])"

This doesn't work because "Related BotTriggers" is a list of values. This function expects just a single Row key that is then used to identify the list to be returned. Rarely would you use this in a Bot's Reference Rows.

************************************

I am not clear on your process.

@jc22 wrote:

This grouped action updates the Leads table and the BotTriggers table which triggers a bot

What is being updated in the Leads table?

@jc22 wrote:

This automation waits for all the rows to be added then sends an email based on the data from the rows

Which rows to be added where?

***********************************

How does a user determine what Leads they select?

What is being gathered and sent on the email? Is it a collection of columns from the Leads table?

Are you placing this information in the email body or are you generating a PDF to attach to the email?

************************************

I am thinking there might be a much easier and efficient way but depends on your process steps.

jc22

Thank you so much for the reply, WillowMobileSys! I was just coming to post that I got the expression to work and I saw your reply. It is working as this:

Select(Users[UserID],[_thisrow].[UserID]=[UserID])

Yeah, it was a bit of a complicated workflow, and I was trying to only post what was necessary for brevity. The user selects Leads (multi-select) from the main Leads list and then chooses "Send to Carrier." The Lead gets updated (Leads table) that the email has been sent. The email pulls most of the data from the selected Leads (columns from Leads table) with a template. I was just trying to do this in an email body for now and possibly add a CSV later. I was initially using a separate table (BotTriggers) as a way to prevent multiple emails from being triggered, but now that I got the above expression working, I am also thinking of a simpler approach. Trying to keep it all in the Leads table, but I often think I want a repository to store user-specific variables which is what I was trying to do with the Users table.

I am working on that simpler approach now and will post back if it works.

Thanks again!!

jc22

Ok, so my plan didn't work. I assumed that bots worked sequentially, so I would set the user variable once the email was sent and prevent the rest of the emails from being sent, but I still got multiple emails.

Here's the problem: I need to update the status on multiple, user-selected leads (and ideally update a log as well), but only send one email. What I can't seem to get around is that if the user selects multiple rows, it will automatically send multiple emails.

I could just add one row as a trigger for the email, but then I don't update all the selected leads as needed.

The only idea I have now is to do a 2-step process with the user:

Have the user select the rows, update and flag those rows AND
Have the user then separately send an email

The problem I have with the 2-step approach is that it seems prone to user error, like if they decide to navigate somewhere else in the middle, then I have status changed on leads but no email sent.

Any ideas?