Count Rows with Filter Delegation problem

[asif082]

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.

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

Thanks.

[Pstork1]

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.

[asif082]

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

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

Thanks

[WarrenBelz]

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

[asif082]

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

[WarrenBelz]

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]

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

[WarrenBelz · Accepted solution]

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]

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