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

MySQL ID Field required

Hello, I really hope someone is able to assist me as I have been dealing with this since November 2018. Here is the complete background. When I posted this yesterday for some reason it was marked spam.

 

I have created a some tables within a MySQL DB. Setup the primary key and autoincrement within those tables for the ID fields. Here is an example table:

 

Table Name: tbl_commlog

Fields:

id_commlog, PrimaryKey, Int, AutoIncrement, Unique, Not NULL (Will not let me set to NULL)

comm_type, varchar, NULL

comm_detail, blob, NULL

 

Now in PowerApps I add a form, connect it to the table, add comm_type and comm_detail fields to the form (I have tried with the id_commlog field as well with the same results so no change).

 

I set a button to set the formmode to new.

I set another button to SubmitForm.

 

Now if I click the first button it sets the form to New mode and allows me to input data. Then when I click the Submit button I get an error saying id_commlog: Field Required. 

 

Now, if I change the SubmitForm to Patch, for example:

Patch(
'[dbserv].[tbl_commlog]',
Defaults(
'[dbserv].[tbl_commlog]'
),
{
comm_type: DataCardValue1.Text,
comm_detail: DataCardValue2.Text
}
)

This errors with the exact same thing, id_commlog:Field Required.

 

BUT, if I specify the ID manually as below:

