Bot Question - Count of Records Created By Specifi...

Ryan_Wagner · 07-30-2021 01:56 PM

Hi,

I’m trying to use a bot to send a notification anytime a users count of field inspections gets over a certain number for the day.

Here’s what my table looks like:

I’ve tried creating a condition expression that can capture the specific user name (technician) and the count of any one of the columns (doesn’t matter which one because I clear that table out daily) but I can’t seem to figure it out. Again, I’m hoping the lack of timestamp, etc. doesn’t matter, and that a COUNT will work because I clear the table every day. I’ve been trying something along the lines of:

AND([Technician] = “rwagner@fightthebite.net”, (COUNT[OS_ASSET_ID] > 400))

Can anyone lend me a hand?

Thanks.

khuslid

Hi,
You need to count a list, not just a column. So you need to include the table.
You can look here: COUNT() | AppSheet Help Center

Ryan_Wagner

Sorry to revive this old thread. I finally got back to trying it again. Am I close with this expression? The table is called “Activity,” and the columns in the formula are right…Apparently I am using FILTER incorrectly:

Steve

Marc_Dillon

As the error says, there is only 1 parameter in your FILTER() expression. What are the
DATE(TODAY())
(which btw, it exactly the same as just TODAY() ),
and
[OBJECTID]>400
supposed to be doing here?

Ryan_Wagner

@Marc_Dillon I guess I am confused at the jargon, though I am slowly picking it up…I was trying to get a count of rows added by a user on a given day, and if it exceeded 400, for that to trigger a notification.

So the table is called “Activity,” and there is a timestamp column that I figured I could use to establish the “today” part…The “OBJECTID” is the key column, for what it’s worth.

I’m sure I made a mess of the expression, but I’m not as savvy with them as many are here. I did previously read the help articles, however.

Human_Resources

IF([TIMESTAMP] = TODAY(), SELECT(ACTIVITY[USER], ([_THISROW].[COUNT] = 400) , true))

Please correct me if I am wrong

Marc_Dillon

I can understand how not knowing the terms can make an error message impossible to read. I’ll explain just a little bit for you, hope it will help.

When you have an expression, like:

FILTER( tableName , condition )

the “parameters” are the things inside of the parentheses, and separated by commas.
*(well, it’s a bit more than that, but since Appsheet’s error message doesn’t even use the correct term, I might as well not even bring it up…)

The “tableName” portion should be pretty obvious, it is the name of the Table that you’re wanting to filter.

Then we have the “condition”. A “condition” is anything that returns a Yes or No value (or TRUE/FALSE). In the case of FILTER(), the “condition” expression is evaluated on every record in the Table individually, if it returns TRUE, then that record is included in the output of FILTER().

Any usage of FILTER must include both parameters. You only included one parameter in your usage, so that’s what the error message is telling you.

The following general structure will accomplish that:

COUNT( FILTER( 
  tableName ,
  AND(
    [date-col] = xxx ,
    [user-col] = yyy
  )
) ) > 400

Human_Resources

Oh He was looking to count the rows. You’re code gave me inspiration to construct a code I have been trying to figure out for some days now. Thanks!!

Ryan_Wagner

Thank you for the thorough explanation!

I am not one to skip reading the help articles, like I said, but they aren’t always worded as conversationally as I would personally need.

The expression you gave me doesn’t give me any errors like mine was, but the automation still isn’t sending a notification. It must be something I did wrong in setting up the bot.

Ryan_Wagner

At the risk of dragging this out too long, I wanted to give the concept one more shot.

I can’t seem to figure out why the notification is not firing. When I test the bot, the event conditions show as “false.” Is there some other criterion that isn’t being met by the expression you (very helpfully) put together?

The bot:

I am sure there is another fundamental piece of this that I’m misunderstanding…

Thanks for bearing with me.

The table I’m using for the row count is called “Activity,” as shown. Just trying to get a notification sent when new rows added by a specific user (me, for testing purposes) exceeds 400 on a given day “TODAY().” I toned it down to >10 so I could test it. Still nothing…

Marc_Dillon

Show the full expression that you’re using.

Ryan_Wagner

Thanks for following up with me. I used what you shared above after adding my column names…

COUNT( FILTER(
Activity,
AND(
[Last Action]=TODAY(),
[Technician] = “rwagner@fightthebite.net”
)
) ) > 400

Marc_Dillon

What are the results when just testing the COUNT(…) without the inequality?

Ryan_Wagner

Pardon my ignorance, but how do I get a “yes/no” result when I take out the “>” component? Or am I misunderstanding?

Marc_Dillon

You don’t. You put the expression elsewhere, like a random new VC. I just want to see if the count is outputting a real result.

Bot Question - Count of Records Created By Specific Users