cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fernanda
Helper III
Helper III

Traffic light filter with dates

Hello!

I have been trying to work out a filter based of delivery dates. I wanted to use the icons you see in the image, but I cannot make it work at all, so I think I am doing it all wrong. I am working on a Sharepoint database

The idea, was that once i pressed the icons, it would filter my gallery according to the following (it should work independently and along with the other filters higlighted):

Green: Over 5 days to due date

Yellow: 5 or less days to due date

Red: 0 dates to due date.

 

Fernanda_0-1619472000208.png

 

I was trying with using a filter for eachone with DateAdd, but that didn't work. Also tried a different approach with CountRows, but I got delegation warnings. I read about conditional formatting, but that (according to me) shouldn't apply. Also read about Collections, but I am not familiar at all with them, so I don't really know how to even give it a try.

 

Is it possible to achieve what I am aiming for? or my approach is completely wrong??

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Fernanda ,'

Dangers of free-typing - I have fixed my original post - yours needs to be

With(
   {
      wList: 
      Sort(
         'Prueba Informe Seg v2';
         'Entrega (modificable)';
         Descending
      )
   };
   Filter(
      wList;
      'Entrega (modificable)' >= 
      DateAdd(
         Today();
         varDays;
         Days
      )
   )
)

 

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.

View solution in original post

7 REPLIES 7
WarrenBelz
Super User III
Super User III

Hi @Fernanda ,

Firstly set a Variable on each of the icons (Green Example)

UpdateContext({varDays:5})

then your filter - note the top bit is to address the Delegation issue (date queries are not Delegable)  - if you can return less then 2,000 records in the latest due dates (and you have your limit set to this), this will work

With(
   {
      wList:
      Sort(
         YourListName,
         DueDate,
         Descending
      )
   },
   Filter(
      wList,
      DueDate>=
      DateAdd(
         Today(),
         varDays,
         Days
      )
   )
)

I have also assumed the DueDate field name here.

 

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.

Hey @WarrenBelz 

Thank you for your help 🙂

I tried your solution, but it is not working (maybe I did something wrong)

 

I set the variable and wrote in the gallery Items.

My database is 'Prueba Informe Seg v2' and my date column is 'Entrega (modificable)'

 

With(
{
wList: Sort(
'Prueba Informe Seg v2';
'Entrega (modificable)';
Descending
)
};
Filter(
wList;
'Entrega (modificable)' >= DateAdd(
'Entrega (modificable)';
varDays;
Days
)
)
)

 

But, when i run the app, and I try only the green solution, it only brings back items with empty dates.

I tried the yellow one, which I set to 4 in the variable (wasn't sure how to write between 0 to 5 days) and that didn't work either. 

When I tried the red solution (variable set to 0) it brought back all the items in the database, only sorted in a descending order.

Did I do something wrong?

Missed to say that my database has no more than 1500 items and it is already set to 2000

Hi @Fernanda ,'

Dangers of free-typing - I have fixed my original post - yours needs to be

With(
   {
      wList: 
      Sort(
         'Prueba Informe Seg v2';
         'Entrega (modificable)';
         Descending
      )
   };
   Filter(
      wList;
      'Entrega (modificable)' >= 
      DateAdd(
         Today();
         varDays;
         Days
      )
   )
)

 

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.

View solution in original post

Hey!

Just tested it and it worked for the green solution!!

If I want to add the others, should I keep the same variable name for all and just change the numbers? or create a whole new logical test and variable?

Hi @Fernanda ,

Just change the numbers

 

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.

sorry to bother again!!

I was doing all the needed tests, but it is not quite working well...

 

For example:

  1. for green solution, column >= was ok, but for the red solution, it needs to be column < today(). I tried adding this logic with || and also tried &&, but it keeps giving me back all of the results instead of making the correct filter. Also tried changing the name of the variable trying to set a difference, but didnt work either.
  2. I definitively don't know how to add a range of numbers in the variable for the yellow solution (range of 1-4 days before expiration date)
  3. I have tried to apply a reset so it would clean up and start fresh, but it won't let me

Also tried changing the With solution to just filter, and it pretty much happened the same

 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

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

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (22,573)