Patch(
'[dbserv].[tbl_commlog]',
Defaults(
'[dbserv].[tbl_commlog]'
),
{
id_commlog: First(
SortByColumns(
'[dbserv].[tbl_commlog]',
"id_commlog",
Descending
).id_commlog+1,
comm_type: DataCardValue1.Text,
comm_detail: DataCardValue2.Text
}
)

Then it works most of the time, sometimes it will error with saying record already created and the submit button must be pressed a second time. This is a major issue, however for the time being this is similar to how I have it setup at the moment to at least get partial functionality. 

 

What I need to do is actually get this to fully work by making powerapps understand that the field is the primary key and it is auto incremented, so it is generated by the server, just as the documentation clearly states even though that is not the case whatsoever. This is extremely frustrating and I'm close to losing my job because I have not been able to fix it yet. 

 

I tried to keep this short and keep the code simple as my apps actual code is extremely complex. Though this exact method is what I am working on verbatum with a test screen that is only seen by me at this time while I attempt to get this working. Any and all assistance is greatly appreciated. Oh BTW I have made attempts at working with MS on this and they just state that this is the expected behavior. THAT IS UNACCEPTABLE, especially when the documentation on Patch CLEARLY states this should work perfectly. No where I have found(forums or documentation and I have read hundreds upon hundreds of pages and articles on this) does it say that you have to manually specify this, and if I absolutely have to specify it manually, how can I get past the whole this record already exists crap. 

8 REPLIES 8
Community Support Team
Community Support Team

Re: MySQL ID Field required

HI @fitbodywrap ,

Could you please share a bit more about the error message within your app?

Do you want to patch new records into your SQL table with the PRIMARY KEY column be generated by your SQL Server and auto-incremented?

Based on the needs that you mentioned, I have made a test on my side, and don't have the issue that you mentioned.

The SQL Syntax to create my SQL Table as below:

create table dbo.TaskLists(
    Id int not null primary key identity(1,1),
    TaskName varchar(20) not null,
    DueDate datetimeoffset not null,
    Executor varchar(10) not null
)

I think there is something wrong with the SQL syntax you provided to create your SQL Table, please consider take a try to modify your SQL syntax as below:

create table tbl_commlog(
      id_commlog int not null primary key identity(1,1),
      comm_type varchar(50)
      comm_detail blob
)

please consider take a try to create your new SQL table within above SQL Syntax, then create a new PowerApps app and add the new created SQL Table as a data source. After that, take a try with the following Patch formula:

Patch(
     '[dbserv].[tbl_commlog]',
      Defaults('[dbserv].[tbl_commlog]'),
      {
      comm_type: DataCardValue1.Text,
      comm_detail: DataCardValue2.Text
      }
)

then check if the issue is solved.

 

Best regards,

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

Re: MySQL ID Field required

Your SQL script is for Microsoft SQL Server, I am not using Microsoft SQL Server, I am using MySQL and per the MySQL Manual for our server version I created the tables just as I have for many years designing PHP Applications and Websites. Perhaps I should have just posted my complete MySQL table script, find it below:

CREATE TABLE tbl_commlog (
  id_epl int(11) NOT NULL AUTO_INCREMENT,
  id_lead int(11) DEFAULT NULL,
  acct_nbr int(11) DEFAULT NULL,
  created_date date DEFAULT NULL,
  created_by varchar(255) DEFAULT NULL,
  subject varchar(255) DEFAULT NULL,
  type varchar(255) DEFAULT NULL,
  details longtext DEFAULT NULL,
  last_mod_by varchar(255) DEFAULT NULL,
  last_mod_date date DEFAULT NULL,
  answered tinyint(4) DEFAULT NULL,
  account_name varchar(255) DEFAULT NULL,
  lead_name varchar(255) DEFAULT NULL,
  comm_source varchar(255) DEFAULT NULL,
  PRIMARY KEY (id_epl)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
AVG_ROW_LENGTH = 0
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci
ROW_FORMAT = DYNAMIC;

Now, for testing purposes I created a MySQL Server using WAMP Server on my local machine (which I have done many time in the past) and setup a test database there with the above table and configuration. Now if I use PHPMyAdmin or MySQL Workbench and do for example this command: 

INSERT INTO tbl_commlog(created_date,created_by,subject,details)
VALUES(NOW(),"Admin","Test","Some Random Details");

This creates the row perfectly and the server generates the number as it should and as expected. Now if I create a new PowerApp, connected it to this new database and table and did as follows:

 

1. Inserted new form named "TestForm"

2. Set forms datasource to 'tbl_commlog'

3. Added the created_date,created_by,subject,details fields to the form with datacardvalue names being: "CreatedDate","CreatedBy","Subject","Details".

4. Set Button1 OnSelect to: 

NewForm(TestForm)

5. Set Button2 OnSelect to:

SubmitForm(TestForm)

6. Set Button3 OnSelect to:

Patch(
   '[dbsys].[tbl_commlog]',
   Defaults(
      '[dbsys].[tbl_commlog]'
   ),
   { 
      created_date: Now(),
      created_by: CreatedBy.Text,
      subject: Subject.Text,
      details: Details.Text
   }
);
ViewForm(TestForm)

Now, I click Button1, this sets the FormMode of TestForm to FormMode.New. I input some data, click Button2 which should submit the data as it is entered. Failed yet again saying id_epl: Field Required. 

So I try clicking Button3 which is configured as you see above to patch the record. AND same exact problem. id_epl: Field Required. 

 

So yet again, if I do what I mentioned in my first post by adding the id_epl field to the form and then in the patch formula I set the field value manually it works perfectly but this is not the solution because consecutive record creations results in "This record has already been created on the server" errors or it will just completely overwrite the data in the database which causes data loss. This is a big problem and there has to be a way to fix this.

fitbodywrap
Level: Powered On

Re: MySQL ID Field required

@v-xida-msft 

So no repsonse then?

JChurch
Level: Power Up

Re: MySQL ID Field required

Running into a similar problem myself.  Were you ever able to find a solution?

 

Thanks.

Joe_100
Level: Powered On

Re: MySQL ID Field required

Also running into the same problem with a DB2 connector, autoincrement/identity field is not recognized and powerapps demands that i give a value for this field but that off course doesn't work as the DB2 will block this action.

 

I have created a ticket at Microsoft but i am not sure if they will pick this up...

 

Have found a workaround tough, by using MS flow and creating a view where i remove the autoincrement field from.

Then i update or create a record through this view with MS flow.

kman42
Level: Powered On

Re: MySQL ID Field required

Same problem. Any updates?

 

 

Joe_100
Level: Powered On

Re: MySQL ID Field required

Hi all,

I have sort of the same problem with a db2 database and a Primary key/ autoincrement.

When adding a record powerapps says that the Primary key/ auto increment is required but of course that is not possible as i Will receive An error from.db2 that this is an auto increment field.

However i have a solution:

Create a view on the table you want to upload your record to.

Remove the autoincrement column.

Next;:

Create a.button, add a Microsoft flow to it and update the view with the fields/variables from your powerapps.

This will work.

Adding records through a view in powerapps doesn't work (maybe for MySQL) i receive An error.that the table is "Read only"

Hope it helps anyone. Also created a ticket at Microsoft but no further response in 1/2 months

mgrimes
Level: Power Up

Re: MySQL ID Field required

We had the same issue and were able to solve it by submitting NULL. Be sure to enter is as an expression and not a value or dynamic content.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 43 members 4,944 guests
Please welcome our newest community members: