Records from today in report

[Scarecrowbirdie]

Good afternoon. I am trying to create a summary report for all records created “today” and exclude any records before “today”. All my records are timestamped with date and time and I have this as my start expression.

<<Start: Select(Enter a Catch[Timestamp=TODAY()], TRUE)>>

When I fire the report, it gives me the following error
“Errors”: “Error: Workflow rule ‘New Report’ action ‘Email Report’ Body template. Expression ‘Select(Enter a Catch[Timestamp=TODAY()], TRUE)’ is invalid due to: Unable to find column ‘Timestamp=TODAY()’. Error: Workflow rule ‘New Report’ action ‘Email Report’ Body template. Start expression ‘Select(Enter a Catch[Timestamp=TODAY()], TRUE)’ 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. Error: Workflow rule ‘New Report’ action ‘Email Report’ Body template. Start expression ‘Select(Enter a Catch[Timestamp=TODAY()], TRUE)’ 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.”,

If I don’t have =TODAY() in it, the report fires the entire table.
Thanks

[Steve]

help.appsheet.com

SELECT()

Gather column values from row in table

[Scarecrowbirdie]

Hmmm, the select statement was already in there. I see that I had a bracket in the wrong place and moved it, and now get this error

“Errors”: “Error: Workflow rule ‘New Report’ action ‘Email Report’ Body template. Expression ‘Select(Enter a Catch[Timestamp]=”TODAY()”, TRUE)’ is invalid due to: Cannot compare List with Text in (ENTER A CATCH[Timestamp] = “TODAY()”). Error: Workflow rule ‘New Report’ action ‘Email Report’ Body template. Start expression ‘Select(Enter a Catch[Timestamp]=”TODAY()”, TRUE)’ 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. Error: Workflow rule ‘New Report’ action ‘Email Report’ Body template. Start expression ‘Select(Enter a Catch[Timestamp]=”TODAY()”, TRUE)’ 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.”,

The timestap is the Key column so it should work from what I am thinking. Not sure why it’s not.

[Steve · Accepted solution]

Your original expression:

Select(Enter a Catch[Timestamp=TODAY()], TRUE)

is invalid because AppSheet treats everything between the square brackets as a column name, but your column is not named Timestamp=TODAY().

Your revised expression:

Select(Enter a Catch[Timestamp]=”TODAY()”, TRUE)

is invalid because the first argument is an expression (Enter a Catch[Timestamp]=”TODAY()”) when it should be just a column specification (e.g., Enter a Catch[Timestamp]). Not strictly invalid, but probably not what you really want: the TODAY() function is in quotes, making it a literal Text value. You probably don’t want to compare a timestamp to the literal text.

What you probably want:

Select(Enter a Catch[Timestamp],[Timestamp]=TODAY())

Or:

FILTER("Enter a Catch", ([Timestamp] = TODAY()))

These suggested SELECT() and FILTER() expressions are equivalent, but I prefer FILTER() because it returns the row keys automatically so you don’t have to specify the key column.

If the Timestamp column is of type DateTime, use DATE([Timestamp]) instead of just [Timestamp] in the above, like this:

FILTER("Enter a Catch", (DATE([Timestamp]) = TODAY()))

[Scarecrowbirdie]

Thank you. I have been trying every combination I can think of. Think I need to brush up on my database skills.