cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rbunge
Advocate V
Advocate V

Filter by date ONLY for certain status, other status no date filter

SharePoint list has a Status column and a Date column. Canvas app gallery currently displays all items. Would like to filter by Date column (past 7 days) only if Status is equal to one of two values (all other Status values show all dates).

 

Is this possible in-line (no collections)?

 

Only way that comes to mind is creating two collections (one filtered by status excluding the two values, the other filtered by status (only the two values) AND date (past 7 days)), and then combine them into a third collection.

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

Hi @rbunge ,

I must have missed you post. Try the below - this is a logic exercise rather than a coding one. If not exact, you might have a go based on this logic - happy to look at it.

With(
   {
      wDate:
      DateAdd(
         Today(),
         -7,
         Days
      )
   },
   Filter(
      SPListName,
      (
         (
            Status = "Accepted" || 
            Status = "Rejected by GM"
         ) && DateField <= wDate
      ) ||
      DateField > wDate  
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

View solution in original post

@WarrenBelz -

 

AHA! Tweaking yours a bit produced the desired results! Thanks very much! Marking yours as solution.

 

With(
   {
      wDate: 
      DateAdd(
         Today(),
         -7,
         Days
     )
   },
   Filter(
      SPListName,
      (
         (
            Status.Value = "Accepted" || 
            Status.Value = "Rejected by GM"
         ) && Modified >= wDate
      ) || 
      (
         Status.Value <> "Accepted" && 
         Status.Value <>"Rejected by GM"
      )
   )
)

 

Now I have to deal with the pesky delegation...

 

Thanks again for your patience and perseverance... 

View solution in original post

8 REPLIES 8
WarrenBelz
Super User
Super User

Hi @rbunge ,

Maybe something like this - NOTE .Value may be different depending on the Items of your dropdown.

With(
   {
      wDate:
      DateAdd(
         Today(),
         -7,
         Days
      ),
	  wAddDate:
	  DropDown.Selected.Value = "YourStatusValue"
   },
   Filter(
      SPListName,
      (wAddDate && DateField >= wDate && Status = DropDown.Selected.Value) ||
      (!wAddDate && Status = DropDown.Selected.Value)
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

 

WarrenBelz
Super User
Super User

Hi @rbunge ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

rbunge
Advocate V
Advocate V

Morning @WarrenBelz -

 

I'm actually JUST sitting down to wire it in -- apologies, it's been non-stop for a week. Back to you in a bit.

@WarrenBelz - thanks again for your quick response.

 

I implemented your solution but am not seeing the precise desired results. If I select Accepted in the dropdown, I do indeed see only Accepted within the last 7 days, but nothing else. If I select a different status, the items showing are indeed any date.

 

However, I need to see both the Accepted within the past 7 days as well as all other status values regardless of date.

 

More specifically, there are 6 possible status values. In the gallery, I need to see "Accepted" and "Rejected by GM" only if Modified date is within the past 7 days, AND all other statuses regardless of Modified date. Note: also no drop-down needed as this is a fixed filter that will not change.

 

Hope this makes sense... Let me know if you need additional info.

rbunge
Advocate V
Advocate V

@WarrenBelz - just touching base... am I out of luck on this one without using collections?

Hi @rbunge ,

I must have missed you post. Try the below - this is a logic exercise rather than a coding one. If not exact, you might have a go based on this logic - happy to look at it.

With(
   {
      wDate:
      DateAdd(
         Today(),
         -7,
         Days
      )
   },
   Filter(
      SPListName,
      (
         (
            Status = "Accepted" || 
            Status = "Rejected by GM"
         ) && DateField <= wDate
      ) ||
      DateField > wDate  
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

@WarrenBelz -

 

AHA! Tweaking yours a bit produced the desired results! Thanks very much! Marking yours as solution.

 

With(
   {
      wDate: 
      DateAdd(
         Today(),
         -7,
         Days
     )
   },
   Filter(
      SPListName,
      (
         (
            Status.Value = "Accepted" || 
            Status.Value = "Rejected by GM"
         ) && Modified >= wDate
      ) || 
      (
         Status.Value <> "Accepted" && 
         Status.Value <>"Rejected by GM"
      )
   )
)

 

Now I have to deal with the pesky delegation...

 

Thanks again for your patience and perseverance... 

Hi @rbunge ,

Just make the Status equal any of the other four values rather than not equal (which is not Delegable) those two.

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Kudoed Authors
Users online (2,106)