Having trouble with Google Templates Report

Work_Goscinski · 11-18-2019 12:21 PM

When I run a report without a template, I get a report emailed to me. (Not yet using a paid version)
However, when I create a report template (using the Create button at the end of the input box), I can see and view the template. But, clicking run does zilch, zero, nada.
So, referencing a template doesn’t produce an email.
Erase the reference to a template and the email sends.

Ultimately, I want to divide the report up into a table. But I am not even able to get that far yet. What data would you need to see to help?

Thanks in advance.

TyAlevizos

Email body template or attachment template???

Work_Goscinski

email body template

WillowMobileSys

The first thing I would do is check to see if an error is being reported from your Workflow. You can see this by tapping the “Log” button found at the tope of any of your Workflows. See pic below.

Look for the the name of your Workflow in the “On” column. Red text/icon means an error. Click on the red binoculars to see the error report. (I don’t have a good example to show right at the moment or I’d include a pic of this too)

Let us know if you see any error and what the message might be.

Work_Goscinski

The log showed an error. It says a Column Name doesn’t exist. However, it appears on my Google Sheet and in the table containing all data. So, I am confused.
I deleted that particular instance and the report runs, minus that column.

Also, I am seeing ALL data from the beginning. I only want to show work done today.

WillowMobileSys

Double check that there wasn’t some spelling or syntax error. Remember that you must surround the column names in the template with double angle brackets and the normal square bracket inside of that. I have seen sometimes the square brackets are left out.

Work_Goscinski

I originally clicked the “Create” button for the template. I cut and pasted within the document. I just added some tables. I thought that would be right for syntax etc.

Guess not.

TyAlevizos

As coincidence would have it, I was working on some printing/merge/google doc examples, and have published an app here:
https://www.appsheet.com/samples/Sample-App?appGuidString=52a2f38b-8e43-46d1-94e5-f5a62bd9d0e6

If you copy it you should get a few working examples up and running to reference. Hope this helps!

Work_Goscinski

It apparently was syntax. I had a double-space between the words “Quantity” and “Sorted”. Good catch, my peers.

I am still trying to get the email to print only for a particular day. I don’t want everything from the beginning to print. Since it is a scrap report, I only want that which was generated the previous day. Today=Now() as start expression doesn’t seem to do anything. Maybe Today=Now()-1?

Lynn

Hi @Work_Goscinski
Do you have a Date column? Maybe try something like [Date]>=TODAY()-1

AlexM

NOW() is Date and Time
TODAY() is Date

Work_Goscinski

Do I write this expression in the “If condition is true” expression area? Because I do have a column “Date” and the expression is not working.

AlexM

Yes
[Date]=TODAY()-1

without >. not sure if that was typo or you intentionally wrote it

Work_Goscinski

Here is proof that Date exists because it populates in the template.

And here it is in the table

AlexM

I didn’t say it doesn’t exist.

AlexM

What’s the error?

Which column it can’t find?

I’m on my phone and can’t see properly.

Work_Goscinski

I know you didn’t say it didn’t exist. That is the error message.

Error message: Unable to find column ‘Date’

AlexM

Close the expression window and take a printscreen of the report page.

Work_Goscinski

I am not exactly sure what you are asking.

Steve

You’re confusing the attempts to help you by switching back and forth between template expressions and workflow conditions without being clear, and your screenshots are of very poor quality, making them very difficult to see.

The reason you’re being told the Date column doesn’t exist is related to the other warning message displayed that you’ve chosen to ignore: This formula is not evaluated in the context of a row, so column values are unavailable.

Work_Goscinski

I appreciate feedback. However, I haven’t “Chosen to ignore” anything. I am not in complete understanding of how expressions work, thus why I am seeking help. I will work on better screenshots. They “Seem” ok on my end as I paste them.

I am switching back and forth, as you say, to show that “Date” exists in my data, it translates into my template, as well. So I am confused by the error…and as you pointed out, “This formula is not evaluated in the context of a row, so column values are unavailable.” When I write something goofy like: Today()=Today()-1, I get a response to that via the report. I am shaking my head at that. I am frustrated. I am trying not to be defensive. But, I will admit…it’s hard not to be.

