I have a couple of SharePoint column which via various Flows (or should I say Power Automates!?) get their values appended by a user's display name (e.g. John Doe) and a semi-colon (;).
The SharePoint columns are called 'AddedBy' and 'DeletedBy'.
I want to be able to count the number of occurrences of the current user's display name (separately) in each of the two columns.
I am using User().FullName to get the current user's display name (e.g. John Doe) but I am having trouble counting how many times this occurs in the two columns i.e. 'AddedBy' and 'DeletedBy'.
Let's say I'm logged in as John Doe and the data in the SharePoint list looks like this:
|John Doe; John Doe||John Doe|
I want to be able to count how many times John Doe exists in each column, so I should get the result 2 (AddedBy), 1 (DeletedBy). Happy for the values to be shown via a label if that's easiest.
The reason for this is that once I have the count values, I can say that if the AddedBy count value is greater than the DeletedBy count value, do something else (yet to be determined).
Bit of a strange request but hoping someone can help!
I'm not sure how to implement this as the source is the SharePoint columns (AddedBy and DeletedBy).
Would I need to 'collect' the data in each of these fields somehow before using the CountIf(AddedList, AddedBy = "John Doe")?
Thanks again Hardit,
Yes, both columns are just SharePoint text columns.
Still not quite sure which element of the PowerApp I should be putting the SharePoint list name.
I have the two labels for the count results, but not sure where the collection would be.
This gets me close to what I'm after, but this counts all of the occurrences of the entire list, whereas what I want to do is count the occurrences in a particular row of the list. For example, if the list looks like this:
|John Doe;John Doe;John Doe;||John Doe|
|John Doe||John Doe;John Doe;|
...in the PowerApp the gallery would show AddedBy values of 1,3 and 1 and DeletedBy values of 1,1 and 3.
Presuming I'm logged in as John Doe, the label with 'Text' property of CountIf('SharePoint List Name', AddedBy = User().FullName) would just show 2 and 2, which isn't quite what I want. I think I need to use a 'contains' rather than an = in the condition too, which I'm struggling with!
I think your solution is to use AddColumns(), CountIf() and Split() together for this. I'll make an assumption that your SP List name is 'SP List'.
AddColumns('SP List', "AddedByCount", CountIf(Split(AddedBy, "; "), Result = User().FullName), "DeletedByCount", CountIf(Split(DeletedBy, "; "), Result = User().FullName))
What this is doing is adding a a pair of columns to the table in the items property of your control (gallery, data table, combo box etc). Each column is the count of the number of columns in the table formed by splitting the AddedBy and DeletedBy strings on "; " into tables where the row matches the full name of the current user.
I haven't tested this, I just typed it out into the forum so E&OE etc. I can't remember of the name of the column that comes out of the Split() function is Result or Value, but the formula above I've used Result.
These new columns will be available just like any other i.e. ThisItem.AddedByCount in a gallery, but only in the control for which the Items property contains this formula. To use it across several controls, create a collection when the app loads or some other way, like OnVisible, OnSelect or OnChange of a screen or control.
Be aware that AddColumns() isn't delegable (despite there being no warning about it) so if your data source exceeds the limit set in the app's advanced properties, you will need to pull a delegable query into a collection then run the AddColumns on that.
To provide an explanation, Split function breaks a string into a table of substrings using the specified delimiter. So in the case of John Doe;John Doe the count of the table will be 2 rows.
The only modification to the formula will be:
AddColumns('SP List', "AddedByCount", CountIf(Split(AddedBy, ";"), Result = User().FullName), "DeletedByCount", CountIf(Split(DeletedBy, ";"), Result = User().FullName))
Fill out a quick form to claim your user group badge now!
Find out where you can attend!
Features releasing from October 2019 through March 2020
The largest Power BI, Power Platform, and Data conference in New Zealand