How long does it take to create a cross-platform web application? The answer is often “it depends.” Application development can takes days or months, depending on the complexity. However, if your goal is to use your mobile device to view Microsoft Dynamics GP data on the go, or if you’d like to view this data in a desktop browser, there is a new method available where you can create an application in minutes and deploy it across your organization.
Welcome to Microsoft PowerApps
With PowerApps, Microsoft providesthe ability to connect on-premise SQL databases to mobile and web applications that will work across both mobile and desktop devices. When creating your application, connect to the database and select the table columns you would like to display to the user. The app is then ready to deploy. We will take you through the process below.
View GP Customers using a PowerApp
Before we proceed, a word on set up. There are some one-time setup steps that will need to be performed to make your GP database available to PowerApps. The main piece is the installation of an on-premise data gateway. As the name suggests, it stands between your app and the data. You have to create a connection to your data (see step 5 below).
To create an app, you can either install PowerApps Studio for Windows, or use PowerApps Studio for web, which allows you to create apps using the web portal at powerapps.com. PowerApps Studio for Windows is only available for Windows 10. If you’re on an older version of Windows, then PowerApps Studio for web is your only option. Both provide similar functionality.
Example: Create the App and Connect to Data
- In PowerApps Studio, click or tap New on the File
- Under Blank app, select Phone layout.
- Near the lower-right corner, select Data Sources.
- In the Data Sources tab on the right, select Add Data Source.
- If you have never created a connection to your SQL data, click the New Connection button to create one. Select SQL Server, select the Gateway that you had installed during setup, and click Connect. If you have previously created a connection, then simply select it. Either way, you will eventually be presented with a list of SQL tables.
- Select table RM00101, and click Connect.
Display Customer Data
- In the right-hand pane, look for the Layout tab, and select a layout. We’ll choose the one with a header, sub-header, footer, and description. Several items get added to the app’s main screen.
- Click in the centre of the app’s screen (this area is called the Gallery – Figure 1).
- On the top left, select the Items property, and enter this formula into the formula bar:
Sort(If(IsBlank(TextSearchBox1.Text), ‘[dbo].[RM00101]’, Filter(‘[dbo].[RM00101]’, TextSearchBox1.Text in Text(CUSTNAME))),CUSTNAME, If(SortDescending1, SortOrder.Descending, SortOrder.Ascending))
At this point, the screen may become empty and show several warning icons. We’ll fix those up next.
- Select the Gallery again by clicking in the centre of the app’s screen (Figure 2).
- Select the Body drop-down and enter this formula for the Text property:
Concatenate(TrimEnds(ADDRESS1), “, “, TrimEnds(CITY))
- Select the Gallery again, then the Heading drop-down, and enter this formula for the Text property:
- Select the Gallery again, then the first Subtitle drop-down, and enter this formula for the Text property:
- Select the Gallery again, then the second Subtitle drop-down, and enter this formula for the Text property:
- Click on the Title bar at the top of the app’s screen, and enter this formula for the Text property:
- Click on the circular Refresh icon to the right of the Title bar at the top of the app’s screen, and enter this formula for the Text property:
- Click on the Sort icon to the right of the Refresh icon, and enter this formula for the Text property:
Save, Run, Share
That’s it! Just go to File > Save to make sure all changes are saved, then click the Play icon on the top right to run it. Once you’ve tweaked it to your liking, go to File > Share, and share the app with anyone in your organization (Figure 3).
In minutes you have created an app that is cross-platform and allows you to view customer data on the go.
What we’ve covered here is just a brief introduction to what PowerApps can do. Full-fledged applications can be developed to not just display but also manipulate and share data. A word of warning though – since we are connecting directly to the SQL database, if you allow your app to delete or modify data, there is little you can do to prevent your users from creating, changing, or deleting records, since the app is bypassing GP’s business rules. This has the potential for some very data-damaging actions unless the users are restricted based on security roles. Stay tuned for more on this topic in future posts.