I use a Report to save a CSV. This is my txt template:
<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[In]>>;3;2
<<End>>
<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[Out]>>;3;2
<<End>>
This is working perfectly. But: I always get an empty last row:
How can I get rid of that empty row?
We import this CSV file in our system and itโs getting confused because of this empty row
Solved! Go to Solution.
@Bahbus Thank you very much for that hint. I found a workaround to get rid of the empty row.
<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[In]>>;3;2
<<End>>
<<Start: TOP(ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In]),
COUNT(SELECT(TimeTracking[Key] , [Date] = TODAY()-1))-1)>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[Out]>>;3;2
<<End>>
<<Start: LIST(INDEX(ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In]),
COUNT(SELECT(TimeTracking[Key] , [Date] = TODAY()-1))))>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[Out]>>;3;2<<End>>
The middle part produces all rows, except the last row.
The last part produces only the last row.
By putting the <<End>>
tag directly to the end, it will not produce a new line.
@Steve should I report this to AppSheet support so they could fix this โadding a new lineโ bug?
Try:
<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[In]>>;3;2
<<End>>
<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[Out]>>;3;2<<End>>
I.e., put the <<End>>
tag on the last data line rather than on a new line.
Thank you @Steve. With this I will not get an empty new line at the end.
But the side effect is, that all entries will be in the same row.
But I need one row per entry.
How about a newline after the <<End>>
tag?
Nothing changes.
<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[In]>>;3;2
<<End>>
<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[Out]>>;3;2
<<End>>
(Newline here)
will produce this:
<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[In]>>;3;2
<<End>>
<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[Out]>>;3;2<<End>>
(Newline here)
Will produce this:
Is it possible to have an <<END>>
tag within an IF statement without AppSheet balking?
It would be combersome but if AppSheet lets you, you could try some sort of IF statement to detect if its the last entry. Have a return then END tag while false, and no return before the END tag when true.
@Bahbus Thank you very much for that hint. I found a workaround to get rid of the empty row.
<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[In]>>;3;2
<<End>>
<<Start: TOP(ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In]),
COUNT(SELECT(TimeTracking[Key] , [Date] = TODAY()-1))-1)>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[Out]>>;3;2
<<End>>
<<Start: LIST(INDEX(ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In]),
COUNT(SELECT(TimeTracking[Key] , [Date] = TODAY()-1))))>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[Out]>>;3;2<<End>>
The middle part produces all rows, except the last row.
The last part produces only the last row.
By putting the <<End>>
tag directly to the end, it will not produce a new line.
@Steve should I report this to AppSheet support so they could fix this โadding a new lineโ bug?
Ah, yes, I used a similar trick before I learned about how the whole auto-comma added thing for JSON templates worked (I think because of you). I was going to suggest this if tried my first idea and it didnโt work.
You could, but I suspect this wouldnโt be โfixedโ.
Why do you think so?
I myself wouldnโt consider it a bug; a newline at the end of a CSV file shouldnโt be a problem to anything consuming the file.
Can you elaborate on this?
The system interpret the new line as an entry.
User | Count |
---|---|
33 | |
29 | |
29 | |
20 | |
18 |