Powerapps on premise data gateway 

You're welcome!

Thank you. I appreciate you taking the time for kind words, they are what keep me making content.

De nada (if I remember my Spanish)

You have the right idea BUT the problem is going to be there isn't an easy way to manipulate AD on prem with Power Apps. If you had Cloud AD or Entra or whatever we call it this would be much easier. Maybe the idea right now is build an app to facilitate collecting info and tracking tasks, but leave the actual work of AD manipulation to PowerShell? Just a thought.

@@ShanesCows Great info, thanks! Most of our tasks are done via PowerShell now, so maybe I can come up with something that tracks, like you say, and can provide historical lookup capabilities, but that can also execute (call) PowerShell scripts to perform specific tasks...? This is going to be fun 😀

Thanks again 😻

Thank you. Yes, that video is on my list to be made. Hopefully sooner than later. I think the list is up to almost 20.

Thanks, glad it helped.

No. The Gateway calls out to Azure instead of Power Apps calling in.

Thank you for the kind words. Do you mean talk about the whole delegation challenge? That would be a good video.

Most business are creating a dedicated account for it. Good news, I think you understand the options, just need to pick the right one. Probably not the personal option though.

You can choose to share it with others or not I am pretty sure.

Sorry I am slow. Either plan would work for you. Per app is probably cheaper if you just need one app covered.

Sorry, not sure. I haven't used MySQL and POwer APps before

You must have a p1 to use the on-perm data gateway. You can use Azure SQL without a p1 license though.

The gateway and the server need to be on same network. Users don’t matter

Look for my video on the SMTP connector. Might be an option

Glad to help. 😀

I don't, sorry. Haven't tried that one.

If they are In the cloud yes. If they are in Orem you need the gateway

I haven't ran into that. I think what you need to do is go into Connections and add a New connection to Postgre. IN that connection specify the Gateway.

I think they do but not sure. Hmm

In the demo they are the same machine, but as long as they are on the same network the Gateway will find it. They don't have to be on the same machine.

Same one. 😎

But when I am login in power automate it is asking for new gateway. My existing gateway is not coming. Why?

@@ShanesCows Hi Shane, I am also facing the same issue but I am using a free trial of power app, but I have a gateway already installed in the server and still I am not able to see the suggestion for the gateway. Is there anything related to license that causing the issue?

Hey Brett. I haven’t done it but I think you will have to look into query of the graph api. I agree a key is involved but not my bag of tricks.

Sorry, I have never used that API. Did you see this video? ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-gLhrHxvKBdI.html Maybe it can give you something different to try.

I haven’t done it. Sorry. We have as a company but I don’t have a demo for it.

I think you would have one gateway, and then multiple connections per database. So yes.

@@ShanesCows Thanks for your feedback and help. I enjoy your channel and contents.

Plan1 will let you connect. Yes MySQL will work.

The Gateway does require a premium Power Apps license. Maybe try going and signing up for the trial to see if you can get through the error?

@@ShanesCows It does require the Premium license as of year.2020.

Thank you. 😻

Not sure. I haven’t ran into that before

Roberto I don't know what I was talking about. Can you leave a new comment with a time stamp and I will see if I can figure it out?

@@ShanesCows Hi Shane, is at 4:20 when you start to talk about some limits. Thnx man

@@robertomarra2431 Okay. I was talking about licensing. Which has changed a bit. The key thing for you to know is that using the Gateway with Power Apps or Automate it is a premium connector. That means anyone who uses that app needs a premium license. The base license included with O365 does not include a premium license.

You are welcome!

Glad you liked it!

Live.

Howdy. The gateway needs to be installed on the same network as your SQL server and the machine you install it on needs to be running. Is your desktop at work possibly sleeping or shutdown?

Ken I would assume this would work the same with on prem sql. powerapps.microsoft.com/en-us/blog/return-an-array-from-a-sql-stored-procedure-to-powerapps-split-method/

Hey Lee I am not sure. Sorry I haven’t tried that scenario before

I don’t think so. I actually made this video using Express. Not sure.

@@ShanesCows thanks Shane - after a few days I figured out I needed to list the SQL Server name as PCNAME\SQLEXPRESS and not just PCNAME as you have above. Worked like a charm after that! Thanks for putting this video together.

No. You need a p1 license to use the gateway

I don’t know. Sorry

Are you getting 500 error? I am getting this { "error": { "code": 500, "message": "Expression value is invalid. The template field is required.", "source": "europe-001.azure-apim.net", "path": "choose[5]\\when[1]\\choose\\when[1]", "clientRequestId": "604fb49c-d7c2-449d-8f08-b846-339422c1" } }

@@shashank2509 I resolved my issue by deleting all my connections and flow, making sure my on-premises gateway is running and when I am making connections, one of the field actually picks up the configured gateway..

Same gateway

After you installed it on the server did you add it to your account? It should work exactly the same as when you did it on your desktop.

Shane Young How do you mean add it to my account?

I don’t have one running all the time so not sure. Email me and I will see if I cannyou more details. Shane at PowerApps911.com

