cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Filter Gallery by Date Range, then Change Date Range

Hello, 

 

I'm looking a starting point for a search query for the following scenario: 

 

  • PowerApps Gallery with Sharepoint List as a Data Source
  • Need to pull all the rows added between 1/1/18 and 6/30/18 that have <employee_id> column in common
  • The challenge is, after 6/30/18, I need a way for the query to then only pull rows added between 7/1/18 to 12/31/18 that have <employee_id> column in common
  • This cycle will repeat year in, year out

1. What is a good PowerApps query to get me started

2. How can I make the query adaptable? Basically, on 7/1/18 the query should return 0 results until new records start coming in. 

 

Thank you very much, 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Filter Gallery by Date Range, then Change Date Range

If i understand correctly, you want o split the year into 2 halves, and only report on the sales up to today in the half of the year that you are currently in.

 

You could create a new column in your share point list that is either "1" or "2"

Use a formula like this in the sharepoint column:

If(or((text(DATE,"mm")="01",text(DATE,"mm")="02",text(DATE,"mm")="03",text(DATE,"mm")="04",text(DATE,"mm")="05",text(DATE,"mm"))="06","1","2")

 

Then in powerapps you can filter by "1" or "2", the current year, and employee id.

View solution in original post

Highlighted
Super User
Super User

Re: Filter Gallery by Date Range, then Change Date Range

This is a great lead, thank you @Anonymous. Since this is my first time using a Calculated Sharepoint column, I need a little more clarification if you can. 

 

I'll add a "period" column to the Sharepoint list to represent which period of the year (1st or 2nd) the record was submitted. The formula will be: 

 

 

If(or((text(DATE,"mm")="01",text(DATE,"mm")="02",text(DATE,"mm")="03",text(DATE,"mm")="04",text(DATE,"mm")="05",text(DATE,"mm"))="06","1","2")

 

I'm having trouble understanding which parts of your formula are the columns to look at. Is "text" or DATE the column names to evaluate in the formula?

 

Thanks again!

View solution in original post

4 REPLIES 4
Highlighted
Anonymous
Not applicable

Re: Filter Gallery by Date Range, then Change Date Range

If i understand correctly, you want o split the year into 2 halves, and only report on the sales up to today in the half of the year that you are currently in.

 

You could create a new column in your share point list that is either "1" or "2"

Use a formula like this in the sharepoint column:

If(or((text(DATE,"mm")="01",text(DATE,"mm")="02",text(DATE,"mm")="03",text(DATE,"mm")="04",text(DATE,"mm")="05",text(DATE,"mm"))="06","1","2")

 

Then in powerapps you can filter by "1" or "2", the current year, and employee id.

View solution in original post

Highlighted
Super User
Super User

Re: Filter Gallery by Date Range, then Change Date Range

This is a great lead, thank you @Anonymous. Since this is my first time using a Calculated Sharepoint column, I need a little more clarification if you can. 

 

I'll add a "period" column to the Sharepoint list to represent which period of the year (1st or 2nd) the record was submitted. The formula will be: 

 

 

If(or((text(DATE,"mm")="01",text(DATE,"mm")="02",text(DATE,"mm")="03",text(DATE,"mm")="04",text(DATE,"mm")="05",text(DATE,"mm"))="06","1","2")

 

I'm having trouble understanding which parts of your formula are the columns to look at. Is "text" or DATE the column names to evaluate in the formula?

 

Thanks again!

View solution in original post

Highlighted
Super User
Super User

Re: Filter Gallery by Date Range, then Change Date Range

@Anonymous, figured it out! There was an extra parenthesis in your original formula.

 

Here is the sharepoint formula that I used:

 

=IF(
OR(
TEXT([date],"mm")="01",
TEXT([date],"mm")="02",
TEXT([date],"mm")="03",
TEXT([date],"mm")="04",
TEXT([date],"mm")="05",
TEXT([date],"mm")="06"),"1","2")

 

Now I have my "period" column... time to try combining this with the [employee_number] column to get the correct results. Thanks again for your insights. 

Highlighted
Super User II
Super User II

Re: Filter Gallery by Date Range, then Change Date Range

Hello everyone,

 

This might not be entirely related, but also queries to date columns in SP are not delegatable (nor is calculated columns in SP), so if you have to use SharePoint for the data, the other option would be to use Flow to fetch the data and return it to PowerApps. Flow can use ODATA queries to get to the correct data and then possibly manipulate or join the data with other services before returning it to PowerApps. I love this approach and it has come in extremely handy in the past. Some might say that using a flow for retrieving data might feel 'expensive' but it does allow for many different functional and security options going forward.


See below demo what can be achieved with this. Please see below and let us know if you have any questions or suggestions:

https://youtu.be/m_j_xN7-LjU

 

Twitter: @DavesTechTips

YouTube: https://www.youtube.com/user/dawidvanheerden
**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,913)