Report only most recent entry

StuartSP · 10-08-2021 01:10 AM

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.

1minManager

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]>>

StuartSP

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

The Date sorting column is an invisible virtual column, specified as a List.

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?

1minManager

StuartSP

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.

StuartSP

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?

StuartSP

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:

Steve

Have you confirmed that the SELECT() expression in your <<Start>> tag produces any results?

StuartSP

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.

Steve

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.

StuartSP

Thanks, please see below:

Steve

Remove <<Start: and >> from around the SELECT() expression in Expression Assistant.

StuartSP

Aha. Seems to work now:

Next steps?

Steve

Test it!

1minManager

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.

Steve

Very true!

StuartSP

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?

Steve

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.