Unanswered

Changed a collection patch to a named formula

(0) Share

Report

Posted on by EpicTriffid

364

I'm looking at the new Named formulas functionality and was wondering if its feasible to turn the below Path/Collect into a named formula:

Patch(
 ClearCollect(accessList,
 Switch(true,
 CountRows(Filter(adminList, currentUserEmail = emailID)) > 0,
 Sort(preAccessList, SortID),
 CountRows(Filter(Sort(preAccessList, SortID), currentUserEmail = IDEmail)) > 0,
 Filter(Sort(preAccessList, SortID), currentUserEmail = IDEmail),
 Sort(preAccessList, SortID))), 
 Defaults(accessList), 
 {Department: "Please Choose Your Department...", SortID:0}));

Categories:

Experimental features

I have the same question (0)

All responses (6)

Answers (0)

Sort by

JorisdG Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Patch is a side-effect function so you can't use it in a named formula. We're working on a new feature called user defined functions, those will allow both behavior (side effect) and non-behavior functions to be created.

That said, I see several filter function calls in here. Depending on how often those are used in other areas those could be great candidates for named formulas. For example Filter(adminList, currentUserEmail = emailID) from the naming seems like it wouldn't likely to change much if at all, so that could be a named formula optimization, and Sort( preAccessList, SortID) is in there twice, so that could be a named formula.

Was this reply helpful? Yes No
EpicTriffid 364 on at

Like (0)

Report
Copy link

Link copied!
Hi @JorisdG

Thanks for the reply. Not sure I understand what a side-effect function is, but I'll go with the expert advice.

Another question, however, coming from what you said about moving some of the code into a named formula, is that I have now done that as below:

preAccessList = Filter('Non-Pay App Access List', SortID <> 0); sortedPreAccessList = Sort(preAccessList, SortID);
(I need preAccessList elsewhere, without the sort)

But having done that, I now get the following delegation error on the code:

And it says that CountRows cannot be delegated by this connector. Is this the delegation bug with named formulas that I have been reading about, or have i misunderstood something here?

Was this reply helpful? Yes No
EpicTriffid 364 on at

Like (0)

Report
Copy link

Link copied!

Does anyone have any answer to my last question?

Was this reply helpful? Yes No
JorisdG Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

sortedPreAccessList is already a local table (it's a filtered&sorted local copy of 'Non-Pay App Access List') so can't be delegated.

A "side-effect" or "behavior" function is a function that changes something. Set a variable, manipulate something in a collection or table (patch, collect, etc.). You can't put formulas using those type of functions in for example a text property. You can only use them in behavior properties like an OnSelect of a button for example.

Was this reply helpful? Yes No
EpicTriffid 364 on at

Like (0)

Report
Copy link

Link copied!

Hi @JorisdG

Thanks for the reply! I was under the impression that delegation was not applicable to local collections and tables? Or is it that the Named Formula is essentially always "connected" to the data source?

Was this reply helpful? Yes No
JorisdG Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

The named formula is local. I think the message you're seeing shouldn't appear. I'll check with the team here.

Was this reply helpful? Yes No