cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TL_Arwen
Frequent Visitor

form to excel time stamp 1 column per day for 30 days

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.

jordend_0-1625585712079.png

 

jordend_1-1625585731031.pngjordend_2-1625585757207.png

 

6 REPLIES 6
BCLS776
Super User
Super User

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
Frequent Visitor

What's the best way to make that happen?


@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
Frequent Visitor

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.


@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
Frequent Visitor


@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.

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (1,924)