Report Question - No New Data

Terry1
Participant II

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:

2X_9_903d079b67c37650cc11a53a7ccae8c73ab1ea38.jpeg

0 16 1,015
16 REPLIES 16

LeventK
Participant V

@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

Terry1
Participant II

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:

2X_5_52b4a7973c5b346f071530d591a05c248daa0ed1.png

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.

2X_c_c6c7bf8b263d3fc2fab3dc5c9dcf7e628f538cd4.png

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?

jandrostory81
Participant III

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

jandrostory81
Participant III

When I do that I receive this error

2X_3_32829d75f8ca9f009582b56cd941ae32e9e08ebb.png

I thought you had some other condition there so I recommended the AND. Remove the AND and just put the COUNT(โ€ฆ)>0 part

jandrostory81
Participant III

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:
    2X_5_5c19bcf9d8123beb78a0be363d9aabd2c5a88fda.png

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.

Top Labels in this Space