cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Cannot filter data table by a longer time period than 7 days

Hi all,

 

I have built a time recording app with a data table on one of the screens. There are various filters on the screen which allows the user to filter the data in the table, including two date pickers which serve as a 'Start Date' and 'End Date' so users can filter by a date range of when a task was started.

 

For some reason I cannot get the data table to show more than 7 days worth of data, even if the date pickers are set to a larger time span than 7 days. Please see the screenshot below along with the items field of my data table:

 

Filter(
AddColumns(AllEntries, "NewTaskTime", Text(Value(TaskDuration), "[$-en-US]#,0")),
(
(IsBlank(NamePicker_1.Selected.UserPrincipalName) || AgentID = NamePicker_1.Selected.UserPrincipalName)

&&
(IsBlank(TeamPicker_1.Selected.Result) || Team = TeamPicker_1.Selected.Result)

&&

(CategoryPicker_1.Selected.Result = "All" || Category = CategoryPicker_1.Selected.Result)

&&

StartTime >= DatePicker2.SelectedDate
&&
StartTime < DateAdd(DatePicker2_1.SelectedDate,1, Days)

&&

Value(TaskDuration) >= 0
))

 

Here is the top of the table: 

freddiejoseph1_0-1634034516002.png

 

Here is the bottom of the table:

freddiejoseph1_1-1634034671731.png

 

You can see that despite filtering until the 11th October, the table stops presenting data on the 8th. Which is 7 days after the start filtering date of the 1st October.

 

Any help would be much appreciated.

 

Thanks,

 

Freddie

6 REPLIES 6
WarrenBelz
Super User
Super User

Hi @Anonymous ,

How many items are in your List and what is your Delegation limit set at? Also, try this - although I am a bit confused with you date logic as the second bit is really the same as the first.

With(
   {
      wDate:
      DateAdd(
         DatePicker2_1.SelectedDate,
         1,
         Days
      )
   },
   Filter(
      AddColumns(
         Sort(
            AllEntries,
            ID,
            Descending
         ),
         "NewTaskTime", 
         Text(
            Value(TaskDuration), 
            "[$-en-US]#,0"
         )
      ),
      (
         IsBlank(NamePicker_1.Selected.UserPrincipalName) || 
         AgentID = NamePicker_1.Selected.UserPrincipalName
      ) &&
      (
         IsBlank(TeamPicker_1.Selected.Result) || 
         Team = TeamPicker_1.Selected.Result
      ) &&
      (
         CategoryPicker_1.Selected.Result = "All" || 
         Category = CategoryPicker_1.Selected.Result
      ) &&
      StartTime >= DatePicker2.SelectedDate &&
      StartTime < wDate
   ) &&
   Value(TaskDuration) >= 0
)

 

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.

 

WarrenBelz
Super User
Super User

Hi @Anonymous ,

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.

Anonymous
Not applicable

Hi Warren,

 

Sorry for the delayed response. I was working on another project. I have tried implementing your solution, and whilst it produces the same functionality as the one I was using previously, the filtering still stops after 8 days. Despite the End Date being further ahead than 8 days. I have attached a screenshot. There are definitely entries for this user between the 12th and 19th October, but for some reason they are not included in the data table even though the date filter should capture these entries, and the filter clearly works because it is filtering something.

 

freddiejoseph1_0-1634545805398.png

 

 

Thanks,

 

Freddie

Hi @Anonymous ,

As you can understand this makes on logical sense unless you have a Delegation limitation which is why I asked how many records you have and suggested you filter with the ID descending to get the most recent record set up to your Delegation limit (which I suggest you set to 2,000 n this case). Can you please think through this logic as clearly something is being overlooked.

 

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.

Anonymous
Not applicable

Hi Warren,

 

I have already set the delegation limit to 2000. But there are far fewer than 2000 rows when filtering for this user for a 2+ week long time period. The count figure in the top right hand corner of my screenshot is the number of rows being returned.

 

Thanks,

 

Freddie

Hi @Anonymous 

It has to be one of your other filters - as a test try it with just the date filter and see what you get.

With(
   {
      wDate:
      DateAdd(
         DatePicker2_1.SelectedDate,
         1,
         Days
      )
   },
   Filter(
      AllEntries,
      StartTime >= DatePicker2.SelectedDate &&
      StartTime < wDate
   ) 
)

Here you are filtering for records where the start time is greater than or equal to the second date picker AND less than the day after the first date picker.

 

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.

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,538)