cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
carem
Helper III
Helper III

Get Auto Increment ID by Default ID Column in Sharepoint List through Power Apps?

I have a Power App form tie to a Sharepoint List "Customers" in Customer Edit Screen and 1 button to Submit the data. 

Screen Shot 2019-11-21 at 5.15.41 PM.png

 

My purpose is to get the auto-increment ID from the Default ID Column in Sharepoint for the newly created items, in a specific format, like "KH000001". So I set the Submit Button property like below:

Submit Button:

- OnSelect:

SubmitForm(frm_EditCustomer_CES);
If(IsBlank(frm_EditCustomer_CES.LastSubmit.'Customer ID'), Patch(Customers, frm_EditCustomer_CES.LastSubmit,{'Customer ID': Concatenate("KH", Right(Text(frm_EditCustomer_CES.LastSubmit.ID + 1000000), 6))}));
Set(gloSelectedItem, frm_EditCustomer_CES.LastSubmit);
Navigate('Customer Details Screen', Fade); 
Set(gloTabs, "Personal")

I success and all items have an ID number.

Customer Details Screen do have a form tie to the same Customers List and have

    Items Property = gloSelectedItem

to display the items just submitted.

But 1 problem occurs, when the Button executes the 4th line, Navigate the Customer Details Screen to review the data I filled, the Customer ID Number is blank at this moment (Although in SharePoint List, the item already has the ID number)

Customer Details Screen:

NOT HAVE ID, BUT SOME INFORMATION IS ALREADY HAVENOT HAVE ID, BUT SOME INFORMATION IS ALREADY HAVE

 

In Sharepoint List:

In sharepoint list, ID Number is alreadyIn sharepoint list, ID Number is already

I have 3 questions:

1. Do the ".LastSubmit" operator only saves the record from SubmitForm() and ignore the Patch() follow this?
2. In my case, what I should do to have the Customer ID number already after submitting for my review in the Customer Details Screen?
3. Do you have any other method more effective than mine to get auto increment ID for the items in Sharepoint List through Power Apps?

1 ACCEPTED SOLUTION

Accepted Solutions
Mr-Dang-MSFT
Power Apps
Power Apps

Hi @carem ,

It sounds like:

You are submitting a form
You expect to see the last submitted item displayed in a display form on another screen.


I have a few suggestions here:

Move these actions into the OnSuccess property of the form so they do not activate if submitting the form failed:

 

If(IsBlank(frm_EditCustomer_CES.LastSubmit.'Customer ID'), Patch(Customers, frm_EditCustomer_CES.LastSubmit,{'Customer ID': Concatenate("KH", Right(Text(frm_EditCustomer_CES.LastSubmit.ID + 1000000), 6))}));
Set(gloSelectedItem, frm_EditCustomer_CES.LastSubmit);
Navigate('Customer Details Screen', Fade); 
Set(gloTabs, "Personal")​


When working with SharePoint, I suggest accompanying any creating and updating of records with a Refresh():

Patch(datasource,...);
Refresh(datasource)​


But the reason your formula is not working is that gloSelectedItem is pointing to the fields written in the form (.LastSubmit). The form did not include the 'Customer Id'--only the Patch did.


As such, the display form is attempting to find a record that looks like what was last submitted, but it doesn't exist because it had been modified.


There's a few ways to go about this.

You can Set() gloSelectedItem to the record that results from the Patch():

If(
    IsBlank(frm_EditCustomer_CES.LastSubmit.'Customer ID'), 
    
    Set(gloSelectedItem, 
        Patch(Customers, 
            frm_EditCustomer_CES.LastSubmit,
            {
                'Customer ID': Concatenate("KH", Right(Text(frm_EditCustomer_CES.LastSubmit.ID + 1000000), 6))
            }
        )
    )
);

 

This means, "If the Customer ID from the form is blank, give it one. Then set the newly updated record as the selected item."

 

Point the DisplayForm control's Item property to a looked up record, rather than a static variable.

 

LookUp(Customers,'Customer ID'=gloSelectedItem.'Customer ID')​


This communicates directly with the connected datasource to show the right record that matches the Id of the selected item. Whereas if you point to the variable, there's a possibility somebody else may have updated the same record, so it would no longer match your gloSelectedItem variable which has remained static.


Let me know if you need clarification on any part.

 

Mr. Dang

View solution in original post

3 REPLIES 3
Mr-Dang-MSFT
Power Apps
Power Apps

Hi @carem ,

It sounds like:

You are submitting a form
You expect to see the last submitted item displayed in a display form on another screen.


I have a few suggestions here:

Move these actions into the OnSuccess property of the form so they do not activate if submitting the form failed:

 

If(IsBlank(frm_EditCustomer_CES.LastSubmit.'Customer ID'), Patch(Customers, frm_EditCustomer_CES.LastSubmit,{'Customer ID': Concatenate("KH", Right(Text(frm_EditCustomer_CES.LastSubmit.ID + 1000000), 6))}));
Set(gloSelectedItem, frm_EditCustomer_CES.LastSubmit);
Navigate('Customer Details Screen', Fade); 
Set(gloTabs, "Personal")​


When working with SharePoint, I suggest accompanying any creating and updating of records with a Refresh():

Patch(datasource,...);
Refresh(datasource)​


But the reason your formula is not working is that gloSelectedItem is pointing to the fields written in the form (.LastSubmit). The form did not include the 'Customer Id'--only the Patch did.


As such, the display form is attempting to find a record that looks like what was last submitted, but it doesn't exist because it had been modified.


There's a few ways to go about this.

You can Set() gloSelectedItem to the record that results from the Patch():

If(
    IsBlank(frm_EditCustomer_CES.LastSubmit.'Customer ID'), 
    
    Set(gloSelectedItem, 
        Patch(Customers, 
            frm_EditCustomer_CES.LastSubmit,
            {
                'Customer ID': Concatenate("KH", Right(Text(frm_EditCustomer_CES.LastSubmit.ID + 1000000), 6))
            }
        )
    )
);

 

This means, "If the Customer ID from the form is blank, give it one. Then set the newly updated record as the selected item."

 

Point the DisplayForm control's Item property to a looked up record, rather than a static variable.

 

LookUp(Customers,'Customer ID'=gloSelectedItem.'Customer ID')​


This communicates directly with the connected datasource to show the right record that matches the Id of the selected item. Whereas if you point to the variable, there's a possibility somebody else may have updated the same record, so it would no longer match your gloSelectedItem variable which has remained static.


Let me know if you need clarification on any part.

 

Mr. Dang

View solution in original post

@Mr-Dang-MSFT , I don't know how to say thank you to you.

But, THANK YOU!!!!

Very detailed, dedicated and sincere.

It's good for newcomers like us to have your help.

We wish you all the best of the MSFS Team with the greatest happiness for your contributions.

Sorry for my bad English.

 

p/s: I guess you are Vietnamese, right?

Welcome to the community, @carem!

And yes, I am Vietnamese and I like cà rem 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,849)