Transitioning from Excel to Sharepoint (Max function not working)

(0) Share

Report

Posted on by NK-11091709-0

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!

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

RezaDorrani 12,143 on at

Like (0)

Report

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

Was this reply helpful? Yes No
v-siky-msft on at

Like (0)

Report
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

Was this reply helpful? Yes No
NK-11091709-0 39 on at

Like (0)

Report

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.

Thoughts?

Was this reply helpful? Yes No
NK-11091709-0 39 on at

Like (0)

Report

Bump

Was this reply helpful? Yes No
RezaDorrani 12,143 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
NK-11091709-0 39 on at

Like (0)

Report

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

Was this reply helpful? Yes No