Thank you. Someone else asked for something very similar so there is a good chance. Can you give me more details on exactly what you are trying to accomplish? The more details, the closer hopefully the video is to an exact fit for you. Thanks

Hi :D Well I.m making an Apps to makes order so I made 2 Database first one with the general information of the order and the status of it. And the second one with the detail of that order (Maybe want to order 2 or more sku in 1 order). So in the edit page I had 2 forms and a table. first form to actualize information of the order. The table to show de detail of sku in that order, And de second form to add or edit and selected sku in the table, this last form only apear que I click a button for new sku o edit a sku in the table. The connection between both table I did by a Key columns call Order Number. My problem is that when I want to create a new Order I could add new products in a collection, so If I submit it then I pass the information to my table inside my database, but if I cancel the order the collection never change my data in my table for detail data for sku. In other data base the change happen in cascade for the conections, but in powerapps I make that conecction manually so if I change something in the Data 1 it doest mean that ir will erase the data created in the Data 2. I know I can do a command to erase but the user can close the application while is creating an order. So I dont want to make any change until I submit all forms.

Should be very similar 😀

The SharePoint Server needs access to the Gateway server. The Gateway server needs access to the internet. SharePoint does not need to talk to the internet directly.

@@ShanesCows Thanks Shane, so bottom line is my power apps will stop working if I lost internet capability.

Where is your sql server located? Yes is the answer.

Thank You Shane. I have another issue right now with connecting Gallery control to two data sources and displaying its data to two different forms. But I am able to connect to one data source at a time using Gallery and able to show data in only one related form. Other form doesn't show the any data when I select items in Gallery. Any help is appreciated. Thanks.

I don't know if I have an answer there. I am guessing they are both in the same ballpark of security so I would ask which scenario are you more comfortable with? Is cost a factor?

Shane Young cost is not mainly a concern, however performance is. when using a gateway, would powerapps application query the on-premises sql server everytime when someone opens an application? Would this not translate to a lot of traffic and reduced performance.

I haven't tested it but I am 99% sure that performance would be best going against an Azure SQL Database vs. an on-prem database.

You're welcome!

Hey Tony. I have not done this scenario with on prem... I am not sure what I would try here.

Shane Young Thanks for reply. Will let you know if I figure it out 👍🏻

No trust / Networks talking in the Scenario

I don't know but I think it should be. In my video my local network was not part of the same network as my Office 365 tenant.

Thanks @@ShanesCows ! Keep up the Great work

You could adapt what I show here to do that ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-QcWjAt7QVn4.html

@@ShanesCows thanks, but if I want to achieve this without using SharePoint as well is there a way?

Connection account.

@@ShanesCows Thank you !!

Not sure Patrick. I haven’t done much with BC

@@ShanesCows ok

Not for Power Apps. Any app with an on premise connection will be premium. None of the office licenses cover this.

@ShanesCows thank you very much for the info.. I take that the same applies to power automate..?

Yes. All of my customer Apps (almost) use SQL. You start with a New, Blank Canvas app and then import your data source and design away.

Shane Young, ok. But I don’t get the app as “easy” as the example in the video, correct? Thank you for your reply (and your videos).

Correct. You will have to build the app yourself.

Not that I know of. Sorry

Thanks 🐶

You’re welcome 😊

I think you can. I have never done it but when I add a connector for Oracle it asks me for my Gateway. So I think yes.

Hi Shane, tks for your advice. Success... In addiction, I had to install a driver (Oracle 11ghome1/ODAC) on my gateway machine host. Regards, Leandro.

Cool

Yes. I don’t exactly follow what you are trying to do?

@@ShanesCows Thanks Shane for the reply. Basically I want to link the data retrieved by the gateway to a dropdown in a SharePoint list. Can I do that with powerapps gateway or the data from the gateway only accessible with in powerapps?

Only in PowerApps. But you can customize your SharePoint list form with a PowerApps app. Would that work?

@@ShanesCows If i understood you correctly, sharepoint list can be converted to a powerapp and then it can be link to a powerapp created with gateway On Prem data to populate the data in to my list?

Try this video. Custom SharePoint List Form with PowerApps ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Badj3_T8zAw.html

No, you can install it on any machine on the network

@@ShanesCows thanx! stay blessed

I actually do. It is in the context of SharePoint but should work for you. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-0g7t0E2rf2s.html

Did you use the MySQL connector instead of the sql connector?

@@ShanesCows yes of course ... problem solved itself . I had to set up a remote user in mysql

De nada 😁

It works I am pretty sure.

Not sure. Sorry

@@ShanesCows Alright . Thanks anyway.

Yikes. I have no idea. Sorry

ProjectData or ProjectServerAPI.

I don’t think it is possible

@@ShanesCows Thank you for qucikly respond.

I know. 😀 But I am not in the list of people who care if people say on prem, on premise, or on premises. Grammatically I am not correct but I have never worried about it. My buddy Todd it drives him crazy. 🙃

Amen! I've been hitting Shane on the nose with a rolled up newspaper about this for years, decades, centuries!! and he just doesn't learn. This is one of many, many reasons I am certain he was dropped on his head at least once as a child. And maybe again as an adult.