cancel
Showing results for
Did you mean:
Helper III

## Calculate zipcode ranges available

Hi guys,

I am making an app where admins can enter postcoderanges between 1000-9999. To make it easy for them i would like to show them which ranges are still available.

In the screenshot below you can see there is a range available between 2531 and 2599. This is the only range that is still available between 1000-9999.

How would i be able to display it like this:

Available zip ranges:

• 2531 - 2599

The datasource "Custom Entity [dbo].[PostcodeRange]" looks like this:
(There are duplicates because companies are related to the ranges)

I would need some kind of forall loop that checks for available ranges between start and end zip codes.

Best Regards,

Anthony

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

Hi @AnthonyDo ,

I did a test for you.

I assume the range is 1-20.

Add a button control and set its onselect property to:

``ClearCollect(Col,{Value:1});Clear(Col);ForAll(LIST168 As A,If(CountRows(Filter(ForAll(LIST168 As B,If(A.PostcodeStart>B.PostcodeStart&&A.PostcodeStart<B.PostcodeEinde,{Value2:1})),!IsBlank(Value2)))=0,Collect(Col,{Value:A.PostcodeStart})));ForAll(LIST168 As A,If(CountRows(Filter(ForAll(LIST168 As B,If(A.PostcodeEinde>B.PostcodeStart&&A.PostcodeEinde<B.PostcodeEinde,{Value2:1})),!IsBlank(Value2)))=0,Collect(Col,{Value:A.PostcodeEinde})))``

Add a combo box control and set its Items property to:

``SortByColumns(Distinct(Col,Value),"Result")``

The result is as follows:

Best Regards,

Wearsky

2 REPLIES 2
Super User

How do you determine if a range is available?

If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.
Community Support

Hi @AnthonyDo ,

I did a test for you.

I assume the range is 1-20.

Add a button control and set its onselect property to:

``ClearCollect(Col,{Value:1});Clear(Col);ForAll(LIST168 As A,If(CountRows(Filter(ForAll(LIST168 As B,If(A.PostcodeStart>B.PostcodeStart&&A.PostcodeStart<B.PostcodeEinde,{Value2:1})),!IsBlank(Value2)))=0,Collect(Col,{Value:A.PostcodeStart})));ForAll(LIST168 As A,If(CountRows(Filter(ForAll(LIST168 As B,If(A.PostcodeEinde>B.PostcodeStart&&A.PostcodeEinde<B.PostcodeEinde,{Value2:1})),!IsBlank(Value2)))=0,Collect(Col,{Value:A.PostcodeEinde})))``

Add a combo box control and set its Items property to:

``SortByColumns(Distinct(Col,Value),"Result")``

The result is as follows:

Best Regards,

Wearsky

Announcements

#### Launching new user group features

Learn how to create your own user groups today!

#### Community & How To Videos

Check out the new Power Platform Community Connections gallery!

#### Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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