Mobile App to Count Inventory with Excel and Powerapps

By Ian Ray posted 07-12-2017 02:12 PM



Counting inventory is often performed the same way it was before computers existed: using paper and pen. Many companies using Dynamics NAV take inventory on paper and transfer the count sheets back into NAV by hand.

Some companies use Excel spreadsheets so that inventory counts can be input to a spreadsheet and later copied and pasted to a Physical Inventory Journal in NAV. Excel can be a great time saver in the case of inventory counts.

With PowerApps, we can take the Excel spreadsheet a step further, creating a mobile app from an Excel sheet saved to OneDrive.

To get started, you will need a subscription or trial of PowerApps and PowerApps installed on a mobile device. To get started with Powerapps, use the Powerapps web page.

Creating Excel Workbook

In NAV, navigate to Phys. Inventory Journals and calculate inventory as you normally would. Use Send to Excel to transfer the Phys. Inventory Journal data to an Excel spreadsheet.

In Excel, remove unneeded columns for doing a count. In my case, I only leave "Item No.," "Description," "Bin," "Location," "Lot," "Qty. (Calculated)," and "Qty. (Phys. Inventory)." Create a new table by selecting the data and Insert - Table. Checkbox "my table has headers." Save this file to OneDrive.

Creating the PowerApp

Open PowerApps Studio Desktop or Web Studio. Connect to OneDrive and use the Excel file you just saved. Use the Table you just created.

PowerApps will begin using your Excel file to create the App.

Once loaded, we can move on to customizing the app.

In the main Browse Screen, give the list a name.

Reorder the browse screen for how you would like data to appear. I use Bin for title, description for subtitle, and Qty. (Phys. Inventory) for body.

Moving on the Detail Form, reorder the columns as desired.

As well, you will want to change the Display Names to something appropriate. For example, instead of "Qty_x002e_x0020_(Phys_x002e__x0020_Inventory)" you could use "Physical Count."

After editing the Detail screen to your liking, it may end up looking something like this

Repeat this process for the Edit screen.

As well, in the edit view, you will want to disable editing of everything except for the count field. To do this, select the fields and scroll down to "Disabled," setting Disabled to true.

Save the app.

Using the PowerApp

Opening the app in PowerApps for a mobile device will allow you to enter count data for the items contained in the Excel table derived from the Phys. Inventory Journal. The data entered through the app is automatically synced back to the Excel document on OneDrive. When finished, the data can be copied from the Excel file in OneDrive into the Phys. Inventory Journal in NAV with "Paste Rows."

Updating the data for a new inventory period or a different calculation is simple, as well. Just change the data in the Excel workbook, save, and refresh the data source in PowerApps Studio.