I have created a filtered list for my application that has rows where a person's name may appear in more than one row. I am interested in creating a table that shows two columns - the name and the number of times is appears in the filtered list.
Thanks!
Jeff
Hi @Jeff-2aca thanks for posting your question! There may be a different options for you depending on your set up. Based on what I know so far it sounds like you want show your app users the count of times person X appears in a table based on specific criteria. The example I’ll walk through is based on a set up where you have a table called ‘User_names’ and another table that contains notes from each person. In this example, I’m specifically interested in filtering for the amount of times the user input notes. Here is a screenshot of my tables:
The key piece in my example is creating a relationship between the tables. This is important to help you filter appropriately. Check out this article for more details on this.
Creating table relationships allows me to filter for how many times a user created notes. Here is a screenshot of the filter made on the ‘User_names’ table:
This filter is filtering my ‘User_notes’ table where the [Notes] column is not blank and was created by the user in THISROW().
Once you have this set up on the tables, you can create a list in your app that displays this tables data.
Let me know if this helps! If your set up is different, let me know the details and we can work through it together 
. In case you have a setup where you are looking to conduct the row count in app, the the function ROWS() may be useful for you.
Happy Building 
Thank you for your help! What I have done is created a filter (hidden in the app) of a table. I then want to present to the user in a list, a summary of this filter. I thought ROWS() might get it done, but unfortunately I have been able to make that work. My current list would show something like the following, which reflects the individual rows in the hidden filter:
Name Count
ALICE 1
BOB 1
ALICE 1
I would like it to query the filter to show:
ALICE 2
BOB 1
Thanks!
Jeff
Great to hear you were already looking into ROWS() . Let's see if we can have it work for you.
If you created a list in your app that shows the individual rows that you want to count, we can utilize that filter in the ROWS() function. I'll continue with the example in my account.
Here is my list that filters for rows where users added notes:
This is what shows in my app:
To show the summary view that you're looking for I will create a list that pulls from my 'User_names' table which lists the names:
Then to show the count for each name, I added another data cell where I utilized the FILTER used above. Two pieces I want to note here are that I updated the FILTER formula so that it would count rows for the correct 'Name' and table relationships is what allows me to do this.
This is what it will look like in my app:
Let me know if that works 
This is helpful. Thank you. Last question - how would you filter out the names that have 0 records listed?
Thank you!
Hi @Jeff-2aca, trying updating the list source in the summary view with something like this:
=FILTER(User_names,"User_names[Has notes]<>%",0)
Let us know if this helps!
More specifically - in your example above, how would you filter the list so that the row with John (with count 0) does not show in the list?
Thanks!
I'd utilize the filter @aj suggested to clean up what shows in my example list . Knowing that you'd like to filter out any rows that show 0, I'd recommend following the steps I shared in my first reply on this post.
Creating summary tables, like in the screenshot below, will allow us to create the proper view you are looking for and actually will simplify your app building process:
With this summary table that I can reference, I'd then utilize the filter AJ mentioned in app builder:
Then I can update my list to show contents of the two columns rather than having to write a formula:
As a result, my list will look like this:
Let us know if this helps, thanks @Jeff-2aca !
I created the table using the ROWS() construct in one of the earlier responses, and it works great.
That said, it does its job and returns the count. I was hoping to further filter so that rows with a ROWS() count of 0 do not show to keep the list shorter.
Thanks!
Hi @Jeff-2aca with how the app interacts with your tables and how list object pulls information, creating the summary table would be the route you'd want to take. This will allow you to filter out those 0 counts in your list. Creating the summary table will allow you to set a filter in your app that is based on filters already created in the table . Give that a try to see it in action . Hope that works for you!
I may be missing something, but my ROWS() count is applied against a filter that basically joins another filter and a table, so I'm not sure creating a summary table will get the done here.
Hi @Jeff-2aca the solution of using a summary table is separate from the solution of using ROWS().
The summary table solution will allow you to show a count for each name and filter out any count that is 0. The ROWS() solution will allow you to show a count for each name but does not filter out rows that have a 0 count in our example. I'd suggest trying to take the summary table solution in order to show users the data that you want while keeping the list shorter (filtering out anything with a 0 count).
Hope that helps clarify the solutions
OK. Here is that I have done....!
, so now what I would like to do is summarize the count of players in the Pairings_Players column on a list in the app. In this example, Steve show a count of 2 and the others would show a count of 1.Moderator note: Edited out personal information from image.
Thanks for sharing this example @Jeff-2aca! With what you have have set up in the ‘Players’ table it looks like each row is unique to each player's name. To follow data modeling best practices, it’s best to keep data related to the unique identifier of each row and in this case it would be for each player. Using row 2 in the ‘Players’ table for example, instead of filtering for data in the ‘Results’ table that do not include Jeff you can instead filter for the items that include Jeff.
For example you can create a column that filters for FILTER(Results,“Results[EventGroup] IN [Pairings][EventGroup] AND Results[Player]=THISROW()”). Note: I used the filter based in your screenshot, but you may need to update this based on your goals. This will then result in this table showing a summary column for each player based on the filter that is set. Then from there in your app you can create a list that pulls in data from this column to show your app users the number of times that player is listed in the ‘Results’ table.
If your data is organized cleanly in this way and assuming that the table the 'Pairings' column is referring to creates the relation between players and different event groups, you can allow your users to search for a specific event group and then see the count for each user
Here is the screenshot of the table my ‘Pairings’ column is referring to:
With the relationship created between the event group and my players I can create a list that shows all the players related to an event group my app users select and each players count (note: in my filter I added the criteria to only show me rows where count is greater than 0 in the case any rows have 0 count).
Screenshot of the running app below:
Sorry - I probably wasn't clear. I've built all of the filters that I need. What I am trying to do is show a table (on a screen in the app for a given player, based on $[InputRow]), that summarizes a count of the names in the the Pairings_Players column, like the one shown on the screen. Effectively I am trying to sum up the number of times a given player has played with other players in the group. Using the example in my post above it would show:
Steve 2
Peter 1
Mark 1
Shaen 1
Waddy 1
Gage 1
So - basically - taking the data in the Pairings_Players cell and counting it up in a list in the app
Appreciate your help!
Hi @Jeff-2aca no worries, thanks for clarifying . With what you have set up, creating a list view in app builder will only show the value of the filter in the ‘Pairings_Players’ column. For your example with Jeff it will display 7. If you decide to take the ROWS() path, you will end up with the same issue that we saw in my example earlier in the thread where it also shows rows 0.
To be able to sum the number of times a given player has played with other players, you will still want to set that up in a table. With the information you provided, here is what I would suggest. Create a player match table where column 1 is row linked to your ‘Player’ table and column 2 is also row linked to your ‘Player’ table. Example screenshot:
From there, I will mirror a similar set up from your ‘Player’ table with the ‘Parings’ and ‘Pairings_Players’ column. Note: there are slight changes to the formula
Screenshot of my ‘Pairings’ formula:
Screenshot of my ‘Pairings_Players’ formula:
My ‘Pairings_Players’ column now provides me the information I need. It tells me how many times player Jeff has been paired with player Mark, player Andy, player Baxter, etc.
Now that tables are set I can go into app builder and create a list that displays a list of all players Jeff has played with and how many times:
Screenshot of the app:
Hope this helps, happy holidays!
Thank you! This works well. The only challenge is to build/manage the player_match table dynamically as new players are added or removed from master list. As it stands, I can manually manage that table. Effectively it looks like you are creating a 2x2 matrix to match each player with one another. We have 29 players in the group, so will end up with a table that is 812 rows (29*28).
Happy to hear this works @Jeff-2aca . If you learn a technique that works for you with managing the player_match table, let me know. There is always room to learn and grow, so I love knowledge sharing .
One thought I had, that could be an option for you to explore, is to create an app experience for your users where they can update your player_match table for you. For example, if your users are able to see the players in their group you could create a button that appears if a player is a "new player" from their perspective (not yet created in the player_match table). Then in the app screen, you could give them the option to "add the player" and create an automation on that button click to add the appropriate information on your player_match table.
I'm sure you may have creative ideas on this, but thought I'd share.
Happy building
