cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WebPortal
Level: Powered On

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
mdevaney
Level 8

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
mdevaney
Level 8

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."

 

WebPortal
Level: Powered On

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
mdevaney
Level 8

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

 

 

WebPortal
Level: Powered On

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." 

mdevaney
Level 8

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

WebPortal
Level: Powered On

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 188 members 6,033 guests
Please welcome our newest community members: