cancel
Showing results for
Did you mean:
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 1 Zone 2 Zone 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
Super User III

``````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!
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!

Super User III

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!
5 REPLIES 5
Super User III

``````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!
Frequent Visitor

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

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!

Super User III

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!
Frequent Visitor

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!

Announcements

#### Welcome to the User Group Public Preview

Check out how to claim yours today!

Test your skills now with the Cloud Skill Challenge.

#### Demo Extravaganza is Back!

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

#### 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)