cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PVosEska
Resolver III
Resolver III

Update collection on filtered rows

Hi,

 

Sorry if the answer is on this page, i could not find it.

 

My goal is to update a value in a collection based on if their title is in a second collection.

 

My current formula:

 

 

 

//Search if this employee exist and retrieve ID
Set(
    refProfID,
    LookUp(
        <List>,
        DisplayName = ComboBox1_1.Selected.DisplayName,
        ID
    )
);
//Collect all rows where the employeeID is the retrieved ID from previous step
ClearCollect(
    refProfSoftware,
    Filter(
        Software,
        employeeID = refProfID
    )
)

 

 

 

that's all going fine,

 

Now i have an already existing collection (softwareCollection) with a column 'selected'(true/false)

I want to change selected to true if the title of that item is in 'softwareCollection'.

 

I tried a ForAll, but i can't go further than comparing one string to a table, and not every string to that table...

 

Hope someone can help!

2 ACCEPTED SOLUTIONS

Accepted Solutions
RandyHayes
Super User
Super User

@PVosEska 

 

You might want to consider using the UpdateIf function instead.

 

Your question was a bit confusing as you mentioned you wanted to update the softwareCollection and change selected to true if the Title was in softwareCollection.  I believe you meant that the refProfSoftware collection you are creating in your formula is the one that you want to compare to.

 

So, based on that assumption, your formula would be this:

UpdateIf(softwareCollection, Title in refProfSoftware.Title, {selected:true})

That should do what you are looking for (again, based on my assumption).

 

Also, consider shortening your other formula a bit and not rely on global variables as much (unless you needed the refProfID for something else):

With({refProfID: Lookup(<List>, DisplayName = ComboBox1_1.Selected.DisplayName, ID)},
   //Collect all rows where the employeeID is the retrieved ID from previous step
   ClearCollect(
      refProfSoftware,
      Filter(Software,
             employeeID = refProfID
      )
   )
)

Just a little bonus information...

 

I hope this is helpful for you.

 

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

@PVosEska 

No problem!

 

So, I could actually go on about variables for a bit (and am working on a video about them currently).

In general, I avoid variables as much as possible.  Not because I don't like them, but because they are very hard to maintain and troubleshoot.  AND, so many lose track of the fact that they are "snapshots" not active items - if you change something the variable came from, now you have to put the logic in to refresh it...now you end up with formula after formula with the same logic, and when you need to change it - good luck!  I tend to lean more toward using controls instead. 

That little bit said, there are definitely purposes for them and I use them in a meaningful and sparing way, and pay very close attention to using Global and using Screen Context to help in maintaining.

But, when I have something that I need to use as a breadcrumb for someone else, I always provide an entire screen dedicated to that.  OnStart seems like a good place (and it is, again, in some cases), but it's not the most obvious place for a future designer.  I usually put an APP_INFO screen in all my apps.  On that screen I place controls that provide information for the rest of the app.  If a future designer needs to change something, that's the place to go and it is usually all there.

 

Phew...again with that said, the only reason I mentioned simplifying the formula you had was that the refProfID seemed to only be used for the ClearCollect formula.  And, since it referenced a ComboBox, I would not have assumed it to be in the OnStart.  If that was the case, simplify is good.  If it was needed elsewhere for some purpose then a Global or context variable would be in order.

 

To play on your scenario a bit, in many cases I would put a Label on the APP_INFO screen.  I would call it _APP_Prof_ID and set the Text property to : Lookup(<list>, DisplayName = ComboBox1_1.Selected.DisplayName, ID) (also, I would usually put another label on the screen to indicate that the control was the source of _APP_Prof_ID - for a future designer to recognize quickly)

Then, in the app, when I need the ID, I would just reference _APP_Prof_ID.Text

This way, only ONE formula for it and it is dynamic, meaning that if the ComboBox is changed, I don't have to then have another formula to update the Global Variable because the Label will update automatically.  Controls are Global.

A lot less formula and a lot easier to maintain.

 

Anyway...your mileage will vary.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

5 REPLIES 5
RandyHayes
Super User
Super User

@PVosEska 

 

You might want to consider using the UpdateIf function instead.

 

Your question was a bit confusing as you mentioned you wanted to update the softwareCollection and change selected to true if the Title was in softwareCollection.  I believe you meant that the refProfSoftware collection you are creating in your formula is the one that you want to compare to.

 

