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

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

v-siky-msft
Level 10

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
Skiroy
Level: Powered On

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? 

 

 

Skiroy
Level: Powered On

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

Bump

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

 

 

Skiroy
Level: Powered On

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
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

Power Apps 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

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,527)