Is it possible to easily change data table name in a Power App?

[VictorSotnikov]

I am developing a canvas Power App application. It works with an SQL Server table; let’s call it MyTable. While I am on development stage, I do not want to work with the real MyTable; instead I have created MyTableTemp. My app works with it.

Now let’s suppose that I have finished the application. It is tested; and it is time to switch it from MyTableTemp to MyTable. The question is: is there a simple way to do this? I do not see an easy way; there are many places in the app that refer ‘MyTable’.  Is it possible to create a data source that initially points out to MyTable and later points out to MyTableTemp; and use this data source in the app?

[SebS · Accepted solution]

Hi @VictorSotnikov 

Environment Variables are your Friend.

First create a solution and place all Flows and App to it it will be easier for you to manage life cycle of app that way.

Hope You already split Your work between Development Environment and Production Environment You can include one for Test if you like.

Now create a variable what will control Your Data Source / Database and after that You adding Variables for each table or List You working with.

Just remember If You work with SQL it's different If I good remember You need to look for Data source environment variables so that should be more applying to Your scenario but should work same way I think it's included in Link Below. 

Here is link with more datils

Use environment variables in solutions - Power Apps | Microsoft Learn  

hope that helps

Regards

[VictorSotnikov]

Thank you for the answer.
Could you tell me - is it possible to resolve the problem without creating a Solution? For example - using a Global Variable inside the given App?

[SebS]

Hi

I'm afraid not. You will need to manually remove connections from Test DB and reassign new to Production DB

Regards

[VictorSotnikov]

“You will need to manually remove connections from Test DB and reassign new to Production DB” – let’s suppose that it is OK for me – OK if this affects only one place. The place should be OnStart event of my App.

Taking this inro account – would such solution work?

[SebS]

Hi,

if you planning to have MyTable and MyTableTemp on the same SQL server you can just create variables like Test

Set(varTest, false)

Then in all Galeries or objects what will use MyTable set formula If(!varTest,MyTable,MyTableTemp)

so when You start editing the app you can go to OnStart and change false to true and that will switch the Name of the table but You will need to connect both tables to the app using SQL connector and keep in mind SQL connector is premium so all users using the app will need to pay.

Iforgot this will get more complicated when You start using filters and collections or other staff what will require MyTable you will need to alwyas create additional lines of code to manage MyTableTemp as well

This will become very messy very quick that's why Variables are cleaner and easier to manage

Regards

[VictorSotnikov]

"...that's why Variables are cleaner and easier to manage..." - here you meant Environment variables, didn't you?

[VictorSotnikov]

“…Just remember If You work with SQL it's different If I good remember You need to look for Data source environment variables so that should be more applying to Your scenario…” – I have created a Solution and creating an environment variable. In Data Type -> Data Source I see only Share Point connector; nothing about SQL.

What should I do to use the environment variable in my case?

[SebS]

You are right SQL is not supported, but there are also Connection References

here is the topic that should give You some more clarity SQL is harder to manage due to the requirement for Authentication it looks like for SQL You will not have an easy way to switch as Sharepoint have

https://powerusers.microsoft.com/t5/Building-Power-Apps/Change-Power-Apps-SQL-data-connection-while-...

1. In your Dev PowerApps Workspace, go-to solutions and Create a solution
2. Add a new PowerApp to the solution
3. Open the PowerApp (before adding any data sources) and go to settings and turn on the general feature to automatically create variables when data sources are added.
4. Save the app to the cloud (basically publish)
5. Close the app
6. Reopen the app
7. Add a SQL Server Data Source using SQL Auth (Azure AD Auth while advertised as the best practice, doesn't seem to work in most cases like this and seems to not be ready for prime time use).
   1. You may have to add a single Data source table to your app as well.
8. Publish the app and close
9. There's a solution menu to add additional items. Use this to add a "Data  Source Reference" (not variable) if you don't already see it.
10. Set the reference to use the Data Source that you added (if dev environment then this should point to your dev Azure SQL Database)
11. Click the button at the top solution menu to "Export the Solution" not just the app.
12. It will ask you if you want to publish changes. Select publish and then finish the export
13. It may or may not popup the download but you will so see a zip in your downloads
14. Go to your Test Power Apps Environment and create a SQL connection to your Test Data Source. Use SQL Auth again.
15. Go to your Solutions portion of your Test PowerApps environment and select import solution and import this solution
16. During the import process it should pickup the connection and reference and ask you to tie a Data Source Connection to it. Tie it to your Test Source.
17. You should now have a solution with an app. Open it and it should be pulling from your Test Data Source.
    1. There were some instances where it didn't import saying it didn't have all components and I had to go back to the dev solution and click the button to get all dependencies and re-export. (maybe because I deleted a reference?)
18. The above steps should get you going on a new app. However if you already created an using the recommended Azure AD Auth for the SQL Connector, there seems to be some real issues trying to convert it over. I don't know if it's possible without rebuilding the entire app. I can delete a few of the Datasources and then it just stops removing them or I get failed to delete resource or just a forever spinning circle, or failed to save app. Maybe I'll have to create all the new sources under new name that it gives it (Datasource becomes Datasource1. doesn't seem to be a way to rename them), then hand fix any code reference to the old data source. Then maybe I can delete them. I suspect the deletes don't work because there are too many references to them that it has to figure out and it bogs down the No-Code environment and stops.