I need to help with this report please. It is to produce a report for weekly payroll.
name: <<[name]>>
<<Start:OrderBy(Select([Related Employees][RIC]))>>
Employee Name: | <<[RIC]>><<[Employee Name]>> - <<[Trade]>> |
---|---|
Address: | <<[Address]>>, <<[City]>>, <<[Country]>> |
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.โ,
Solved! Go to Solution.
This:
Payroll Report for the period <<[Today()-7]>> to <<[Today()]>>
should be this:
Payroll Report for the period <<Today()-7>> to <<Today()>>
This:
Payroll Report for the period <<[Today()-7]>> to <<[Today()]>>
should be this:
Payroll Report for the period <<Today()-7>> to <<Today()>>
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.",
Sorry for the delayโIโve been out sick.
This:
Select([Related Employees][RIC])
is not a valid SELECT() expression.
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.
Thank you Steve, I am now error free. I do have two issues or problems based on this templateโฆ
name: <<[name]>>
<<Start:OrderBy([Related Employees][RIC], [employee name], [Status]=โActiveโ)>>
Employee Name: | <<[RIC]>><<[Employee Name]>> - <<[Trade]>> |
---|---|
Address: | <<[Address]>>, <<[City]>>, <<[Country]>> |
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]>><> |
<>
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]>>
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?
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])>>
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โ)>>
Thank you Steve, issues resolved.
User | Count |
---|---|
40 | |
32 | |
30 | |
16 | |
16 |