Power Apps & Dynamics GP – Microsoft Dynamics GP

Welcome to webinar Wednesday my name is
Paul Johnson I’m a software Solutions
Architect with Prophet Business Group and
have been with the organization for over
12 years in this webinar we will review
how we build simple power apps to
interact with information in Dynamics GP
this webinar is geared toward the
financial analyst role and there will be
some subject matter that is it a little
more technical in nature the webinar is
being recorded and chat is available for
you the audience to ask questions chat
is not being recorded our outcome for
today is to understand what power apps
are and how to connect them to Dynamics
GP data
let’s start off by reviewing what the
data gateway is we use the data gateway
to connect to on Prem data such as
Dynamics GP and connect that data to
cloud apps such as power ops and flow
data gateway is typically installed on a
sequel server however it cannot be a
domain controller
what are power ups power ups our suite
of apps services connectors in data
platform that provides a rapid
application development environment to
build custom apps for your business
you can quickly build business apps that
connect to business data stored in
various online and on-premise data
sources apps built using power-ups
provide rich business logic and workflow
capabilities to transform your manual
business processes to digital automated
processes / ops can be run on your
browser or um or on a mobile mobile
device basically you can build custom
business apps without writing any code
let’s jump into a basic demonstration
so let’s jump into a very basic demo I’m
gonna be creating a power rap that has
Excel as its back-end and the Excel
workbook will be saved into onedrive so
I have onedrive open right now and I’ve
just gonna create a new file and it
would be an Excel workbook document what
you’ve already done here’s the workbook
I decided some basic columns to this
workbook customer ID customer name
customer type and a balance amount so
this part will allow us to add new
customers and also update the balances
of those customers so once I have the
Excel workbook created and we’ll also
want to save this data as a table
so you’re just highlighting the cells
and then inserting a table is typically
what you’d want to do so I’ve already
done that already
so now I’m gonna jump over to power-ups
and start building naps so it’s as
simple as going into power-ups so let me
just minimize this for a second we’ll
jump into power-ups which I already have
open here
so we log into perhaps comm and we
select the appropriate environment and
we’re just gonna select something pretty
basic which is we’re gonna build an app
from data so to do that we go to the
creat option here on the left hand side
and then we’re gonna select start from
data and we’re gonna hit create and it’s
gonna prompt us for connection so in our
particular case we’re gonna select a
phone layout and we’re gonna select our
data is going to be stored in onedrive
for business but this could be any sort
of the connectors that are available
with floor power-ups so we’ll select
phone layout onedrive for business and
then I need to select where the file is
actually stored in onedrive for business
so profit webinars a folder and I have
my customer balances one webinar file
located there now previous I indicated
your data need to be stored in a table
I’ll say that’s case it’s going to only
look for tables so I’m gonna select the
table name I created and hit connect
what this is going to do is it’s going
to go out and build a basic power app
just from the data I have in my Excel
workbook so it’s gonna create it’s gonna
bring up the studio which is what this
is to allow me to add it’s my app but at
this point its created three different
screens for me it’s created a normal
detail screen and an edit screen or the
three screens so here’s my normal screen
if I go to detail screen
it’s showing a bunch of detail and then
the edit screen is something very
similar as you can see it’s created the
customer ID customer name the type
customer balance is not on here we don’t
want type we
one customer balance sort of do that I’m
gonna edit this particular screen so
first thing I’m going to do is I’m going
to change the title up here so to do
that I’m just basically gonna select
that particular label and just put in a
new title so we’re gonna call this
customer bounces see it’s pretty pretty
straightforward the next thing I’m going
to do is I want the third field to be
the balance amount so I’m gonna select
the particular browser gallery which is
what I’ve selected here and I’m gonna go
to properties and fields and select edit
and as you can see the body says
customer type I can just hit the
drop-down it’s gonna show me all the
fields that are available in my
spreadsheet that’s the balanced field
title its customer ID customer name I
actually want to flip these around so
I’m going to change this to be customer
name and this to be customer ID as you
can see as I’m making changes it’s being
reflected on the form in front of me so
pretty pretty straightforward the other
thing I can do is pretty it up pretty up
the numbers here so if I select that
particular field the balanced field now
we can go to the Advanced tab I can
scroll down to the text property here
and we’re just gonna put dollar signs
space dollar sign and purse and as you
can see it’s put a dollar sign in front
of those values I can even format this
to be a number I’m just going to leave
it as is for right now I’m gonna get the
idea so at this point we’ve created our
basic first window I should also mention
I can zoom in
two on the screens a little bit just
just by moving this browse this slider
around so just make it easier for you
guys to see make that obviously you can
see a little less real estate to work
with if I go to this next screen here
which is our detail screen I also have
the balanced amount customer ID customer
name and type I’ll just leave that as is
and the Edit screen once again we’ll
leave that as is
so now what I’m happy with the app I can
go file give it a save it give it a name
so save as give it a name here it’s
gonna save it up to Power Platform
environment it’s gonna minimize we’re
just gonna actually play the app and
show you how it works so if I say
preview this app and click on this
button or f5 lets me let me redo that
again let me just start off from the
main screen and we’re gonna hit play so
basic I can search I can do a search for
let’s say blue it’s gonna do a search
I’m gonna take off that search so very
similar to how mobile apps or native
apps work today mobile device I can
actually drill into one of these records
so if I select a alarm company I can
drill into it and as you can see see the
data if I want to add it particular
record I can click this edit button and
I can change the balance to let’s say
7000 and I can hit the submit button
what this is doing it’s going out back
out to our excel file and updating the
values I actually have that file open
right now so it’s not it’s locked so I’m
actually gonna go and close that
particular file
just so we can actually write to it just
this record here which is close that so
I should be able to submit this 7,000
which I’ve submitted it so that’s that’s
great and if we were to jump I can also
add a new record and you can do that by
just clicking on this plus button we’ll
set this to be $7,000 we’ll give it a
customer ID of zero zero zero zero one
and it goes burn a webinar and we’ll set
this to type one and I’m gonna submit
this so as you can see it’s added it to
the list which is what we wanted now if
I jump back to the Excel spreadsheet in
the background which I’ll do here
and we were to open that file back up
you will see that it’s added that
webinar customers Orser one-hour type
and our balance amounts been added
it also power-ups also adds this extra
column to your table so I basically can
correlate a record back to the record
within the power wrap so don’t be
alarmed if you see that that’s how it’s
supposed to function so let’s jump into
another demonstration on connecting to
Dynamics GP data and updating that data
back into Dynamics GP from a powerup
so I’m just going to go back to our
perhaps environment and we’re going to
so same thing we did before which was
create basically a new app so I’ll just
close this one we’re not gonna save that
at all so I’m gonna go back to create
and we’re gonna select start from data
we could also build from a blank canvas
and we can also build in model driven
apps but we’re not going to be covering
that today so we’ll say start from data
and hit create
and using the data gateway obviously
there’s a sequel server on our Prem on
Prem server so we’re gonna select sequel
server as our connection here
we’re select phone layout and it’s gonna
want me to define a connection back to
our data gateway so I’m just gonna show
you what that looks like you’ve already
created one here but I’ll just show you
what that looks like so I’ll select new
and then I’m gonna select sequel server
and this is where it’s gonna prompt me
do you want to connect to arm Prem data
gateway which is what we want we’re
gonna specify our server name so PPG
demo serve our database name so this is
our Dynamics GP company database so TW o
PA in this case authentication method
we’re gonna select basic and I’m just
gonna put sa password in here and then
the data gateway we’re gonna connect to
was PPG demo serve and we would hit
like I indicated I’ve already done that
so I’m just going to hit cancel to that
and we’ll go back to our previous screen
select data create
close that window
when I select the sequel server and I’m
going to select my connection and then
it’s going to prompt me what table or
view do I want to select in this case
the app we’re going to be building is
going to be updating credit amounts
inside of the customer maintenance
window or the RM 0 0 101 table in
Dynamics GP
so we’re gonna select RM 0 0 101 table
that’s the field that hasn’t credited
mountain work and hit connect
it’s going to go ahead and do the same
thing as before it’s going to take all
the fields in the RM 0 0 1 and sort of
built what it thinks is the default app
we want we’re then going to modify that
app shortly and make it to what we want
force what the system is created for us
so I have the basic app created I’m just
going to make this zoom in a little bit
so everyone can see this so first thing
we’re gonna do is we’re gonna change the
header of the app so I’m just going to
rename this just like we did with our
other app so we’re just name the
customers and we’re gonna select the
particular gallery and we’re gonna
change the fields that are in the
gallery so I don’t want address I want
this to be the title to be the customer
name so as you can see all the fields
got returned here but we’re gonna set
that customer name we’re gonna set the
subtitle to be customer number and we’re
gonna set the body to be our credit
amount field
or credit limit amount sorry so we’ve
done that as you can see it’s created
our basic now we’re gonna do the same
thing we did before we’re gonna select
we’re gonna select our field that we
want here which shall be body one field
and we want the format of this field to
be changed so we’re just going to select
advanced multiple ways of doing this
amaura select death the field text field
put dollar sign double quotes dollar
sign space ampersand and then will
format this field to be so this will be
text format amount and this has to be
closed with quotes close that off so as
you can see each one of these records is
pulling from GP and it’s given me the
information I want so that’s that’s
that’s perfect I can now go to the next
step which is I think this sort of
screen is okay to what we want we’re
gonna go to the detail window detail
window has you know a bunch of other
more information it doesn’t really
matter the the fields that are on here I
just want to show you how easy is it to
remove and add add fields so we’re just
going to select up to Claire window and
I’m gonna go to advanced
we’re select edit field here’s a list of
all the fields that are on the screen so
I’m gonna leave customer number address
one two three I’m gonna get rid of so
let’s just make click on our left
clicking and removing that field and
it’s just removing it from the form not
from your data set so balanced type we
also don’t want see and I don’t need to
see this information here it’s obviously
taking the first X number of fields now
to add a new field I’m just going to
click Add and we’re gonna add the credit
amount credit limit amount of field so
I’m just gonna click that and hit add as
you can see it added it to the bottom
and it’s on the form now so I can go
ahead and change the titles and you know
change the field values and make it look
a lot nicer but I’m not gonna do that at
the moment I’m now gonna jump over to
the Edit screen once again we have much
of extra fields we don’t need so I’m
just going to do the same thing we did
last time which is we’re gonna select a
field here for advanced edit our fields
so I’m just gonna remove a bunch of
fields we’re just gonna on this
particular form while we’re gonna keep
is basically the customer number and the
credit amount
those are the only two fields that are
required to update within GP so we’re
just gonna add that credit or credit
amount back on here
or credit limit amount so I have the
customer number and the credit limit
amount one of the major issues is that
we are not able to directly update a
record in on a sequel table that has a
trigger on it so to get around that what
we’re going to do is create a flow and
Microsoft flow which calls a store
procedure which will then update the
record in the sequel table so let me
just jump over to sequel management
studio so I can show you what that
particular stored procedure that it
created looks like so basically just
passing the customer number and the
credit limit amount as parameters within
the store procedure and then we’re just
doing a simple update our m01 setting
that to be our one parameter where the
customer number equals our other
parameter wrapping that around a begin
and commit transaction inside a flow so
let me just jump to flow for a minute
and I can show you what our flow that we
created looks like so that’s pretty
straightforward and that we create a new
flow if I go to the home page and we’ll
just type power-ups
so here’s some templates that exist so
here’s the one that we want which is
execute store procedure on a per apps
button click so if I select that it’s
gonna go out and it’s gonna say what
connection do I want to use I’ve already
have this connection called TW PPG
sequel so then I hit continue I don’t
need to do anything here I just need to
define what the store procedure name was
which was USP something in our table so
it’s loading all the store procedures
that exist in my database as you can see
it’s found our store procedure and then
next step is to provide our true
parameters for store procedure and in
this case we’re just gonna say ask power
apps and select each one of those and
that’s all we’re willing to do and we
would just save this flow and we can use
this within our powerup so let me just
jump back to our power app so on the
edit screen we want to add a button at
this point so we’re just going to insert
a new button
and we’re gonna change the text on that
button to submit the other thing I want
to do is get rid of this particular
button here so we’ll just delete that we
don’t need that on submit so now when I
click this I want an action to happen so
I’m just going to select action and this
is where I specify a flow of action on
this button and it’s gonna look to see
what store procedures we’ve created we
got this one called execute store
procedure so we’re just going to select
so it’s this perhaps button click run
now this wants parameters so we need to
specify the parameters we’re going to
use and in our particular case we have
on the edit screen we have the data card
key 9 value and data card credit amount
which is the data card 18 value in this
particular case so so we just need to
supply those so the format of that would
datacard value 9
don’t text as we can see it’s
highlighted that particular value green
and it’s actually highlighted that
particular field on our form green as
well which is great and supplier data
card 18
so the purple is the customer number and
then green is the credit close all that
the other thing I can do was add extra
commands into this particular submit
button when it’s clicked so the way to
do that is just by doing this so one of
the things I’m gonna do is I’m gonna say
I wanted a nav once I’ve clicked the
submit button I want to navigate back to
so just navigate screen one but before I
actually do that I’m gonna add we’re
gonna do a refresh of the data so what
that’s gonna do is it’s gonna pass in
these parameters into the flow run the
flow and then it’s gonna refresh our
data so it pulls back into our dataset
so refresh RM 0 0 101 close that off and
we’re gonna add another command or
should have had closed bracket there
another command which is the navigate
command or do do before and we want to
go back to browse screen one probably
should have give them these a better
name just easier to maintain the
application after but you get the idea
so once I’ve done that and just go off
of there now we can run the app and
actually see this in action so to do
that I’m just gonna scroll up here go
back to my main screen
and we’re gonna hit play I’m actually
going to change this to be just some
just to make it a little easier we’re
just going to change this to cuss name
and I’m just can’t get rid of all of
these fields in my sort just so I can so
Aaron fits us up to talker I made a
mistake spaces I didn’t want there so
we’re gonna hit play at this point now I
can refresh I can you know sort this in
different orders and that new customers
obviously can’t add new customers
because I’m only passing in the credit
amount into GP in this particular case
we’re gonna preview the app so Aaron
Fitz we have 20,000 dollar credit amount
if I hit the edit and we can put in a
amount so let’s put in 90,000 and we’re
gonna hit submit so this has gone out
it’s updated Aaron Fitz if i refresh
this form as you can see it’s now saying
90,000 here if I go into GP at the
moment and we bring up Aaron Fitz we’re
gonna see
that the credit amounts for this
particular customer has now been set to
$90,000 so that concludes our demo of
the power app but once again I would
save that app so I don’t lose any work
and I would do that by going to once
I’ve previewed the app I can close this
and I’m back in the studio and I can go
file save as give it a name
and I would hit save because if you
don’t save and you just close the tab
you’ve just lost all the work that
you’ve done because the studio is a
browser-based cloud application not
running locally on your PC let’s go over
some basic dynamics GP use cases for par
raps some of the apps you can build are
check and update customer balances in GP
this would be the credit amount that we
covered earlier in our demo checked
deposits in checkbook and payments
associated with that deposit you can
check users logged into GP and trigger a
floor to send them a team’s message to
log off of GP you check quantity of
product in a site in GP and show
quantity on order in quantity quantity
allocated of course there’s many more
use cases these are just some of this
some of the ones we’ve implemented in
the past webinar recap we reviewed today
what are power-ups basic power-ups demo
connecting to Excel we also reviewed
power-ups demo connecting to Dynamics GP
data and updating data back into GP and
we reviewed some use cases for power
apps and GP join us for these upcoming
webinars October 23rd Dynamics 365
marketing lists in Dynamics 365 October
30th a review of Summit Conference
November 6th yammering on what is the yammer
November 13th Microsoft flow and
teams together
thanks for watching today please visit
prophet.ca/webinars to register
for upcoming webinars and view on-demand
videos subscribe to our YouTube channel
Prophet Business Group if you want to
talk about working with the power
platform and perhaps contact me Paul J
at prophet.ca to chat all things Power Platform.

Leave a Reply

Your email address will not be published. Required fields are marked *