cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
asif082
Helper II
Helper II

Count Rows with Filter Delegation problem

Hi Guys,
I am having issue with count rows delegation and I need to fix it before the total record goes beyond 2000 records. I am using below countRows formula. What I am trying below is getting count of record based on Year(date of application) and adding + 1 to last record when new record is created.

 

asif082_1-1641866551275.png

 

 

 Does, anyone know the workaround please I need this desperately. 

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @asif082 ,

For the first example

'SW ApplicationNumber': 
Text(Year(Now()) & "-" & 
With(
   {
      wList:
      Sort(
         SW_Details,
         'Date Application received',
         Descending
      )
   },
   If(
      Left(
         First(wList).'SW ApplicationNumber',
         4
      ) = Text(Year(Today())),
      Text(
         CountRows(
            Filter(
               wList,
               Year('Date Application received') = Year(Today())
            )
         ) + 1,
         "00000"
      ),
      "1"
   )
)

and the second

'SW ApplicationNumber': 
Text(Year(Now()) & "-" & 
With(
   {
      wList:
      Sort(
         SW_Details,
         'Date Application received',
         Descending
      )
   },
   With(
      {wLastNo:Last(wList).'SW ApplicationNumber'},
	  If(
          Left(
             wLastNo,
             4
          ) = Text(Year(Today())),
         Text(
            Value(
               Right(
                  wLastNo, 
                  Len(wLastNo) - 5
               )
            ) + 1,
            "00000"
         ),
         "1"
      )
   )
)

 

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

8 REPLIES 8
Pstork1
Dual Super User
Dual Super User

I think the only way to do this will be to construct a starting date of "01/01/(year)" and an ending date of "12/31/(year)" and then filter on Greater than or equal to and less than or equal to.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
asif082
Helper II
Helper II

Thanks for this Pstork1. I have change it to below but still no luck.

asif082_0-1641873225918.png

 

I have change application date to string and doing greater than eql against selected date.

 

Thanks

WarrenBelz
Super User
Super User

Hi @asif082 ,

Another idea as you are always dealing with the current year, this will give you up to 2,000 in the year. CountRows is simply not Delegable (nor is Year). You will also not get a Delegation warning here, but if the current year goes over 2,000 records, your count will be wrong.

'SW ApplicationNumber': 
Text(Year(Now()) & "-" & 
With(
   {
      wList:
      Sort(
         SW_Details,
         'Date Application received',
         Descending
      )
   },
   Text(
      CountRows(
         Filter(
            wList,
            Year('Date Application received') = Year(Today())
         )
      ) + 1,
      "00000"
   )
)

Or you could grab the last app no field entered, get the number part and add 1

'SW ApplicationNumber': 
Text(Year(Now()) & "-" & 
With(
   {
      wList:
      Sort(
         SW_Details,
         'Date Application received',
         Descending
      )
   },
   With(
      {wLastNo:Last(wList).'SW ApplicationNumber'},
      Text(
         Value(
            Right(
               wLastNo, 
               Len(wLastNo) - 5
            )
         ) + 1,
         "00000"
      )
   )
)

 

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

Thanks Warren, really appreciate the help here. One of the requirement is after the year end the number will be reset e.g. if year 2022 end with 2022-99912 then 2023 will start from 2023-00001 so I guess solution 1 will work in this scenario? 

 

Thanks

Hi @asif082 ,

The second one should and if you manually enter the first one for the year in the first, it will also. Auto-changeover is possible, but a whole new level of complexity,

 

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

asif082
Helper II
Helper II

Hi Warren,

Yeah it needs to auto reset after year ends. In my original example it was possible until i found it cannot goes beyond 2000 records. Do you have any solution how can I auto reset it each year without using year() since its not delegable.

Thanks

Hi @asif082 ,

For the first example

'SW ApplicationNumber': 
Text(Year(Now()) & "-" & 
With(
   {
      wList:
      Sort(
         SW_Details,
         'Date Application received',
         Descending
      )
   },
   If(
      Left(
         First(wList).'SW ApplicationNumber',
         4
      ) = Text(Year(Today())),
      Text(
         CountRows(
            Filter(
               wList,
               Year('Date Application received') = Year(Today())
            )
         ) + 1,
         "00000"
      ),
      "1"
   )
)

and the second

'SW ApplicationNumber': 
Text(Year(Now()) & "-" & 
With(
   {
      wList:
      Sort(
         SW_Details,
         'Date Application received',
         Descending
      )
   },
   With(
      {wLastNo:Last(wList).'SW ApplicationNumber'},
	  If(
          Left(
             wLastNo,
             4
          ) = Text(Year(Today())),
         Text(
            Value(
               Right(
                  wLastNo, 
                  Len(wLastNo) - 5
               )
            ) + 1,
            "00000"
         ),
         "1"
      )
   )
)

 

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

asif082
Helper II
Helper II

Thanks Warren you a legend. Appreciate the help into this.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

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.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (2,087)