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

Write Data to a SQL Table from PowerApps

I am trying to write the following fields to a SQL table from PowerApps (the column name for the SQL field - followed by the formula for PowerApps data I am trying to capture):

 

1. UserId - User().Email

2. FullName - User().FullName

3. AppName - "Organization Connection" (this one is a static string)

 

I want these three fields to be input into my SQL table when the app is opened, so I would like to write the formula onVisible for the first screen on the app. The following is what I tried:

 

Patch('[dbo].[APP_USERS]',Defaults('[dbo].[APP_USERS]]'),{UserId: User().Email, FullName: User().FullName, Application: "Test Payer Guidelines"})

 

However, this did not work and I keep getting the following error:

"First argument of 'Patch' should be a collection" and the first field that I underlined was in red squiggles.

 

I also tried the following and got the same error:

Patch('[dbo].[APP_USERS]',Defaults('[dbo].[APP_USERS]'),{Application: "Test Payer Guidelines"})

 

Note: I am not trying to update a specific record that already exists in the SQL table, I am trying to write a new record to this table and it is not a collection. Or is there a way to collect it and then write the collection to the sql table?

Open to suggestions on different ways to do this.

Thank you!!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Write Data to a SQL Table from PowerApps

Hi @PAB,

 

I suspect the issue here should be mostly related to the SQL Server connection.

On first loading, I think the App is trying to initialize the connection used in this App, so for the formula, we could first check if the table is empty, if not, update it with new record.

If(!IsEmpty('[dbo].[APP_USERS]'),
Patch('[dbo].[APP_USERS]',
          Defaults('[dbo].[APP_USERS]]'),
           {UserId: User().Email, 
            FullName: User().FullName, 
            Application: "Test Payer Guidelines"}
         )
)

Besides, when using Patch() with SQL Table, please have the Primary Key (If it has not been configured to auto-Generated from the Server side ) involved in the Records field, otherwise, it will rise another error message tolding missing required fields.

 

Check to see if this would make it work.

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PAB
Level: Powered On

Re: Write Data to a SQL Table from PowerApps

Yes, I was able to resolve the issue by adding a primary key to the table. I added this in the create statement in SQL and once I did that I was able to use the statement. I hope this helps with your issue!

7 REPLIES 7
Community Support Team
Community Support Team

Re: Write Data to a SQL Table from PowerApps

Hi @PAB,

 

I suspect the issue here should be mostly related to the SQL Server connection.

On first loading, I think the App is trying to initialize the connection used in this App, so for the formula, we could first check if the table is empty, if not, update it with new record.

If(!IsEmpty('[dbo].[APP_USERS]'),
Patch('[dbo].[APP_USERS]',
          Defaults('[dbo].[APP_USERS]]'),
           {UserId: User().Email, 
            FullName: User().FullName, 
            Application: "Test Payer Guidelines"}
         )
)

Besides, when using Patch() with SQL Table, please have the Primary Key (If it has not been configured to auto-Generated from the Server side ) involved in the Records field, otherwise, it will rise another error message tolding missing required fields.

 

Check to see if this would make it work.

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PAB
Level: Powered On

Re: Write Data to a SQL Table from PowerApps

I still get the same error, stating that "The first argument of 'Patch' should be a collection." in regards to the underlined portion of the statement.

 

(!IsEmpty('[dbo].[APP_USER_LOG]'), Patch('[dbo].[APP_USER_LOG]', Defaults('[dbo].[APP_USER_LOG]'), {UserId: User().Email,  FullName: User().FullName, Application: "Test Payer Guidelines"}))

philashby
Level 8

Re: Write Data to a SQL Table from PowerApps

I'm having the same issue.  Did you manage to resolve this?

PAB
Level: Powered On

Re: Write Data to a SQL Table from PowerApps

Yes, I was able to resolve the issue by adding a primary key to the table. I added this in the create statement in SQL and once I did that I was able to use the statement. I hope this helps with your issue!

philashby
Level 8

Re: Write Data to a SQL Table from PowerApps

Perfect! You're a star!  Thankyou!

Anonymous
Not applicable

Re: Write Data to a SQL Table from PowerApps

Yo tengo el mismo problema, donde debo agregar la llave primaria

Adjunto error de PowerApps

Adjunto creacion de mi tabla en SQl

PAB
Level: Powered On

Re: Write Data to a SQL Table from PowerApps

@Anonymous it appears you do not have a primary key in your table in sql.

 

For example, you can use the following statement to modify your table to add a primary key to the id column:

ALTER TABLE dbo.FDETAIL

ADD CONSTRAINT id_pk PRIMARY KEY (id);

 

Once you have a primary key in the table you should be able to write to it.