cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rtang1
Frequent Visitor

Create Records with Auto-Increment-ID in a PowerApps

My Power Apps connected to Oracle data source with the PK "ID int GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY". However, in Power Apps patch function, it required me to fill the auto generated field and not allow me to save.Capture.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @rtang1 ,

Have you taken a try to re-create a new table using above syntax I provided?

 

If the issue still exists, please consider remove the Identity property from the PK "ID" column in your Oracle Table, instead, you could generate the ID value within your app.

Please consider take a try with the alternative solution I provided above, then generate a "ID" column value within your app, then write back the generated ID value to your Oracle Table.

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/AutoGen-Mandatory-DB2-field-blockin...

Within your Edit form, set the Default property of the ID field Text Box to following:

If(
   EditForm1.Mode = FormMode.New,
   Last('YourOracleTable').ID + 1,    // Generate a ID value when you add a new record using the app
   ThisItem.ID
)

Set the OnSelect property of the "+" button to following (click it to navigate to Edit screen):

Refresh('YourOracleTable');
NewForm(EditForm1);
Navigate(EditScreen1)

 

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.

View solution in original post

5 REPLIES 5
v-xida-msft
Community Support
Community Support

Hi @rtang1 ,

Based on the PK "ID" declaration syntax that you mentioned, I think there is something wrong with it. When you set a IDENTIFY column with BY DEFAULT in your Oracle table, you could provide a value for this IDENTIFY column manually, rather than force the Oracle system to generate a vlaue for this column.

 

On your side, you should declare the PK "ID" using "ALWAYS AS IDENTITY" syntax. Please consider declare the PK "ID" column using the following syntax:

ID int GENERATED ALWAYS AS IDENTITY PRIMARY KEY

On your side, please consider re-create a new SQL Table or alter your existing table using above syntax, then re-create a new connection to your modified table, then try the Patch function again, check if the issue is solved.

More details about creating Auto-generated Identify column in your Oracle table, please refer to the following article:

https://www.arungudelli.com/tutorial/oracle/auto-increment-identity-column-in-oracle-table-primary-k...

 

If the issue still exists, please check and see if the alternaitve solution I mentioned below would help in your scenario:

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/AutoGen-Mandatory-DB2-field-blockin...

 

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.

@v-xida-msft  I tried below and same error occurred. 

 

 

ID int GENERATED ALWAYS AS IDENTITY PRIMARY KEY

 

 

ID int GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY

 

 

Does it means I have to generate the ID from the Power Apps instead of using Oracle database?

image.png

Hi @rtang1 ,

Have you taken a try to re-create a new table using above syntax I provided?

 

If the issue still exists, please consider remove the Identity property from the PK "ID" column in your Oracle Table, instead, you could generate the ID value within your app.

Please consider take a try with the alternative solution I provided above, then generate a "ID" column value within your app, then write back the generated ID value to your Oracle Table.

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/AutoGen-Mandatory-DB2-field-blockin...

Within your Edit form, set the Default property of the ID field Text Box to following:

If(
   EditForm1.Mode = FormMode.New,
   Last('YourOracleTable').ID + 1,    // Generate a ID value when you add a new record using the app
   ThisItem.ID
)

Set the OnSelect property of the "+" button to following (click it to navigate to Edit screen):

Refresh('YourOracleTable');
NewForm(EditForm1);
Navigate(EditScreen1)

 

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.
sakshi20tiwari
New Member

Hi, I am new to power Apps development. I need help in one of the scenario where i need to generate autogenerated number.

The Scenario is , when i click on a button it will navigate to next page and this next page contains a submit form and has 5 fields among those one is "Auto generated number" field , current date and time field and creator name(In this case my name ). So for this auto generated number there are some conditions:

1) it contains the current year and a unique number . Suppose my last auto generated number was 22-003 , where 22 is current running year(2022-> 22) and a unique number , so if a new from will appear on a button click this auto generated number should be 22-004.

2) If suppose , the current year (2022 ) is going to end and new year (2023)is going to start, so for this new year , the auto generated number should be 23-001 for the first entry.

 

Please help. Thanks.

aaaa3
Helper IV
Helper IV

I am using sharepoint and powerapp. I created a shopping cart app on powerapp and after i click to send order, I want to generate a  unique id (which is in sharepoint list) which can be used as a reference to find out tracking status of the parcel. What should I do?

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (7,844)