cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ktownshend
Helper I
Helper I

Help with updating one list with info from a field in another list

I have just recently moved several access tables to SharePoint lists.  The relationships have been built and that seems to be working well.

 

Where I've run into an issue is, when the Main table was brought up, there were 121 missing entries (they were deleted on purpose) so the standard auto numbering is off.  I've created a column and called it Corrective Action ID and right now users are manually entering the next number in line.

 

What I'd like to do is take that number and have it auto-increment. 

 

I've created a secondary table that holds just the last number used. I'd like to make a flow that will look at the current in-use number, add 1 to it and update the secondary table with it so that the next time a user enters data, it will look at the last number in the secondary file add 1 and that becomes the new number and updates the secondary file.

 

If you would like more info, I'm happy to provide screenshots and stuff.

 

Thank you 

2 ACCEPTED SOLUTIONS

Accepted Solutions

Hi @ktownshend ,

Do you custom a form app in your SP List using PowerApps?

 

Please consider modify your formula as below:

If(
   SharePointForm1.Mode = FormMode.New, 
   First(Sort(CorrectiveAction, 'Corrective Action ID', Descending)).'Corrective Action ID' + 1, 
   ThisItem.'Corrective Action ID'
)

or

If(
   IsBlank(SharePointIntegration.Selected) || IsEmpty(SharePointIntegration.Selected), 
   First(Sort(CorrectiveAction, 'Corrective Action ID', Descending)).'Corrective Action ID' + 1, 
   ThisItem.'Corrective Action ID'
)

 

Set the OnNew property of SharePointIntegration to following:

Refresh('SP List');
NewForm(SharePointForm1)

set the OnEdit property of SharePointIntegration to following:

Refresh('SP List');
EditForm(SharePointForm1)

set the OnView property of SharePointIntegration to following:

Refresh('SP List');
ViewForm(SharePointForm1)

 

Please consider take a try with above solution, 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.

View solution in original post

Thank you so much.  I started over from scratch and used the following:

 

If(
   SharePointForm1.Mode = FormMode.New, 
   First(Sort(CorrectiveAction, 'Corrective Action ID', Descending)).'Corrective Action ID' + 1, 
   ThisItem.'Corrective Action ID'
)

 This worked and I'm having my end users test it out now 

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

SharePoint Lists have ID columns with unique incrementing values. Is there a reason you can't use that as the ID?

 

You could also setup a calculated column that is equal to the row's SharePoint ID plus whatever the difference is with your other ID.

v-xida-msft
Community Support
Community Support

Hi @ktownshend ,

Could you please share a bit more about the "Corrective Action ID" column in your Main List? Is it a Text type column?

Do you want to store the latest number value within your second List?

 

Based on the needs that you mentioned, I think it is not necessary to set up second List to store the Latest number value. I have made a test on my side, please take a try with the following workaround:

1. Generate an app based on your Main List. It would generate three screens -- BrowseScreen, DetailScreen and EditScreen.

2. Within the Edit screen, enable "Corrective Action ID" field within the Edit form. Set the Default property of the "Corrective Action ID" Text Box to following:

If(
   EditForm1.Mode = FormMode.New,
   Value(First(Sort('Main List', Value('Corrective Action ID'), Descending)).'Corrective Action ID') + 1,
   ThisItem.'Corrective Action ID'
)

Then when you add a new record using the generated app, it would auto-populate the "Corrective Action ID" field with latest value.

 

More details about the Sort function, please check the following article:

Sort function

 

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 

 

Thank you for getting back to me, the field is a number field, not text, does that make a difference?

 

I was not able to get this to work as expected.  It returned either 630 or 2, neither of which are the next number I need, it should be 1025.

 

Not sure what I'm doing wrong.

 

Any help or point in the right direction is appreciated.

 

Thank you 

 

 

v-xida-msft

Hi @ktownshend ,

Could you please share a screenshot about the Edit form in your app?

 

I think the data type of the  "Corrective Action ID" field do not make a difference in the returned result.

 

Within the Edit screen, enable "Corrective Action ID" field within the Edit form. Set the Default property of the "Corrective Action ID" Text Box to following:

If(
   EditForm1.Mode = FormMode.New,
   First(SortByColumns('Main List', 'Corrective Action ID', Descending)).'Corrective Action ID' + 1,
   ThisItem.'Corrective Action ID'
)

Note: The above "Auto-Increment" functionality would work only when you create a new record (Edit form is in New mode).

 

In addition, you could also consider take a try with the following workaround:

Set the Default property of the "Corrective Action ID" Text Box to following:

 

If(
   EditForm1.Mode = FormMode.New,
   Max('Main List', 'Corrective Action ID') + 1,
   ThisItem.'Corrective Action ID'
)

 

Please check if you have typed above proper formula within the Default property of the "Corrective Action ID" Text Box within the Edit form.

 

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.

Cool, I will try those suggestions as well.

 

Here's what I have typed up:

 

If(SharePointForm1.Mode = New, Value(First(Sort(CorrectiveAction, Value('Corrective Action ID'), Descending)).'Corrective Action ID') + 1, ThisItem.'Corrective Action ID' )

 

I am getting this error: The function Value has some invalid arguments 

 

Hi @ktownshend ,

Do you custom a form app in your SP List using PowerApps?

 

Please consider modify your formula as below:

If(
   SharePointForm1.Mode = FormMode.New, 
   First(Sort(CorrectiveAction, 'Corrective Action ID', Descending)).'Corrective Action ID' + 1, 
   ThisItem.'Corrective Action ID'
)

or

If(
   IsBlank(SharePointIntegration.Selected) || IsEmpty(SharePointIntegration.Selected), 
   First(Sort(CorrectiveAction, 'Corrective Action ID', Descending)).'Corrective Action ID' + 1, 
   ThisItem.'Corrective Action ID'
)

 

Set the OnNew property of SharePointIntegration to following:

Refresh('SP List');
NewForm(SharePointForm1)

set the OnEdit property of SharePointIntegration to following:

Refresh('SP List');
EditForm(SharePointForm1)

set the OnView property of SharePointIntegration to following:

Refresh('SP List');
ViewForm(SharePointForm1)

 

Please consider take a try with above solution, 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.

Thank you so much.  I started over from scratch and used the following:

 

If(
   SharePointForm1.Mode = FormMode.New, 
   First(Sort(CorrectiveAction, 'Corrective Action ID', Descending)).'Corrective Action ID' + 1, 
   ThisItem.'Corrective Action ID'
)

 This worked and I'm having my end users test it out now 

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 (2,264)