Steve

When the workflow expression is evaluated, it’s being evaluated without being attached to a specific row in your table. It’s looking at the worksheet as a whole rather than at just one row of that worksheet. So when you reference a column value like [Date], AppSheet doesn’t know which row’s Date column value you want. That’s what “This formula is not evaluated in the context of a row, so column values are unavailable” means.

The goal in the If this is true… condition is to decide whether the report should be run at all. For instance, to avoid sending an empty report if there are no new rows. But again, the condition is looking at the entire table, not a specific row. If you want to consider a specific row, you have write that into your expression.

So what are you trying to do with the report’s If this is true… condition?

Work_Goscinski

Thank you, Steve. I apologize for my defensiveness earlier.

I am basically trying to get an emailed report from Yesterday and only yesterday. So what I THINK I want from a row is this: “Look at the date. Is it today minus 1? Good. Send that email.”

As it runs now, I get EVERY entry from the beginning of my app. I basically only want a daily digest.

Steve

I would interpret that as: you want a report to include entries only from yesterday. Is that correct?

Work_Goscinski

That is correct.

Steve

Okay. In the report’s If this is true… condition, I suggest you set it to TRUE, which will always run the report at the scheduled time. We can go back later and find a way to avoid sending empty reports.

The next step will be to configure the report template to only include yesterday’s entries.

Work_Goscinski

To be clear, I should leave the “If this is true” section blank. Let the report generate as it is supposed to.

And configure the document template itself to calculate “yesterday.”

Am I reading you correctly?

Steve

Yep.

Work_Goscinski

OK. Could you point me in the right direction where to research putting expressions/formulas/whatever the right term is into a template as to whether it should run or not?

Steve

The goal in the template is not to decide whether the report should run or not, but rather which rows should be included in the report. The rows included in the report are determined by the <<Start>> tag. It appears yous is:

<<Start: Select(AllDataSets([Timestamp], TRUE)>>

Your SELECT() expression selects all rows in the table for inclusion. Consider replacing TRUE with an expression that is only TRUE when the row should be included. For instance:

<<Start: Select(AllDataSets([Timestamp], ([Date] = (TODAY() - 1)))>>

Work_Goscinski

That is getting me closer. A good stepping off point for now.

Work_Goscinski

Is it possible to have 2 start expressions? If the expression you wrote [quote=“Steve, post:30, topic:16345”]
<<Start: Select(AllDataSets([Timestamp], ([Date] = (TODAY() - 1)))>>
[/quote] is true, AND some other start expression “Such a row”=“Automotive”, then print it?

Lynn

Hi @Work_Goscinski You can include AND in your expression something like

<Start: Select(AllDataSets([Timestamp], AND([Date] = TODAY() - 1,[Location]=“Automotive”)))>>

@Steve will set me straight if Ive got this wrong.

Work_Goscinski

This expression is working…sort of. <<Start: Select(AllDataSets[Timestamp], [Date]=TODAY()-1)>>
Below is a screenshot of the workflow run.

However, I have noticed a few things:

For some reason, the same table is generated three times.
The only records displayed are for MY email name. Any other data is ignored by the template.
There are blank rows present. There is data in those rows that pertain other things. For instance, the screenshot is the AUTOMOTIVE scrap. The line above that looks blank, in the data set has data referring to something other than automotive.

Is this a better screenshot?

Here is a link to the email template https://docs.google.com/document/d/1q89irtIb-q-aAptJZWzGZBJzUsZ6wJ6bFAic25cOZd0/edit?usp=sharing

Steve

Much better! Thank you!

Steve

The <<Start>> and <<End>> tags should (I believe) occur entirely within the Doc table row, not outside the table. In generating the report, AppSheet will repeat everything between <<Start>> and <<End>> for each app data row the SELECT() expression returns.

Work_Goscinski

Does that mean, if there are 3 entries of data, AppSheet will repeat it 3x?

Steve

Yes, once for each entry.

Work_Goscinski

Is there a way to prevent this? That’s a lot of repetition. LOL

Steve

Prevent what? You don’t want each row displayed?