I have created two weekly reports for a driver. The reports send an email to show a list of subscriptions that have either Started or Stopped. The report works great but this week there were no Stopped subscriptions and the driver was still emailed the report which was blank aside from some common text from the template. How do I keep the report from firing if there is no new data to send? Is this done inside the Report itself inside the โIF THIS IS TRUEโฆโ section? If so, how would I write the syntax for the expression? Here is the slice for one of the reports that Iโve set up:
@Terry
You can use a COUNT(โฆ) expression similar to this. Please adopt necessary changes as per your table and column names.
COUNT(SELECT(StopSubscriptionTable[KeyColumn],[Driver]="Jackie")) > 0
Basically, you need to count the number of the records as per condition and trigger the report/workflow if the count is greater than zero.
Thanks for that. Been looking for ages. Now people wonโt get all the blank emails.
@Steve It would be useful in the documentation if it gave an example for a condition.
https://help.appsheet.com/en/articles/961724-reports as I donโt think itโs very clear what you would need to do.
Thanks
Thank you for the reply. Iโm assuming the COUNT expression will go into the body of the email of the report. Or should it go into the slice tied to the report? Iโve tried both scenarios without success. If it goes into the body of the email, is it possible to combined the COUNT expression with ORDERBY, which Iโm currently using to organize the report by route number? Hereโs what Iโm currently using:
@Terry
COUNT() expression will be your conditional rule for that workflow/report. Your workflow will be triggered if the COUNT(โฆ) expression evaluates to TRUE.
So glad I found this. Iโve been having trouble with this. My issue is that I have entiretable chosen and then working through a slice. The formula above wonโt work because it says I canโt put anything referencing columns in the conditional formula.
If I go with an send from each row I end up getting 10 emails for 10 different tasks when i want 1 email from with all 10 tasks inside.
How can I fit the formula you mentioned above into the start formula below
<<Start: Select(Past Due[KEY], CONTAINS([ASSIGNEE],โStoryโ))>>
The formula above works in my email body template but it still returns a blank email to my team when there is nothing in the table assigned to them. My question is twofold:
Or
Your help is very appreciated as this is my last step to finishing this app but I am stuck!
What is the exact text of the error message?
When I choose to create a report: โForEntireTableโ and then try to type a conditional formula into the โIf this is trueโ part, it says:
Therefore, I assume, I have to figure out how to do everything in the Body Template which reads the following:
<<Start: Select(Past Due[KEY], CONTAINS([ASSIGNEE],โStoryโ))>>
Task: <<[Task]>>
Date: <<[Date]>>
Assignee: <<[Assignee]>>
Category/Event: <<[Category/Event]>>
Status: <<[Status]>>
Comments: <<[Comments]>>
ID: <<[ID]>>
Google Document: <<[Google Document]>>
<<_ROW_WEB_LINK>> TO GO TO TASK IN APP
<>
This works perfectly except that I receive a blank email like this, when there is nothing in the Past Due Table Slice.
That the email is blank indicates to me that this expression:
Select(Past Due[KEY], CONTAINS([ASSIGNEE],โStoryโ))
does not produce any results, which leads to the following questions:
Is KEY the name of the key column for the Past Due table?
Do any values in the ASSIGNEE column of the Past Due table contain the literal text, Story
?
Correct, the KEY is the name of the Key Column and many values are assigned to Story.
I have tested and confirmed when there are 5 tasks in the Past Due (2 Assigned to โStoryโ, 1 Assigned to Schooley, 1 assigned to Ball and 1 assigned to Adams) that the email will contain only the 2 assigned to me. I have also confirmed that when I create a separate Report (kind of a pain in the butt) for each person in my team they do receive only the task assigned to them. @Aleksi mentioned I should do a count formula but I have no idea how to do this. Where do I put the count formula? Etc, etc.?
As I said, if there is a better way to handle this I am all ears. I had a report that looked like this:
I also had a report that was set for โEachRowInTheTableโ and it looked like this:
The Email body template was the same as above without the Start Expression. This yielded an email for each task. When I setup a Start expression in this particular one, the tasks were combined into 1 email, EXCEPT, that it would still send an email for each row with the combined SELECT data.
Unfortunately, my experience with reports is just about zero, so I feel out of my comfort zone trying to assist further. Perhaps @LeventK can chime in again?
You can put this in the IF THIS IS TRUEโฆ
AND(<<YOUR CURRENT FORMULA>>, COUNT(Select(Past Due[KEY], CONTAINS([ASSIGNEE],โStoryโ))>0)
Thank you for your reply. Here is what I typed in for you to ensure I did it correctly:
<<Start: AND(Select(Past Due[KEY], CONTAINS([ASSIGNEE],โStoryโ)), COUNT(SELECT(PAST DUE[KEY],CONTAINS([ASSIGNEE], โSTORYโ))>0))>>
Here is what the error I received says in the log:
Error: Workflow rule โPast Due (Story)โ action โAction 1โ Body template. Expression โAND(Select(Past Due[KEY], CONTAINS([ASSIGNEE],โStoryโ)), COUNT(SELECT(PAST DUE[KEY],CONTAINS([ASSIGNEE], โSTORYโ))>0))โ is invalid due to: Cannot compare List with Number in (SELECT(PAST DUE[KEY],CONTAINS([Assignee],โSTORYโ)) > 0).
Error: Workflow rule โPast Due (Story)โ action โAction 1โ Body template. Start expression โAND(Select(Past Due[KEY], CONTAINS([ASSIGNEE],โStoryโ)), COUNT(SELECT(PAST DUE[KEY],CONTAINS([ASSIGNEE], โSTORYโ))>0))โ should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the โKeyโ column of the referenced table.
You shouldnโt put that in the Start expression but ONLY in the IF THIS IS TRUEโฆ section
When I do that I receive this error
I thought you had some other condition there so I recommended the AND. Remove the AND and just put the COUNT(โฆ)>0 part
After almost 2 weeks of slaving over this problem for 4-8 hours ever night until 5 am, I finally figured it out, with the help of @Bellave_Jayaram. What you mentioned wasnโt the exact answer, but it lead me in the right direction. I was under the impression that I couldnโt put columns at all in the โIf this is trueโ Condition. Hereโs how I did it:
I Selected my โPast Due (Slice)โ table and chose ForEntireRow.
In the โIf this is Trueโ I did the following:
I put my email in
Then, the body of template looks like this:
I had no Idea I had to put the count condition in the โIf this is Trueโ and also in the body of the template as well.
I posted these hoping to help someone else not have a grueling two weeks trying to figure this same issue out.
Thank to all those who responded @Bellave_Jayaram, @Steve. I am beyond thrilled.
User | Count |
---|---|
33 | |
29 | |
29 | |
20 | |
18 |