Create Records with Auto-Increment-ID in a PowerApps

[rtang1]

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.

[v-xida-msft]

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,

[rtang1]

@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?

[v-xida-msft · Accepted solution]

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,

[Anonymous]

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.

[Anonymous]

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?

[luissimoespt]

What if you are not using Forms? What would be the approach?