So, based on that assumption, your formula would be this:

UpdateIf(softwareCollection, Title in refProfSoftware.Title, {selected:true})

That should do what you are looking for (again, based on my assumption).

 

Also, consider shortening your other formula a bit and not rely on global variables as much (unless you needed the refProfID for something else):

With({refProfID: Lookup(<List>, DisplayName = ComboBox1_1.Selected.DisplayName, ID)},
   //Collect all rows where the employeeID is the retrieved ID from previous step
   ClearCollect(
      refProfSoftware,
      Filter(Software,
             employeeID = refProfID
      )
   )
)

Just a little bonus information...

 

I hope this is helpful for you.

 

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Hi @RandyHayes ,

Thanks for the tip! i looked at it for so long that i forgot UpdateIf() updates multiple rows...

 

I generally use more Global Variables so i can declare them in the onstart, which would help users in the future to also make changes. Not a good direction according to you?

@PVosEska 

No problem!

 

So, I could actually go on about variables for a bit (and am working on a video about them currently).

In general, I avoid variables as much as possible.  Not because I don't like them, but because they are very hard to maintain and troubleshoot.  AND, so many lose track of the fact that they are "snapshots" not active items - if you change something the variable came from, now you have to put the logic in to refresh it...now you end up with formula after formula with the same logic, and when you need to change it - good luck!  I tend to lean more toward using controls instead. 

That little bit said, there are definitely purposes for them and I use them in a meaningful and sparing way, and pay very close attention to using Global and using Screen Context to help in maintaining.

But, when I have something that I need to use as a breadcrumb for someone else, I always provide an entire screen dedicated to that.  OnStart seems like a good place (and it is, again, in some cases), but it's not the most obvious place for a future designer.  I usually put an APP_INFO screen in all my apps.  On that screen I place controls that provide information for the rest of the app.  If a future designer needs to change something, that's the place to go and it is usually all there.

 

Phew...again with that said, the only reason I mentioned simplifying the formula you had was that the refProfID seemed to only be used for the ClearCollect formula.  And, since it referenced a ComboBox, I would not have assumed it to be in the OnStart.  If that was the case, simplify is good.  If it was needed elsewhere for some purpose then a Global or context variable would be in order.

 

To play on your scenario a bit, in many cases I would put a Label on the APP_INFO screen.  I would call it _APP_Prof_ID and set the Text property to : Lookup(<list>, DisplayName = ComboBox1_1.Selected.DisplayName, ID) (also, I would usually put another label on the screen to indicate that the control was the source of _APP_Prof_ID - for a future designer to recognize quickly)

Then, in the app, when I need the ID, I would just reference _APP_Prof_ID.Text

This way, only ONE formula for it and it is dynamic, meaning that if the ComboBox is changed, I don't have to then have another formula to update the Global Variable because the Label will update automatically.  Controls are Global.

A lot less formula and a lot easier to maintain.

 

Anyway...your mileage will vary.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

@RandyHayes 

Thanks for the detailed explanation! I'd have to see how i would implement this in my apps, since some of the bigger apps have between 10-20 global variables declared OnStart(), so i'd have to see how i would put them all on one infoscreen. 

 

I have heard that the With() function is also calculated faster than a global variable, but i believe the function wasn't available yet when i started out with PowerApps, so i just got used to using global variables.

 

But going through them, most of the global variables i do use, will be used in one place, so i'm gonna start to put those in a With() function instead.

 

Again, thanks for the tip!

@PVosEska 

Sounds good.

 

Yes, you always need to consider the context of your variables.  OnStart is not a bad thing, but lots of variables, in general, is.  It's more maintenance and more "re-snapshotting" (meaning that if the underlying information the variable is based on changes, it's up to you to refresh the variable.  When it's in a control, it's automatic.

 

Also consider that Global variables means just that...they are global to the entire app.  Does it need to be?  If you are using a variable to govern how something appears on one screen, then a Context variable is a better fit (an d "unclutters" your long list of variables to maintain).  If you just need to generate a variable in order to do some other function, then consider the With statement.  Again, reduces the number of variables to maintain and manage.

 

Nothing worse then inheriting someone else's app and trying to fix something that a variable is controlling, only to have to hunt through endless formulas to find it.  The design studio has "limited" ability to help.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (2,774)