A PowerApp for InventoryOnHand and Order Information
With the availabilty of PowerApps and the data source for Dynamics 365 for Operations I started to test which opportunities will open. How can business users be supported to do their business in easiear and smarter way. Maybe onsite with the customer and access to the most freshest data of Dynamics 365 for Operations.
For testing the features of PowerApp and the data entities of Dynamics 365 for Operations we have the requirement that the sales reps to be informative to prospects and customers all the time to all products whether these are on stock and if not – when will these arrive and can be delivered. So i started to build a PowerApp to give just this information to our sales reps to improve the onsite sales process.
A lot of common controls for mobile apps are prepared to be consumed typically by mobile devices as sliders, radio button as well as navigational elements. As a experienced mobile device user i missed none of the commen elements we are used from other mobile apps
Logic and programm control is generated using forumlas. Formulas are expression similar to Excel which can be nested and are connected to the properties of the controls and objects as text or image elements. Formulas are calculated automatically as values have changed and can be executed for more than one action in a subsequent order if you seperate each expression of the formula by an sepeerator (;).
Most important for creating PowerApps is to realize Delegation. Delegation means that only up to 500 records are loaded to the PowerApp. This makes sense not only because of limited screen size but also from view of loading time for data from any data source. So you have to prepare conceptual to guide you users to find the right information in any datasource also with a huge number of records. The question is who wants to scroll at 500.000 records to find the right one.
So delegation means to move the query logic to the server side database engine. This makes sense. And because you can build query logic that can not be quite simple transported from mobile device to every supported data source, sometimes the created formula tells us “you lost delegation”. Formulas with lost delegation are shwon us with a blue underlined parts of the formula. In this case you may loose correctnes and completness of the selected data, because only first 500 records will be loaded to the local device. So be aware if you use also any aggregate function while selecting a non delegatable data query you may have wrong aggregated values as a sum or average because it may be calculated for the first 500 (loaded) records and not over the complete record set.
If you have queries with loosed delegation PowerApp shows this in the formuala (blue underlined) as well as at the mentioned screen and gallery with a blue circle. This means that query could not be resolved in this way to be executed complete at server side’s data source. Be aware and create better design!
The data source for Dynamics 365 for Operations is still in preview, so I saw some abnormalities while creating the InventoryOnhand App. For this first preview i connected direct to an Dynamics 365 for Operations deployment and not to an Common Data Model (CDM). I queried the data entities InventoryOnHand, PurchOrderLine and SalesOrderLine.
Some fields of the entity did not appear in the PowerApp whether they are part of the entity. The missing fields i used in PowerBI before if i query the same entity, but in PowerApps i misses e.g. SalerOrderNumber. Another issue was the query to the SalerOrderLineStatus which is normally a enumeration, but i can’t select the status in a regular formula without loosing delegation.
Because the App is designed to be more informative and we won’t create new records i built the App from scratch without using the PowerApp Generator i described in Part 1 of this post.
The three created screens are:
InventoryOnhand, overview for items on Stock, searchable to item numbers and Productname, sortable on Item Number, Productname, Total Available Quantity or OnHand Quantity.
Touching one of the cells containing the three dots (…) switches to the details screen of Purchase or Sales Orders.
SalesOrderList, shows items on open SalesOrders to customer with requested and confirmed delivery date. The SalesOrtderList is sortable by requested or confirmed delivery date, salesPrice or ordered quantity
PurchOrderList, shows items on open PurchaseOrders with requetsed and confirmed delivery date. The PurchaseOrderList is sortable by requested or confirmed delivery date, salesPrice or ordered quantity
Some topics are built in the InventioryOnhand PowerApp are:
Light full text search for Items. Using the Search Function allows to take one or more columns of the data source to search for a given seach string. So we can enter a Item Number, Product Name, Product Color, … or whatever column we tell the Search Function to use for search.
Flexible sort order is just given with the Function SortByColumn which takes one field to sort the record set. Using UpdateContext Function reverses the order to sort. With using a Dropdown Control sorting gets more flexible to choose the column to sort by the user.
Navigation is done by “OnSelect” events we can add to every element as text boxes or images or icons. So User can see details of all open Purchase orders for an item (=row) if he touches the “OnPurchOrder” column. Tooltips are provided as well as Hover-Events even i don’t know how to use it in a meaniungful way on a touch device.
Data Refresh of the datasource can be done by the Refresh Function which allows user to update individual screens at every time to get the most uptodate OnStock, OnSalesOrder and OnPurchOrder information.
So far we did not create a informational screen which was requested to show product details to have some product information, images or product attributes available on the PowerApp. This could be done by creating a details screen switching to if the user touches the item number.
You can also see Part 1 of the post to create a easy three screen PowerApp with PowerApp Generator.