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))),
{SubjectSmiley Frustratedubject.Text,DescriptionSmiley Very Happyescription.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!

3 REPLIES 3
RezaDorrani
Level 10

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 8

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))),{SubjectSmiley Frustratedubject.Text,DescriptionSmiley Very Happyscription.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

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? 

 

 

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 229 members 5,080 guests
Please welcome our newest community members: