Error in timesheet report

[Brent_Meade]

I need to help with this report please. It is to produce a report for weekly payroll.

Payroll Report for the period <<[Today()-7]>> to <<[Today()]>>

Company

name: <<[name]>>

<<Start:OrderBy(Select([Related Employees][RIC]))>>

Employees

Employee Name:	<<[RIC]>><<[Employee Name]>> - <<[Trade]>>
Address:	<<[Address]>>, <<[City]>>, <<[Country]>>

Timesheet

RIC	Date	Time In	Verified In	Time Out	Verified Out	Project	GPS	Total Hours	Total Wages
<<Start:OrderBy(Select([Related Time][RIC], AND(([Date] >= (TODAY() - 7)), ([Date] <= TODAY()))))>><<[RIC]>>	<<[Date]>>	<<[Time In]>>	<<[Verified In Time]>>	<<[Time Out]>>	<<[Verified Out Time]>>	<<[Project.Project]>>	<<[GPS]>>	<<[Total Hours]>>	<<[Total Wages]>><>

<>

This is the error that I am getting below.

“Errors”: “Error: Report ‘Employee Payroll Report’ action ‘Generate List’ Attachment template. Expression ‘[Today()-7]’ is invalid due to: Unable to find column ‘Today()-7’. Error: Report ‘Employee Payroll Report’ action ‘Generate List’ Attachment template. Expression ‘[Today()]’ is invalid due to: Unable to find column ‘Today()’, did you mean ‘id’?. Error: Report ‘Employee Payroll Report’ action ‘Generate List’ Attachment template. Expression ‘OrderBy(Select([Related Employees][RIC], [Status]= “Active”))’ is invalid due to: ORDERBY has invalid inputs. Error: Report ‘Employee Payroll Report’ action ‘Generate List’ Attachment template. Start expression ‘OrderBy(Select([Related Employees][RIC], [Status]= “Active”))’ 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: Report ‘Employee Payroll Report’ action ‘Generate List’ Body template. Expression ‘[Today()-7]’ is invalid due to: Unable to find column ‘Today()-7’. Error: Report ‘Employee Payroll Report’ action ‘Generate List’ Body template. Expression ‘[Today()]’ is invalid due to: Unable to find column ‘Today()’, did you mean ‘id’?. Error: Report ‘Employee Payroll Report’ action ‘Generate List’ Body template. Expression ‘OrderBy(Select([Related Employees][RIC], [Status]= “Active”))’ is invalid due to: ORDERBY has invalid inputs. Error: Report ‘Employee Payroll Report’ action ‘Generate List’ Body template. Start expression ‘OrderBy(Select([Related Employees][RIC], [Status]= “Active”))’ 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.”,

[Steve · Accepted solution]

This:

Payroll Report for the period <<[Today()-7]>> to <<[Today()]>>

should be this:

Payroll Report for the period <<Today()-7>> to <<Today()>>

[Brent_Meade]

Thanks Steve, I have moved from 8 errors to two errors. I deleted the attachment for now.

“Errors”: "Error: Report ‘Employee Payroll Report’ action ‘Generate List’ Body template. Expression ‘OrderBy(Select([Related Employees][RIC]))’ is invalid due to: SELECT has invalid inputs.

Error: Report ‘Employee Payroll Report’ action ‘Generate List’ Body template. Start expression ‘OrderBy(Select([Related Employees][RIC]))’ 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.",

[Steve]

Sorry for the delay–I’ve been out sick.

This:

Select([Related Employees][RIC])

is not a valid SELECT() expression.

help.appsheet.com

SELECT()

Gather column values from row in table or slice

You probably don’t need SELECT() at all if the RIC column is of type Ref:

OrderBy([Related Employees][RIC])

Note, too, your ORDERBY() expression dose not indicate which column to order by.

help.appsheet.com

ORDERBY()

Sort row references

[Brent_Meade]

Thank you Steve, I am now error free. I do have two issues or problems based on this template…

Payroll Report for the period <<Today()-7>> to <<Today()>>

Company

name: <<[name]>>

<<Start:OrderBy([Related Employees][RIC], [employee name], [Status]=“Active”)>>

Employees

Employee Name:	<<[RIC]>><<[Employee Name]>> - <<[Trade]>>
Address:	<<[Address]>>, <<[City]>>, <<[Country]>>

Timesheet

RIC	Date	Time In	Verified In	Time Out	Verified Out	Project	GPS	Total Hours	Total Wages
<<Start:OrderBy([Related Timesheets][Timestamp], ([Date] > (NOW() - 7)))>><<[RIC]>>	<<[Date]>>	<<[Time In]>>	<<[Verified In Time]>>	<<[Time Out]>>	<<[Verified Out Time]>>	<<[Project].[Project]>>	<<[GPS]>>	<<[Total Hours]>>	<<[Total Wages]>><>

<>

1. The data is returning all the data in the table and not pay records for the last week. <<Start:OrderBy([Related Timesheets][Timestamp], ( [Date] > (NOW() - 7) ))>><<[RIC]>>

2. I am getting data returned even if no time was recorded for the user for the period, how do i exclude them from the report?

[Steve]

Brent_Meade:

The data is returning all the data in the table and not pay records for the last week. <<Start:OrderBy([Related Timesheets][Timestamp], ( [Date] > (NOW() - 7) ))>><<[RIC]>>

ORDERBY() merely orders the rows, it doesn’t omit any. What you probably want instead is:

<<Start:OrderBy(Select([Related Timesheets][Timestamp], ([Date] > (NOW() - 7))), [Timetamp])>>

Brent_Meade:

I am getting data returned even if no time was recorded for the user for the period, how do i exclude them from the report?

The first <<Start>> expression would need to use SELECT() as well to include only those employees with recorded time. Perhaps something like this:

<<Start:OrderBy(Select([Related Employees][RIC], IsNotBlank(Select([Related Timesheets][Timestamp], ([Date] > (NOW() - 7)))))), [employee name], [Status]=“Active”)>>

[Brent_Meade]

Thank you Steve, issues resolved.