Re: I keep getting errors about my report template

Tom_Cat · 01-22-2020 10:14 AM

Hi,
I’m having issues getting the hang of reports. I am not sure if the report “template” example that is listed here (https://help.appsheet.com/en/articles/961724-reports) is maybe out of date or something, I have the following report template table:
|Serial Number:| <<Start:FILTER(TableName, TODAY() - [Date of Receiving] < 6>><<[Serial No.]>>|
|RMA Number:| <<[RMA No.]>>|
|Date Added:| <<[Date of Receiving]>>|
|Added by:| <<[User]>><>|

Now this is an edited version since before I had <<[TableName].[Column Name]>> however that gave me errors so I thought maybe that was the incorrect syntax and tried without table name, however I still get the same error:

Expression is invlaid due to: error in expression, unable to find column ‘TableName’

Steve

Does you app have a table named TableName?

Tom_Cat

No, I just replaced it with table name, however I am using the correct table name. I am not sure however if I need to enclose it in brackets or quotation marks

Steve

Please post screenshots of your template and of the error message.

Tom_Cat

Error:
Properties:
{
“AppId”: “e6c0ac06-d33a-4b61-a3b5-244dfbd6445c”,
“AppTemplateVersion”: “1.000175”,
“RuleName”: “Weekly Report: PERs”,
“EventType”: “Scheduled”,
“InvokedBy”: “EditorRunButton”,
“Server”: “produ991100014T”,
“IgnoreSecurityFilters”: false,
“EventMatch”: “Workflow event successfully matched”,
“Condition”: “”,
“MatchesCondition”: “True”,
“ActionNotPerformed”: “4 errors present.”,
“ActionResults”: “Created 1 ActionResults”,
“Action Type”: “Email”,
“Action Name”: “Action 1”,
“Errors”: “Error: Report ‘Weekly Report: PERs’ action ‘Action 1’ Attachment template. Expression ‘[Tomcat_PER_TimeData].[RMA No.]’ is invalid due to: Error in expression ‘[Tomcat_PER_TimeData].[RMA No.]’ : Unable to find column ‘Tomcat_PER_TimeData’. Error: Report ‘Weekly Report: PERs’ action ‘Action 1’ Attachment template. Expression ‘[Tomcat_PER_TimeData].[Date of Receiving]’ is invalid due to: Error in expression ‘[Tomcat_PER_TimeData].[Date of Receiving]’ : Unable to find column ‘Tomcat_PER_TimeData’. Error: Report ‘Weekly Report: PERs’ action ‘Action 1’ Attachment template. Expression ‘FILTER(Tomcat_PER_TimeData, TODAY() - [Date of Receiving] < 6’ is invalid due to: Expression ‘FILTER(Tomcat_PER_TimeData, TODAY() - [Date of Receiving] < 6’ could not be parsed due to exception: Number of opened and closed parentheses does not match… Error: Report ‘Weekly Report: PERs’ action ‘Action 1’ Attachment template. Start expression ‘FILTER(Tomcat_PER_TimeData, TODAY() - [Date of Receiving] < 6’ 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.”,
“EmailTo”: “david_boydston@dkgenr.com ,dk.tomcatper@gmail.com”,
“EmailCC”: “”,
“EmailBCC”: “”,
“EmailFromDisplay”: “dk.tomcatper@gmail.com”,
“EmailReplyTo”: “noreply@appsheet.com”,
“EmailPreHeader”: “Scheduled rule ‘Weekly Report: PERs’ for application ‘Tomcat_PER’ invoked at 1/22/2020 10:03:55 AM”,
“EmailSubaccount”: null,
“EmailSubject”: “‘Weekly Report: PERs’. AppSheet Reporting.”,
“EmailTitle”: “Scheduled rule ‘Weekly Report: PERs’ for application ‘Tomcat_PER’ invoked at 1/22/2020 10:03:55 AM”,
“EmailAttachment”: “Name: ‘ScheduledReport.pdf’ Archive: AttachAndDoNotArchive FileName: ‘’ MIME Type: ‘application/pdf’ PageOrientation: ‘Portrait’ PageSize: A4 AttachmentByteLength: 0 AttachmentTextLength: 0 PDFCount: 1”,
“EmailOtherAttachments”: “”,
“AppTemplateName”: “Tomcat_PER-1198592”,
“Operation”: “Workflow action”,
“Result”: “Failure”
}

(NOTE: I fixed the missing parenthesis on the FILTER statement, however it tells me this does not generate a list and that Expression ‘FILTER(Tomcat_PER_TimeData, TODAY() - [Date of Receiving] < 6)’ is invalid due to: Arithmetic expression ‘((TODAY()-[Date of Receiving]) < 6)’ does not have valid input types.

LeventK

Tom_Cat:

“ActionNotPerformed”: “4 errors present.”,
.
.
.
“Errors”: “Error: Report ‘Weekly Report: PERs’ action ‘Action 1’ Attachment template. Expression ‘[Tomcat_PER_TimeData].[RMA No.]’ is invalid due to: Error in expression ‘[Tomcat_PER_TimeData].[RMA No.]’ : Unable to find column ‘Tomcat_PER_TimeData’. Error: Report ‘Weekly Report: PERs’ action ‘Action 1’ Attachment template. Expression ‘[Tomcat_PER_TimeData].[Date of Receiving]’ is invalid due to: Error in expression ‘[Tomcat_PER_TimeData].[Date of Receiving]’ : Unable to find column ‘Tomcat_PER_TimeData’. Error: Report ‘Weekly Report: PERs’ action ‘Action 1’ Attachment template. Expression ‘FILTER(Tomcat_PER_TimeData, TODAY() - [Date of Receiving] < 6’ is invalid due to: Expression ‘FILTER(Tomcat_PER_TimeData, TODAY() - [Date of Receiving] < 6’ could not be parsed due to exception: Number of opened and closed parentheses does not match… Error: Report ‘Weekly Report: PERs’ action ‘Action 1’ Attachment template. Start expression ‘FILTER(Tomcat_PER_TimeData, TODAY() - [Date of Receiving] < 6’ 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.”,

@Tom_Cat
Let’s breakdown your error message…

Error#1

It seems that you have a de-ref expression [Tomcat_PER_TimeData].[RMA No.] where AppSheet cannot find that [Tomcat_PER_TimeData] column (which is the ref column I believe)

Error#2

It seems that you have a de-ref expression [Tomcat_PER_TimeData].[Date of Receiving] where AppSheet cannot find that [Tomcat_PER_TimeData] column (which is the ref column I believe)

Error#3

You have a missing parentheses in your FILTER expression. Notice the parentheses at the end:

FILTER(
	Tomcat_PER_TimeData,
	TODAY() - [Date of Receiving] < 6
)

Hence; your Start expression shall be:
<<Start: FILTER(Tomcat_PER_TimeData,TODAY() - [Date of Receiving] < 6)>><<[Serial No.]>>

Error#4

Related with Error#3 above, the expression cannot create a list of key values

Can you post a screenshot of your Table Schema for Tomcat_PER_TimeData table?

Tom_Cat

[Tomcat_PER_TimeData] is a TABLE NAME not a column.
I already added the parenthesis in my EDIT above and it still gives me an error that that expression is invalid

LeventK

What is the column type for [Date of Receiving]?

Tom_Cat

[Date of Receiving] Column type is DATE, and actually it’s initial value is TODAY() (for when it is entered)

LeventK

DateType - DateType produces a result of Duration type but you are evaluating it with a number. Try with this:

FILTER(
	Tomcat_PER_TimeData,
	TODAY() - [Date of Receiving] < "6.00:00:00"
)

Tom_Cat

Hi LeventK, that seemed to fix one of the many errors I’m getting. Thank you for your help.
The only confusing part is why it cannot find the columns I’m asking it to look for.
Table Name: Tomcat_PER_TimeData
Columns I want to display: Serial No. | RMA No. | Date of Receiving | User |

<<Start:FILTER(Tomcat_PER_TimeData, (TODAY() - [Date of Receiving] < “144:00:00”))>><<[Tomcat_PER_TimeData].[Serial No.]>>
putting that syntax of <<[Table Name].[Column Name]>> Doesnt work.
I tried just <<[Column Name]>> but that doesnt work either

LeventK

@Tom_Cat
What is your table schema/structure that you are trying to generate the workflow template? It’s hard to say anything without knowing that.

Tom_Cat

I dont understand your question completely, The table Tomcat_PER_TimeData is from a google spread sheet that contains various columns. There is a keycolumn “TransactionID” wich has the =UNIQUEID() formula. After that there’s a bunch of columns with data in various types (text, enum & date mostly). And this is not a workflow issue, or not that I know of. I am trying to get a report template made not a workflow

Steve

If Tomcat_PER_TimeData is a table and not a column the this is invalid:

<<[Tomcat_PER_TimeData].[Serial No.]>>

If this does’t work:

<<[Serial No.]>>

then there is no column named Serial No. in the Tomcat_PER_TimeData table.

Tom_Cat

Thank you Steve, It seems once the FILTER() issue was resolved it resolved all other issues with the syntax you suggested. I had tried that synax before but I guess the failed FILTER() statement made the other ones fail as well.

Steve

That’s often how it works and why troubleshooting should always start with resolving the first error in a list of errors. I’m glad @LeventK and I could help.