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
Community Support
Community Support

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

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

Highlighted
ktownshend Helper I
Helper I

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

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
EricLott Resident Rockstar
Resident Rockstar

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

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.

Community Support
Community Support

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

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.
ktownshend Helper I
Helper I

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

@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

Community Support
Community Support

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

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.
ktownshend Helper I
Helper I

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

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 

 
Community Support
Community Support

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

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

Highlighted
ktownshend Helper I
Helper I

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

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

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Power Platform 2020 release wave 1 plan

Power Platform 2020 release wave 1 plan

Features releasing from April 2020 through September 2020

Top Solution Authors
Top Kudoed Authors
Users online (8,740)