Report Question - No New Data

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:

Slice

@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.

1 Like

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:

  1. How can I edit the formula in the template body for entire table to not send an email when table is blank

Or

  1. Is there a way to use the each row option and only receive one email that has all records past due assigned to them rather than an email for each row?

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:

image

Therefore, I assume, I have to figure out how to do everything in the Body Template which reads the following:

Past Due (Story)

<<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.

image

1 Like

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:

  1. Is KEY the name of the key column for the Past Due table?

  2. 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)

1 Like

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

image

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:

  1. I Selected my “Past Due (Slice)” table and chose ForEntireRow.

  2. In the “If this is True” I did the following:

  3. I put my email in

  4. Then, the body of template looks like this:
    image

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.

2 Likes

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