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

[ktownshend]

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 

[Anonymous]

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]

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,

[ktownshend]

@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

[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,

[ktownshend]

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 

[v-xida-msft · Accepted solution]

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,

[ktownshend · Accepted solution]

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