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.
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
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:
DATE(TODAY())
TODAY()
),[OBJECTID]>400
@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.
IF([TIMESTAMP] = TODAY(), SELECT(ACTIVITY[USER], ([_THISROW].[COUNT] = 400) , true))
Please correct me if I am wrong
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
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!!
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.
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:
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โฆ
Show the full expression that youโre using.
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
What are the results when just testing the COUNT(โฆ) without the inequality?
Pardon my ignorance, but how do I get a โyes/noโ result when I take out the โ>โ component? Or am I misunderstanding?
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.
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |