form to excel time stamp 1 column per day for 30 d...

TL_Arwen · ‎07-06-2021

So I need to create a flow that will write a timestamp in a column each day when someone comes in to the building. I want it to remember 30 days worth of punch-ins. I'd also like it to include the temperature from the first punch-in of the day, but it may not be entered on subsequent punches that day. I don't want it to create a new row if the person already has put it in once before.

BCLS776 · ‎07-06-2021

Can I offer three suggestions?:

You can tremendously simplify your data if you move away from the idea of a column for each day. Instead, allow the timestamp to go into one or two columns (maybe a ClockIn and ClockOut?) and then use data filter/sort/search operations to pull out the pieces you need later.
If users enter a temperature into the form, no matter their clock-in order, why not put it in the table? You can choose later if you want to use it.
Excel as a data source has some significant limitations and slow performance. A Sharepoint list is a significantly better option, and you can still export the data to Excel if you need to handle it as a spreadsheet.

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

TL_Arwen · ‎07-07-2021

What's the best way to make that happen?

BCLS776 · ‎07-07-2021

@TL_Arwen wrote:
What's the best way to make that happen?

That's a big bunch of questions wrapped into one, but start with this:

Have a good look through @WarrenBelz's blog at https://www.practicalpowerapps.com/ and really digest the entry, "Before you start...". His blog focuses on Power Apps, but the principles apply to flows too.
A Sharepoint list with a small number of columns (Title, ClockIn, ClockOut, EmployeeName, Temperature, and maybe one or two more) may be enough to fit your solution. You'll find the flow that stores data from Forms can get a lot simpler as a result.
It is OK to have separate rows (aka records) in the list for the same employee for each day of the month. You can later use queries such as Filter, Search & Lookup in an app or flow to pull up specific records you need for your purposes
As you bump into specific questions, have a search on this Community and the related Power Automate Community for answers. Most questions have been asked before, so you won't have to wait for a response.

Regards,

Bryan

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

TL_Arwen · ‎07-07-2021

Thanks for that. I am taking a read. I also only want the one line per person as this will be used for our ERT team. So in an emergency, if they need to see who's currently in the building, it needs to be a fast access kind of thing.

BCLS776 · ‎07-07-2021

@TL_Arwen wrote:
Thanks for that. I am taking a read. I also only want the one line per person as this will be used for our ERT team. So in an emergency, if they need to see who's currently in the building, it needs to be a fast access kind of thing.

It sounds like you're planning to use your data source as the user interface -- can I steer you away from that idea too?

To:

keep people using your solution,
preserve data integrity and avoid accidental edits,
and save time,

set up a simple, intuitive interface that lets your users see and understand the data in the ways that are most effective for them. Forcing them to find, open, and understand an Excel file with 30+ columns in an urgent situation seems at odds with that.

Instead, Power Apps makes this stuff possible and accessible, or everyone would still be using Excel. You can create a single screen app labelled "Who's in the Office?" that shows a list of names that is accessible with one or two taps on a tablet. The screen can show a table (gallery) summary of a simple query of the Sharepoint list that filters which people have checked into the office but not yet checked out that day.

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

TL_Arwen · ‎07-07-2021

@BCLS776 wrote:
@TL_Arwen wrote:
Thanks for that. I am taking a read. I also only want the one line per person as this will be used for our ERT team. So in an emergency, if they need to see who's currently in the building, it needs to be a fast access kind of thing.
It sounds like you're planning to use your data source as the user interface -- can I steer you away from that idea too?

To:
keep people using your solution,
preserve data integrity and avoid accidental edits,
and save time,
set up a simple, intuitive interface that lets your users see and understand the data in the ways that are most effective for them. Forcing them to find, open, and understand an Excel file with 30+ columns in an urgent situation seems at odds with that.

Instead, Power Apps makes this stuff possible and accessible, or everyone would still be using Excel. You can create a single screen app labelled "Who's in the Office?" that shows a list of names that is accessible with one or two taps on a tablet. The screen can show a table (gallery) summary of a simple query of the Sharepoint list that filters which people have checked into the office but not yet checked out that day.

Yeah, after I sent that, I thought of that. Just make a query that will take the data and tell me who's in the office and display it nicely. And also have something set up so that after 14 days (our current requirement for contact tracing) it will delete entries. That way I can just do single line for each clock in and out.