How to get a 'vote' count from the data in a table cell

[Brad Johnson]

I want to setup a product roadmap section where individual pages can be created, one per suggested change, and users can vote for a feature and a report lists the features sorted in descending order of votes so it shows from most popular to least popular.

I tried using Page Properties and Page Properties Report, including the 'Likes' metadata and the Likes appear great in the Page Properties Report but I can't sort on Likes. This would have been great as it doesn't involves Users editing a page in order to vote. The downside is without analytics we couldn't see who voted (which might be important).

I can add a Votes (Name) and Votes (Count) to the table on each page and tell users they have to add their name and increment the Count and then sort the report by Votes (Count) but that's a bit bleh.

I could just have the Votes (Name) in each table and maybe use Table Transformer to count the names (using SQL and/or regex) and add a new column as part of the report but that's beyond my capabilities. I would prefer the name to be a @mention but also OK if they have to type it in as text if there is a solution here.

I would love to hear any ideas on how to achieve this expanding either on my ideas above or any new idea.

[Katerina Kovriga {Stiltsoft} · Accepted solution]

Hi @Brad Johnson ,

If you need help with the Table Transformer macro (or maybe the Pivot Table macro will suit you), you may give us a manually created table with dummy data that looks like the Page Properties Report macro that you will get for the real use case. Then you may provide us with the desired output (to count distinct names, votes, etc.) and how you want it to look like (again with the help of a manually created table). 

We can work here or via our support portal that is confidential (we can use real screenshots there).

But again if we are talking about usernames, features and voting, maybe it is not very convenient to use manual work? Type in names, increment the numbers, etc.

Maybe it's easier to use apps for voting - we also provide one for Confluence Cloud and it's free. Please check the Pollmaker for Confluence.

With its help you'll be able to create single and multi vote questions and decide what kind of polls you need: anonymous or "clear" where you will see the voters' avatars.

[Brad Johnson]

Hi Katerina,

i had looked at Pollmaker but I really want to setup a template for users to create pages and capture the description and requires menus for the feature and have people vote directly on that page rather than manually re-enter feature titles into poll maker. 

I’ll post some detail of what I’d like tomorrow. 

[Katerina Kovriga {Stiltsoft}]

If you have the examples of real source tables with data for the Page Properties Report macro (with headers that we'll be able to use inside the SQL query), raise a support ticket - attach the screenshots for our agents to recreate similar tables and guide you through the process.

If you prefer manually created tables with dummy data, you may update this thread.

[Brad Johnson]

Here is an example of a table on the feature page

I have typed names as text here for privacy while posting this. I would prefer people @ mention themselves but would accept either really as I don't expect people to misspell their own name..

Here is what the report could look like. Its simply a page properties report of the features sorted by most popular (The row order should be reversed in the below). A solution could be that the report view would show a column of the count of the names in the 'Vote (Names) field and it would be sorted by that. If we can't count @mentions then we might need to enforce a format, like a separator (e.g. comma) or tell people to always put first and last name so we know 2 words is one vote.

[Katerina Kovriga {Stiltsoft}]

And there will be no the "Votes (Count)" column in the result table? This column was shown in the original one-row table.

If no and you want to count names in the cells, what element is supposed to be between them? The space?

As I see the case, it will be much easier to use the Table Excerpt/Table Excerpt Include macros. You can also collect tables via these macros and show the source page link. But you'll be able to collect multiple-row tables - your users can each have their own row. So, every username will be placed into a separate cell - it's much easier to count them. The simple Pivot Table macro will do.

What concerns the several mentions in one cell, I'll take a look: again please tell how they are separated (if we won't be able to use the @ in the query to count/separate them).

[Brad Johnson]

Sorry for the confusion. I should have removed the Votes (Count) column in the first table because I want to calculate the count based off the number of names entered in Votes (Name).

This feature is new. I will implement whatever is deemed feasible. I would prefer to count @mentions, otherwise I can specify how users need to enter their name. Likely FirstName LastName and a comma separator.

I would rather not let users have their own row. I'll end up with tables that have a hundred rows of users.

I will use anything that's feasible to separate @mentions as well. Anything you can make work.

Thanks for your help. I'm really excited to introduce this feature and I can't wait to see what you come up with.

[Katerina Kovriga {Stiltsoft}]

Thank you, I'll return here shortly.

[Katerina Kovriga {Stiltsoft}]

Hi @Brad Johnson ,

So here is a possible solution for the case:

SELECT *,
LENGTH('Votes (Name)') - LENGTH(REPLACE('Votes (Name)', "!", "")) AS 'Votes (Count)'
FROM (SELECT *, CASE WHEN 'Votes (Name)' IS NULL
THEN "No votes"
ELSE 'Votes (Name)'
END
AS 'Votes (Name)' FROM T1)

I'm gonna try to describe what I have done here and why.)))

The second part of the query that is 

(SELECT *, CASE WHEN 'Votes (Name)' IS NULL
THEN "No votes"
ELSE 'Votes (Name)'
END
AS 'Votes (Name)' FROM T1)

is required to work with empty cells (when your features don't have any votes). Later we need them to become strings, so we check if they are empty (IS NULL) and paste the "No votes" expression inside each empty cell.

The first part of the query that is

SELECT *,
LENGTH('Votes (Name)') - LENGTH(REPLACE('Votes (Name)', "!", "")) AS 'Votes (Count)'
FROM

is required to count the number of user names (@mentions).

Here comes the difficulty: the "@" character is a virtual one for such @mentions, it can't be seen by macros and counted using SQL.

So we need some real characters to split user mentions. Maybe it looks better with spaces, but they are very likely to be missed and besides it will be confusing for the macro while operating with empty cells.

So I took an exclamation mark and placed it before every user mention: you can relate to it as a vote confirmation (at first we confirm it, then insert our name).

Or you may use "+" instead - change the source table and this part of the query LENGTH(REPLACE('Votes (Name)', "+", "").

Now we can count the number of exclamation marks in the cell that equals to the number of our votes.

Hope this helps.

[Brad Johnson]

Perfect!

I switched to ‘,’ for the separator as I think it will make more sense to users. I’ll leave an instruction that there must always be a comma at the end. If they forget then it’ll just be missing one vote. 

Thanks for this.

FYI I tell everyone who will listen that confluence is great but Stiltsoft makes it awesome. Your product, your documentation, and especially your support are 10 / 10.

[Katerina Kovriga {Stiltsoft}]

The "," may be confusing if there is only one vote or your vote is the last:

| @katerinakovriga, |

| @katerinakovriga, @johhnsmith, |

Commas indeed are mostly used like separators, but here it's better to use something that equals the number of user names. From our experience, variants like "plus one ..."

| + @katerinakovriga , + @johhnsmith, |  

are better - people tend to forget commas and points, especially if they go to the end of the line.

P.S. It's just our personal experience, I hope that if somebody really wants this or that feature, they will follow the instruction and make sure that their vote is not missing. )))))

Thank you for the positive feedback! I'm going to show your message to the whole Table Filter and Charts team!

[Brad Johnson]

Thought it was worth double checking, there is no ways to count mentions? They aren't single words that could be counted?

Or if we can assume very name in a mention is two words count pairs of words?

[Katerina Kovriga {Stiltsoft}]

It's a string of words - you are right. And the words are counted by separators (spaces, commas, etc.).

I mean even not considering SQL, but in general (for Python, for example) if you have a sentence and need to count its words, you will look for the spaces. Otherwise you don't know where the new word begins and ends as the length is different.

So, I don't see any workaround as for now.

The easiest way is to put each name in a separate cell as I've mentioned before.