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

Transitioning from Excel to Sharepoint (Max function not working)

Hello, 

 

I'm working on a project where a user creates a ticket using the following formula: 

 

If(IsBlank(Subject.Text) || IsBlank(Description.Text) ,
UpdateContext({msg_visible:true}),
SubmitForm(NewTicketForm2);
UpdateContext({
New:
Patch('ZZZ.Tickets',LookUp('ZZZ.Tickets',ID=Text(Max('ZZZ.Tickets',ID))),
{Subject:Subject.Text,Description:Description.Text,AssignedTo:'next team'.Text,DateAssignedTo:'today'.Text})}))

 

I was using this formula fine when I had my data source in excel, but due to needing to have multiple users have access to the raw data, I'm needing to move this to Sharepoint to promote broader access to the app. I know (Max) isn't delegable, but what are some suggestions so the same functionality would be present (i.e., look up highest ticket ID, and patch the +1. 

 

Thanks!

6 REPLIES 6
Highlighted
Dual Super User
Dual Super User

Re: Transitioning from Excel to Sharepoint (Max function not working)

Hi @Skiroy 

 

Assuming every ticket that is created will have a ticked ID set, you can read the latest created item in the tickets list

 

Last(Sort(ZZZ.Tickets,Created,Descending)).ID

will give u the last ID

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Highlighted
Community Support
Community Support

Re: Transitioning from Excel to Sharepoint (Max function not working)

Hi @Skiroy ,

 

I want to confirm that whether the ‘ID’ in your formula is the ID Column(Auto-number) in SP list or your custom column. What is data type of “ID”?

Do you want to find out the maximum ID record and avoid the delegation warning of Max function?

 

If the Data type of ID is Number column, you can use SortByColumns function to Descending by ID Column which is delegable. So that you can use First function to get the record with maximum ID.

Part of your formula can be modified as below:

Patch('ZZZ.Tickets',LookUp('ZZZ.Tickets',First(SortByColumns('ZZZ.Tickets',"ID",Descending))),{Subject:Subject.Text,Description:Dscription.Text,AssignedTo:'next team'.Text,DateAssignedTo:'today'.Text})

To look up highest ticket ID, and patch the +1. You can refer to the following:

Patch('ZZZ.Tickets',LookUp('ZZZ.Tickets',First(SortByColumns('ZZZ.Tickets',"ID",Descending))),{ID: First(SortByColumns('ZZZ.Tickets',"ID",Descending)).ID+1})

If the Data type of ID is Text column, I think it is hard to sort by text, because it is ordered by the letter.

So I suggest you can add a Number Column based on “ID” when you move data to SP, and follow the above workaround to get the maximum ID.

 

Best regards,
Sik

Highlighted
Helper I
Helper I

Re: Transitioning from Excel to Sharepoint (Max function not working)

Hi @v-siky-msft@RezaDorrani

 

I tried both of your work arounds and I this on select got lit up like it was the 25th of December. 

Previously when I had this patch working to Excel, the formula was 

If(IsBlank(Subject.Text) || IsBlank(Description.Text) ,UpdateContext({msg_visible : true}),
SubmitForm(NewTicketForm2);
UpdateContext({New : Patch(Tickets,LookUp(Tickets,ID=Text(Max(Tickets,ID))),
{Subject : Subject.Text,Description : Description.Text})}))

 

and now to transitioning over to Sharepoint we're at quare one. 

 

Using the workaround in the picture below, I'm now getting errors (Patch invalid, lookup invalid) Delegation warning regarding if the set gets too large it might not work correctly, Incorrect type (record) - looking for boolian instead, and apparently my AssignedTo column doesn't exist, which I can verify is very much in existence. 

 

Capture.PNG

 

 

Capture.PNG

 

Thoughts? 

 

 

Highlighted
Helper I
Helper I

Re: Transitioning from Excel to Sharepoint (Max function not working)

Bump

Highlighted
Dual Super User
Dual Super User

Re: Transitioning from Excel to Sharepoint (Max function not working)

@Skiroy 

 

you are looking to update the ID column

ID is internal column in sharepoint whihc is read only and cannot be updated - it would automcatically be the latest ID available

 

secondly,

when patching 

patch(datasurce, secondparam - this can just be your form.LastSubmit

where form - is the name of the form control on your screen

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 

Highlighted
Helper I
Helper I

Re: Transitioning from Excel to Sharepoint (Max function not working)

@RezaDorrani

 

Hey Reza, 

 

Thanks for the response. Yes, there is the typical ID column that every sharepoint list has, and maybe I should have named my ID field (not the sharepoint ID) a bit better. Lets call this "Ticket ID". 

When someone clicks the button, I need the onsubmit to look at the "Ticket ID" column and patch a new row with +1 whatever the highest "Ticket ID" was. I tried the Lookup patch above, but I see that lookup will start bogging down the app once it's looking over 500 records, which this app will have well over 500 Tickets. One of my fears would be that there are multiple people submitting tickets at the same time, and there could be some strange crossover if the submittal was happening at the same time. 

 

 

Helpful resources

Announcements
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

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

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,984)