Solved: Excel formula in Powerapps

JayRussell · ‎07-13-2020

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.

Drrickryp · ‎07-13-2020

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

Drrickryp · ‎07-13-2020

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

JayRussell · ‎07-14-2020

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