cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JayRussell
Helper I
Helper I

Excel formula in Powerapps

Hi everyone.

 

TL:DR -Building an attendance tracker / fire register app. Excel formulae and links queries

 

(I'm quite new to PowerApps but getting there slowly)

I built an electronic in/out system a few months back using excel with an add-in, a mobile phone and Flow.

It works OK but our ICT guys don't like the fact the system needs an account permanently logged in with an open Excel sheet for it to operate. They recommended PowerApps as a better alternative.

 

I can get a QR code to be read and update a table in excel.

What I can't work out is how to use (or replicate) the existing excel formula which determine whether you have just scanned in or out. Can I get PowerApps to determine this or should I just read from the Excel table?

 

My excel has 2 parts:

Scanned data in -Name (from QR code) and time stamp

In/Out list -formula driven based on the counts of scans (0=out, 1=in Odd=In Even=out)

 

The flow part copies the file to another folder and renames it with the date at the end of the day.

The second flow clears the table in the live register ready for use the next day.

 

Thanks all.

1 ACCEPTED SOLUTION

Accepted Solutions
Drrickryp
Super User
Super User

Hi @JayRussell 

Please see this article about Excel and PowerApps. if you are considering building a robust app that will work into the future and be used by more than one person. http://powerappsguide.com/blog/post/how-well-does-powerapps-cope-with-large-excel-files  the title of the article is  Excel - Reasons NOT to use Excel as a data source

View solution in original post

2 REPLIES 2
Drrickryp
Super User
Super User

Hi @JayRussell 

Please see this article about Excel and PowerApps. if you are considering building a robust app that will work into the future and be used by more than one person. http://powerappsguide.com/blog/post/how-well-does-powerapps-cope-with-large-excel-files  the title of the article is  Excel - Reasons NOT to use Excel as a data source

Hi @Drrickryp  thanks for that.

 

I'm not sure I'll collide with that data limitation in excel. We have a team of c.75 people and we'd gather a maximum of 300 rows of data in the course of one cycle (presuming the current process of archive and refresh daily continues).

 

I'm indifferent as to whether the data is stored in Excel or a sharepoint list -as long as I can archive it for storage and I'm able to do the calculations I need to to determine who is in/out and record timestamps.

 

The current model has one sheet which just 'lists' all the QR code scans (Name and timestamp)

Another sheet in the workbook does the maths to determine (by count of name) whether someone is in or out

 

If I could simplify this but maintain functionality, that would be great. -I'm very new to PowerApps so still working out the limitaions/capabilities as well as the 'how-to' part of it.

 

Jay

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,201)