I have an app for our drivers which works beautifully in that it runs a report on the timesheet every day, however I would prefer to have it run on demand because sometimes the user might forget or not be able to sync their start and finish times and therefore will need to submit backdated records.
I can get it to work, but when I get the report, it shows all of the previous entries when I want just the most recent: I have been looking at various expressions and suchlike and it looks like something around _ROWNUMBER should work but I canโt write it correctly to make it function correctly.
The template at the moment has <<Start: SELECT(Timesheet[Date], ISNOTBLANK([Night out?])) >><<[Driver]>> to ensure that the report does not fire until the final column (Night out?) has been completed, but I donโt know if that needs to be retained or replaced?
All advice appreciated.
Normally what Iโd do here is have a DateTime column with an initial value of Now() that works as a creation timestamp. Then you could change your formula to somthing like this
<<Start:
Select(Timesheet[Date],[Timestamp]=
MAX(Select(Timesheet[Timestamp],ISNOTBLANK([Night out?])))
)
>><<[Driver]>>
Thanks Simon. By changing the Date column to DateTime that works up to a point, however adding NOW() throws out another formula which is needed to get the entries to run chronologically on the weekly report:
ORDERBY(Timesheet[Date],[Date], False)
Error message: Column Name โDate sortingโ in Schema โTimesheet_Schemaโ of Column Type โListโ has an invalid app formula โ=ORDERBY(Timesheet[Date],[Date], False)โ. ORDERBY has invalid inputs
I suppose the lazy way would be to duplicate the table, strip out the virtual column and use that for the on demand report, and keep the existing for the weekly but thatโs not as elegant as Iโd like to achieve, if possible?
Thanks again. I played with this a while ago but it didnโt work so had to get Aleksi involved to work out what was/I was doing wrong, and thatโs what we ended up with. I will have another look though.
Iโve been playing about with adding a column for timestamping (called โSubmittedโ but made invisible) and keep finding the error below:
โErrorsโ: โError: โSend daily timesheet 2โ task โSend daily timesheetโ Attachment template. Found 1 unmatched โEndโ. They are: <p dir=โltrโ class=โpt-Normal-000007โ xmlns=โXHTML namespaceโ><span lang=โen-GBโ class=โpt-DefaultParagraphFont-000020โ><<[Night out?]>><>
โ,Except, I canโt find the unmatched part! - See below:
So aside from the mystery angle bracket, does my timestamp need to be visible and if so, can it be different to the Date column?
I managed to solve the angle bracket conundrum, it was a space in the first formula. So now the bot works, however the report is blank, which is frustrating to say the least. Screengrabs below to assist with diagnosis:
Total hours column has the formula IF(ISBLANK([Finish]), โ000:00:00โ, [Finish]-[Start])
Date sorting has the formula ORDERBY(Timesheet[Date],[Date], False) (not sure if strictly necessary in this sense and maybe the source of the problem?)
Submitted column has an initial value of NOW() and Date TODAY()
Template snip:
Have you confirmed that the SELECT() expression in your <<Start>>
tag produces any results?
Hi Steve. If I knew how to check that, I wouldโฆThere are no errors reported on the audit log, nor does anything jump out at me on the automation monitor. Both report a success.
Create a virtual column in the table the entire report is for. Set the App formula expression for the virtual column to that SELECT() expression. Within Expression Assistant, use the Test feature to test the expression.
Thanks, please see below:
Remove <<Start:
and >>
from around the SELECT() expression in Expression Assistant.
Aha. Seems to work now:
Next steps?
Test it!
Iโve found that carriage returns in template formulas can cause all sorts of stupid errors. I have to create a formula, then copy it taking out any tabs & carriage returns before pasting it in to the template doc.
Very true!
Runs OK although if you go into the expression result both name and result have โ-โ and report PDF is still blank.
Do I need to simplify the template, as I havenโt touched that since bringing across the formula?
Curious why you say that given it produces no results
Your expression is bad. Youโll need to fix it so that it produces usable results.
User | Count |
---|---|
37 | |
31 | |
29 | |
22 | |
18 |