cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AxelBA
Level: Powered On

Connecting to Azure Cosmos DB

I am trying to create a PowerApp with an Azure Cosmos DB.

 

Setting up the connection is pretty straight forward. I just add a new connection and fill in the blanks:

NewCDB.jpg

The information can be found in your Azure Portal. Go to your Cosmos DB and lookup the URI and the Primary Key

CDBKeys.jpg

The URI goes in the "Account ID" slot and the Primary Key goes in the "Access key to your Codmos DB Account"

 

You cannot set a name for the connection during creation, so you might want to rename it after creation, if you have more then one connection.

ConnectionRename.jpg

Now it is time to create the application. I created a new Phone app and clicked on the "Connect to Data" link on the start screen:

SelectDB.jpg

I selected the New Azure Cosmos DB:

selecteddb.jpg

Now comes the strange part. When heading over to Insert->Gallery, it seems the connection has vanished:

CDBgone.jpg

But when adding the database again, it seems it is there anyway:

seconddb.jpg

Well, it might just be some GUI glitch, so I decided to try out the Connection anyway and added a function to Items.

queryerror.jpg

It seems I have to save and restrat the app, before I can proceed. So I did. Looking at the "Apps using this connection" on the connection, I now see the name of my newly saved App:

appconnected.jpg

Unfortunately I still get the same error. The Azure Cosmos DB seems not to be connected and if I add it again, I get yet another connection.

 

Even though the connection seems to be gone in the Gallery connection, it is possible to se the _rid label from the Azure Cosmos DB in the Field selector:

buttheridisthere.jpg

Any help on this would be much appreciated!

11 REPLIES 11
PowerApps Staff aorth
PowerApps Staff

Re: Connecting to Azure Cosmos DB

Thanks for reaching out on your issue.  Could you share a session id for this issue so we can see what is happening when you try to use the Azure Cosmos DB in the canvas app?

AxelBA
Level: Powered On

Re: Connecting to Azure Cosmos DB

Absolutely

Session ID: fb12a6a1-556d-4739-b26b-c5484874ebf4

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Connecting to Azure Cosmos DB

Hello @AxelBA,

There a few issues with your scenario, so get ready for a long answer Smiley Happy. But before that, a short version - unfortunately, PowerApps doesn't work well with Azure Cosmos DB, as data coming to / from PowerApps needs to have a strongly defined schema (the names / types of columns of the data should be known in advance), which is not the case for Cosmos DB. Microsoft Flow can work with it, so a possible workaround is to access Cosmos DB (see more details below).

On for the issues:

Why you didn't see connection in the gallery data sources in the right-side pane

There are two types of data sources in PowerApps: tabular data sources and "function" or "API" data sources (not the official name). The former represents things like a SharePoint list, a SQL Server table, a CDS entity, and so on - they can be used directly as the Items property of a gallery, for example, as they're already a table. In the latter case, they're not a table, but they can be used to call functions that return a table - for example, Office365Users.SearchUser. In the data source selector for the gallery only tabular data sources are shown (as they can be used directly). The Azure Cosmos DB data source can be used to call functions to return a table (such as GetDocuments) but by itself it's not a table.

Not really an issue, just a hint

You don't need to use the entire URL when creating the connection to the Cosmos DB - instead of using "https://cloudeonemployee.documents.azure.com:443/" you can specify only 'cloudeonemployee'

ForumPost001.PNG

PowerApps not working properly with Cosmos DB

I'm guessing you added the connection in web.powerapps.com - there you can add connections to all supported connectors, as they can be used in both PowerApps and Microsoft Flow. PowerApps has the limitation, as I mentioned before, of only working with data that is strongly-typed (or has a well-defined schema), but Flow can work properly with them.

If you try to add a new connection from PowerApps Studio itself, you won't see an option for Azure Cosmos DB:

AddConnectionToCosmosDB.gif

You will still see it in the list of existing connections. I'm not sure if this is a good or a bad thing - if it doesn't work, then we shouldn't show it. If it's shown, then it should work (even if partially). This is a bug in the product, and I'll file it to the product team for them to fix it.

A possible workaround to use Cosmos DB in PowerApps

As I mentioned before, Microsoft Flow doesn't need the data to be strongly typed for it to use it. So you can, from PowerApps, call a flow that would access the DB (even passing any parameters that you need), and then return the result to PowerApps. There are a couple of blog posts that talk about this scenario: https://flow.microsoft.com/en-us/blog/return-data-to-powerapps/ and https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/; if you are interested in this workaround you can look at them for guidance on how to implement it.

 

Hope this helps!

Axel666
Level: Powered On

Re: Connecting to Azure Cosmos DB

Hi Carlos,

Thank you so much for your thorough reply. I'll get to work with this new info during the upcoming holidays.

Best regards,
Axel
AxelBA
Level: Powered On

Re: Connecting to Azure Cosmos DB

Now I have had the time to investigate, and I have gotten pretty far. This link got me most of the way: https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/

 

I figured out, that pressing the button updated my collection and that I could see the sollection prior to attaching it to the gallery. Unfortunately the result from my Response body seems to be different when running the PowerApp then when running the flow.

 

This is my setup so far (I also know I ought to do some more errorhandeling, before passing the JSON to the Response):

flob.jpgflowdetail.jpgButton.jpgCollection.jpgIm quite sure something is wrong with my response or with the query in my PowerApp, but what is the error?

Any hint or advice is much appreciated.

AxelBA
Level: Powered On

Re: Connecting to Azure Cosmos DB

Turns out you have to remove and re-add the flow after updating it, or your changes to your flow will not be updated. I can get my table now.

AxelBA
Level: Powered On

Re: Connecting to Azure Cosmos DB

And now: How do I get data from the PowerApp to Cosmos DB?

 

I need to create a Form in PowerApps, taht collects the data I want to submit to the Cosmos DB, but how can I create a Form, that submits the data I enter in it, to a Flow that finally creates the Cosmos DB entry? What Data source should I use for the temporary data? 

Dayananthan
Level: Powered On

Re: Connecting to Azure Cosmos DB

Looking for the same. Let me know once you cracked it. I think we need to use AzureCosmosDB.CreateDocument but looking for some sample codings.
Highlighted
AxelBA
Level: Powered On

Re: Connecting to Azure Cosmos DB

You need to send string to a Flow that then in turn adds the string to the CosmosDB by Flow.