cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MikaelJ
Frequent Visitor

Filters in Collection - Finding a value

Greetings,

 

Ok, let's try to explain this case. 

I am trying to find the corresponding weight in a Collection that I have (as below). 

 

Weight: Price level if you wish. 

Zone: The price for the zone 

( Example: 8 kg have different prices depending on what zone is chosen)

 

I have a input where I fill in 'My Weight' which should then find the corresponding row in the Collection. 

Problem is, 'My Weight' will have to be able to handle decimals and I would like the system round up to the next Price level.

(Example: 'My Weight' = 8.6 kg rounds up to 9 )

 

After that I would like to lookup the price for the zone that I have filled into 'My Zone'.

 

Weight(kg)Zone 1Zone 2Zone 3
8.0   
8.5   
9.0   
9.5   
10.0   
11.0   
12.0   

 

I've been trying out First() and Filter() but I cannot get it to work properly and my brain is fried for today so I figured I give the forums a shot.

 

Any suggestions would be very appreciated.

3 ACCEPTED SOLUTIONS

Accepted Solutions
RandyHayes
Super User III
Super User III

@MikaelJ 

Your formula would be this:

With({_weight: RoundUp(Value(youInputControlName.Text), 1)},
    LookUp(Sort(yourCollection, Weight), Weight>=_weight)
)

This will return the matching record from the collection.  You can then access the Zone 1, 2, or 3 column from that record for the value you want.

 

NOTE: Since you data has things like 8.0 and 8.5, rounding to zero decimals is not valid.  You'd have to round to 1 decimal place.  The problem with that is, 8.6 rounded up to 1 decimal place is still 8.6, which would never match.  So the real rounding is done in the LookUp.  There we find the record where the weight is equal to or greater than the weight in a table sorted by weight.   This will perform the rounding for you.  

The original rounding in the With scoped variable is only to round up the initial value to 1 decimal...it's really not even needed, but I added to demonstrate.

 

I hope this is helpful for you.

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

View solution in original post

MikaelJ
Frequent Visitor

Hi, I didn't get the formula you suggested to work.

 

The formula below did work. It's a little bit of what I tried first and a little bit of what you suggested @RandyHayes 

First(Filter(Price_list, Value(KG) >= My_weight)

 

Thanks for helping me solve this!

 

View solution in original post

RandyHayes
Super User III
Super User III

@MikaelJ 

Glad it's working.  However, you should always avoid using First(Filter - this is the same as LookUp, but with a lot more performance overhead.

So, LookUp(Price_list, Value(KG) >= My_weight) is equivalent to your formula without the extra overhead.

 

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

View solution in original post

5 REPLIES 5
RandyHayes
Super User III
Super User III

@MikaelJ 

Your formula would be this:

With({_weight: RoundUp(Value(youInputControlName.Text), 1)},
    LookUp(Sort(yourCollection, Weight), Weight>=_weight)
)

This will return the matching record from the collection.  You can then access the Zone 1, 2, or 3 column from that record for the value you want.

 

NOTE: Since you data has things like 8.0 and 8.5, rounding to zero decimals is not valid.  You'd have to round to 1 decimal place.  The problem with that is, 8.6 rounded up to 1 decimal place is still 8.6, which would never match.  So the real rounding is done in the LookUp.  There we find the record where the weight is equal to or greater than the weight in a table sorted by weight.   This will perform the rounding for you.  

The original rounding in the With scoped variable is only to round up the initial value to 1 decimal...it's really not even needed, but I added to demonstrate.

 

I hope this is helpful for you.

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

View solution in original post

@RandyHayes 

 

Thanks for the tips. I'll have a look at this later this afternoon and get revert if I can get this solution working.

MikaelJ
Frequent Visitor

Hi, I didn't get the formula you suggested to work.

 

The formula below did work. It's a little bit of what I tried first and a little bit of what you suggested @RandyHayes 

First(Filter(Price_list, Value(KG) >= My_weight)

 

Thanks for helping me solve this!

 

View solution in original post

RandyHayes
Super User III
Super User III

@MikaelJ 

Glad it's working.  However, you should always avoid using First(Filter - this is the same as LookUp, but with a lot more performance overhead.

So, LookUp(Price_list, Value(KG) >= My_weight) is equivalent to your formula without the extra overhead.

 

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

View solution in original post

@RandyHayes 

That's cool. That fixed another problem I had in the App. I couldn't use the value that came out of the First-Filter-solution but now I can.

 

Again, thanks!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (63,978)