cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

How to get the max value of Sharepoint list column

I have a Sharepoint list (Tabela1) with column Nº de obra, which contains a sequential integer.

 

I'm trying to get the maximum value. Using a text label, I changed the "Text" property to:

 

Max(Tabela1.'Nº de Obra')

 

Which returns the error:

Invalid argument type (Table) shoud be number

 

And then I tried:

Last(Sort(Tabela1;'Nº de Obra';Descending))

 

Which returns the error:

 

Text value expected

 

 

Why? Can anyone help with this?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: How to get the max value of Sharepoint list column

@WebPortal 

I translated your error message for the SORT + LAST method.  In English it says:

 

The requested operation is invalid.
Server Response: The attempted operation is forbidden because it exceeds the list view
threshold imposed by the administrator.

 

This is a problem to be solved 100% on Sharepoint.  The code within PowerApps is good.  Try these suggestions in the link below.  I believe it will fix your error. https://natechamberlain.com/2018/05/21/how-to-resolve-sharepoint-list-view-threshold-error-the-view-...

 

...

 

I suppose MAX is not delegable under Sharepoint even though it is mentioned in the Delegation doucmentation.  How wiered.  That's OK though, I think you can use SORT + LAST once the issue with Sharepoint is resolved.

 

 

View solution in original post

6 REPLIES 6
Highlighted
Super User
Super User

Re: How to get the max value of Sharepoint list column

@WebPortal 

Here's a few ideas.  This one initially did not work beacuse Last returns a record and you need a field text/number instead.  Also, you'll want to get the FIRST record of your table sorted in descending (greatest-to-least) order

 

First(Sort(Tabela1;'Nº de Obra';Descending)).'Nº de Obra'

 

I am not as familiar with the max formula but I think this would work.  Your issue was there should be two parameters (Table, Column) instead of 1 parameter.

 

Max(Tabela1,'Nº de Obra')

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

 

Highlighted
Helper V
Helper V

Re: How to get the max value of Sharepoint list column

Hi @mdevaney 

 

Thank you for your answers!

 

The first returns an error:

"Server error: ... exceeds maximum number of visits"

The second, runs, but only within the delegated rows and it returns:

Delegation warning: ... 'max is not supported...'

So, in essence I need to get the maximum value of a large sharepoint list (around 50k rows) and not just the maximum of the delegated rows.

 

Later, I need to add one to this value and expand the list.

 

How can I do this?

Highlighted
Super User
Super User

Re: How to get the max value of Sharepoint list column

 

@WebPortal 

Could you please provide a screenshot of the full error message shown here?  I am little surprised as I have used SORT + FIRST in many instances.  Maybe the full error message will help me understand.

 

 

"Server error: ... exceeds maximum number of visits"

 

 

For the MAX function, did you combine it with any other code?  The documentation says its delegable... so I am a little confused here https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-aggregates

 

 

Highlighted
Helper V
Helper V

Re: How to get the max value of Sharepoint list column

Hi @mdevaney 

 

Here's the full screen:

image.png

"The requested operation in invalid"

 

This is the max function:

image.png

 

This is the warning message of Max:

image.png

My system is Portuguese, but this one means: "Delegation warning. The detached part of this formula may not function correctly in big datasets. "Max" operation is not supported by this connector." 

Highlighted
Super User
Super User

Re: How to get the max value of Sharepoint list column

@WebPortal 

I translated your error message for the SORT + LAST method.  In English it says:

 

The requested operation is invalid.
Server Response: The attempted operation is forbidden because it exceeds the list view
threshold imposed by the administrator.

 

This is a problem to be solved 100% on Sharepoint.  The code within PowerApps is good.  Try these suggestions in the link below.  I believe it will fix your error. https://natechamberlain.com/2018/05/21/how-to-resolve-sharepoint-list-view-threshold-error-the-view-...

 

...

 

I suppose MAX is not delegable under Sharepoint even though it is mentioned in the Delegation doucmentation.  How wiered.  That's OK though, I think you can use SORT + LAST once the issue with Sharepoint is resolved.

 

 

View solution in original post

Highlighted
Helper V
Helper V

Re: How to get the max value of Sharepoint list column

Hi @mdevaney 

 

I have added an index for column 'Nº de obra' and now the Sort function works!

 

I guess now I can find another function to auto increment this number (I'll open another question 🙂

 

Thanks a lot for helping!

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (4,544)