Unanswered

How to Make Azure SQL DB Schema Compatible with Powerapps?

(0) Share

Report

Posted on by mogulman52

I'm just getting started migrating some Salesforce entities to Azure SQL Database. I couldn't find any guidelines for making a schema compatible with Powerapps. Is there one? I want to make sure the schema is compatible with Powerapps. I show the partial schema from the Client (Account) and Contact table. Key items:

1. Identity column.
2. Set the default value for most varchar columns to blank to avoid nulls.

3. Boolean columns (Checkbox) use bit type.

4. Percent and Real types use int type.

5. Use Date type for dates.

6. Lookup tables such as CompanySector use id's for reference.

Any suggestions? Thanks

Categories:

Building Power Apps

CreateTables.zip

I have the same question (0)

All responses (5)

Answers (0)

GertVonck 35 on at

Like (1)

Report

Basically if you setup an Azure SQL DB, your data (through tables and views) will be compatible with PowerApps through the SQL Server connector.

Was this reply helpful? Yes No
v-monli-msft on at

Like (1)

Report

Hi @mogulman52,

PowerApps get the columns as long as you connected Azure SQL to PowerApps. I would like to suggest that you could create your first app by generating an app automatically. Click New > File > Start with your data > New connection > SQL Server:

Some documentations about SQL and PowerApps for your reference:

https://baizini-it.com/blog/index.php/2017/09/26/powerapps-and-azure-sql-database-101/

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/connections/connection-azure-sqldatabase

Regards,

Mona

Was this reply helpful? Yes No
mogulman 764 on at

Like (0)

Report

I think I have this somewhat understood right now. I migrated the Account (Client) and Contact entities over to an On-premise SQL Server. I setup the gateway to use it. It is easier to use the on-premise SS during development. You have to have the identity column. I was also able to use lookup tables for dropdown boxes. I didn't use views but I will for Gallery and Detail view in the future. In the edit screen it was a little tricky to get dropdown boxes working. I had to create a global var to hold value of dropdown. Then assign the value in the field to the dropdown value. I used the automated phone layout to test but the plan is to have a desktop version. I added ROWVERSION (timestamp) to tables. I heard this may be needed. Long way to go but I'm excited to tackle it.

Thanks for the input.

Was this reply helpful? Yes No
Shanescows 2,218 Most Valuable Professional on at

Like (0)

Report

Why did you have to use a variable with dropdown? I am guessing if you tell me what you were trying I can get you a cleaner solution. 🙂

Was this reply helpful? Yes No
mogulman 764 on at

Like (0)

Report

As an example I reference the ClientStatus table from Client. A ClientStatusID = 2 has ClientStatus = Current Client. So I have a dropdown box I overlay on the ClientStatus field in Client. I set the values for the dropdown using the ClientStatus table. I set the selected value of the dropdown using a lookup and I use the ClientStatus field (2) in the Client table so dropdown reads Current Client. Works great, no problem. Now if I want to change the value of ClientStatus to 3. I have the dropdown setting the field and field setting the dropdown. Powerapps doesn't like that. I used a global variable to get around problem.

Was this reply helpful? Yes No