cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AnthonyDo
Helper III
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. 

 

AnthonyDo_0-1630655147332.png

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)

AnthonyDo_1-1630655312678.png

 

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
v-xiaochen-msft
Community Support
Community Support

Hi @AnthonyDo ,

 

I did a test for you.

vxiaochenmsft_0-1631246291614.png

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:

vxiaochenmsft_1-1631246410450.png

 

Best Regards,

Wearsky

 

 

View solution in original post

2 REPLIES 2
JR-BejeweledOne
Super User
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.
v-xiaochen-msft
Community Support
Community Support

Hi @AnthonyDo ,

 

I did a test for you.

vxiaochenmsft_0-1631246291614.png

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:

vxiaochenmsft_1-1631246410450.png

 

Best Regards,

Wearsky

 

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

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)