cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WillPage
Level 8

Re: Count occurrences of specifc text in a SharePoint column

If you're unsure whether there's any whitespace around the semicolon, split on the semicolon only, like in @PowerAddict's example , then trim the result of Split() with TrimEnds():

AddColumns('SP List', "AddedByCount", CountIf(Split(AddedBy, ";"), TrimEnds(Result) = User().FullName), "DeletedByCount", CountIf(Split(DeletedBy, ";"), TrimEnds(Result) = User().FullName))

 

Community Support Team
Community Support Team

Re: Count occurrences of specifc text in a SharePoint column

Hi @Joe_Fox ,

Could you please share a bit more about your scenario?

Do you want to count the occurrences of specifc text within the 'AddedBy' and 'DeletedBy' Text type columns of your SP List?

 

Based on the needs that you mentioned, I have made a test on my side, please consider take a try with the following workaround:

Create two Label controls, one for 'AddedBy', another one for 'DeletedBy'.

1. For 'AddedBy' label, set the Text property to following:

CountRows(
          Filter(
                  Split(Concat('YourSPList', AddedBy & ";"), ";"),
                  User().FullName in Trim(Result)
          )
)

 

2. For 'DeletedBy' label, set the Text property to following:

CountRows(
          Filter(
                  Split(Concat('YourSPList', DeletedBy & ";"), ";"),
                  User().FullName in Trim(Result)
          )
)

 

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Joe_Fox
Level: Powered On

Re: Count occurrences of specifc text in a SharePoint column

Thanks for everyone's suggestions so far.

 

@WillPageI'm trying to set my PowerApp up as you suggest but having problems.

What I've done is this:

 

  • Added a gallery inside my existing gallery (the new one called 'Gallery1')
  • For the 'Items' property of the new gallery, entered the following (replacing 'SP List' with the name of my list):

AddColumns('SP List', "AddedByCount", CountIf(Split(AddedBy, ";"), Result = User().FullName), "DeletedByCount", CountIf(Split(DeletedBy, ";"), Result = User().FullName))

 

When I check the preview of the data in Gallery1 (by placing my cursor in the formula bar) I can see the 'AddedByCount' column and the correct values, but what I'm struggling with is how to then display this in e.g. a label.

 

From here I get stuck. I'm not sure how to call/use/display the new columns within my PowerApp. I'm also not sure how to create a collection, as you suggest.

 

Hope you can help.

Joe_Fox
Level: Powered On

Re: Count occurrences of specifc text in a SharePoint column

I've got a bit closer to solving this one.

I didn't realise that I needed to add a label inside the new gallery in order to use e.g. ThisItem. AddedByCount

Trouble is, I've now got galleries: One showing a filtered view of the SharePoint list, and another showing the AddedByCount and DeletedByCount values.

 

Maybe a description of the SharePoint list will help. Here goes:

 

The SharePoint list is called 'Calendar template' and is preloaded with every date from 1/1/2019 to 31/12/2025.

Another list, called 'Timesheets' holds timesheet information. When a user adds an entry to the 'Timesheets' list, a Flow runs that adds their name to the corresponding date in the 'AddedBy' column within the 'Calendar template' list. Likewise, when a user deletes an item from the 'Timesheets' list, it adds their name in the 'DeletedBy' column within the 'Calendar template' list. 

 

The purpose of the PowerApp screen that I am building is to provide users with a list of dates that they have yes to add a Timesheet for. It all works well, except for when a user deletes a 'Timesheet' entry.

 

My first gallery has the following 'Items' formula:

 

Filter('Calendar template',Date>=DateAdd(Today(),-30,Days) && Date<=Today() && ((User().FullName in AddedBy) && User().FullName in DeletedBy) || (Date>=DateAdd(Today(),-30,Days) && Date<=Today() && Not(User().FullName in AddedBy)))

 

This shows the current user the last 30 dates (rows) in the 'Calendar template' list that either doesn't have the current user's name in the 'AddedBy' column, or has their name in BOTH the 'AddedBy' column and the 'DeletedBy' column. This is ok in most scenarios except where a user adds a timesheet, then deleted it, then adds it again (where the filter would still fire)

 

I am trying to use the count of AddedBy and DeletedBy so that I can adapt the filter to only show when there are an equal number of occurrences of the current user's name in both the AddedBy and DeletedBy column (or none in the AddedBy at all).

 

It may be a peculiar way of handling this, but I couldn't think of another approach and I believe I'm close to getting it to work.

 

Presumably, what I need to do next is incorporate the following into the first gallery:

 

AddColumns('Calendar template', "AddedByCount", CountIf(Split(AddedBy, ";"), Result = User().FullName), "DeletedByCount", CountIf(Split(DeletedBy, ";"), Result = User().FullName))

 

I've tried putting both in the first gallery's 'Items' formula but just get an error. Is it possible to use both of the formulas shown above together?

Joe_Fox
Level: Powered On

Re: Count occurrences of specifc text in a SharePoint column

I think this is correct:

 

Filter(AddColumns('Calendar template', "AddedByCount", CountIf(Split(AddedBy, ";"), Result = User().FullName), "DeletedByCount", CountIf(Split(DeletedBy, ";"), Result = User().FullName)),Date>=DateAdd(Today(),-30,Days) && Date<=Today() && ((User().FullName in AddedBy) && User().FullName in DeletedBy) || (Date>=DateAdd(Today(),-30,Days) && Date<=Today() && Not(User().FullName in AddedBy)))
WillPage
Level 8

Re: Count occurrences of specifc text in a SharePoint column

There's a slicker way of doing all that which won't cause duplicates in the columns. I'll document on my blog and post the link.
Highlighted
WillPage
Level 8

Re: Count occurrences of specifc text in a SharePoint column

Joe_Fox
Level: Powered On

Re: Count occurrences of specifc text in a SharePoint column

@WillPage  Thanks a lot! I've had a read through and think it's probably a better way to go. I'll have a crack at this tomorrow and see if I can implement it.

 

I did get this way working using the suggestions, but know it's probably not the most elegant solution.

 

Thanks,

@Joe_Fox 

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,984)