The Admin's tale - User Macro filtering a Confluence table

[#1]

 

Once upon a time, there was a Confluence holding pages with very very large tables with lots of columns and even more rows. People used the browser's search function to jump to the data they need, but they asked for something that would hide all rows except the ones they were searching for. 

The Admin thought about it and this is what he found out:

What if one could add a search field on top of the table to filter out all unnecessary rows?

So, how can this be done? There is no built-in-feature like that and the admin didn't find a plugin for a reasonable price, his budget was already spent for this year. So he decided to create a small custom user macro to implement this feature. 

User Macros can be implemented by Confluence administrators. The admin decides whether the macro is just available for other admins or for all the other users when editing pages. 

The place where user macros are created is in the admin's section of Confluence. Here you find the menu item "User Macros".

Creating a new User Macro results in a form with these fields:

 

Macro Name is the name of the macro, of course... - but not the title of the macro (which is displayed in the macro browser).

Visibility - hide User Macros as long as they are not really really tested!

Macro Title - is displayed in the macro browser

Description - tell your users what your macro is doing - the description is displayed in the macro browser:

Next, the admin had to define, how the macro body should be processed. The processing options are described in detail on this page: https://confluence.atlassian.com/conf510/writing-user-macros-829077960.html

The admin tried a little bit and then decided that "No macro body" would be the right option, since the macro has just one parameter, but no body:

 So now take a look at the heart of the user macro, the template itself:

Each macro has a header consisting of these lines:

## Macro title: <your macro-title as stated above>
## Macro has a body: <Y or N>
## Developed by:
## Date created:
## Installed by:

I think, these parts are self-explanatory, but the next is not:

## @noparams

Parameters are the values you enter when you click on a macro's edit button when you edit a page. So, yes, there are macros that don't have parameters. @noparams is the default.

But the admin thought, the user should decide, how wide the input field should be: short, medium, medium-long, long or full-width. These values were defined by Atlassian in their CSS and the admin used these so that the new field has the look and feel of the other Atlassian page elements.

So he added the parameter as param0 to the template:

## @Param 0:title=Length of the input field|type=enum|enumValues=short-field,medium-field,medium-long-field,long-field,full-width-field|default=long-field

And then, the user could set the length of the input field:

 

Next, the admin had to define the input field. It is a regular HTML form with an input element. The class aui refers to the Atlassian User Interface CSS:

<form class="aui">
<input class="text $param0" type="text" id="searchInput" placeholder="Enter Search term"></form>

At last, the admin needed a script to filter out all the rows, where the search term is not included. He used JQuery, a "cross-platform JavaScript library designed to simplify the client-side scripting of HTML" (Wikipedia).

The Script is doing the following

• if you type something into the field "searchInput", it hides every row of your table
• then it looks at every row again: 
  • if it finds the term, the row is shown
  • if not, it stays hidden
• table headers are always shown

And this is the script:

<script type="text/javascript">
AJS.$("#searchInput").keyup(function () {
var jqry = AJS.$
var rows = jqry("tr").hide();
if (this.value.length) {
        var data = this.value.split(" ");
        jqry.each(data, function (i, v) {
                rows.filter(":contains('" + v + "')").show();
        });
} else rows.show();
AJS.$('thead tr').show();
});
</script>

And this is how it looks on the Confluence page:

The unfiltered table:

 

The same table filtered with the term "12":

 

What's left?

This is just a very simple basic filter. Soon, the admin's people asked for more:

• search in a specific column
• filter with wildcard characters
• filter a specific table (in this example, each table on the page is filtered with the one and only input field)

But the admin had a lot more things do and so, this filter remained the same until now ...and they all lived happily ever after.

Maybe another admin will add some new features to the basic filter... Sometimes

And here is another Admin's Tale: Adding an image to the page title

[Davin Studer]

This is how I would do bullet point three ....

Set the macro body processing to Rendered. Put your table inside the macro body. Then in your template do something like this ...

## provides a unique number that can be used to target this user macro only
#set( $id = $action.dateFormatter.calendar.timeInMillis )

<div id="tableFilter$id">
$body
</div>

Now you can target each table individually in jQuery like this ...

AJS.$('#tableFilter$id tr')

[Thomas Schlegel]

Hi Davin,

thank you for your suggestion! I will try that as soon as possible.

[Fabian A. Lopez (Community Leader - Argentina, Florida, California)]

Very good article. Thanks for sharing!

[Daniel Eads {unmonitored account}]

Nice! The Table Filter and Charts plugin can provide this functionality as well as more gravy, but this is a great example of doing something with a user macro. There's a lot of possibilities without having to develop an entire plugin!

[Arni]

I love this, thanks for sharing! :)

[David Sumlin]

Nice!  This is helpful!

[Thomas Schlegel]

@Fabian, Daniel, Arni and David - thank you. I'm glad you like the article :-)

[Dennis]

Thanks for sharing this useful article.

[Thomas Kreft]

Thanks for sharing! Just implemented this and it's very useful. I like the fact that it doesn't require to move tables into a macro body, which can be really painful (especially if, for some reason, the macro gets broken and you have to move all tables out of the macro body again).

[Thomas Kreft]

On a sidenote... is there a way to make this filter case insensitive?

[Thomas Schlegel]

Hi Thomas, Hi Denis,

glad you like the article, thank you.

Making the filter insensitive should be no problem. But I have not implemented that yet. The ":contains"-part of the script will be a bit longer then. 

Here is an example script for case insensitive search: http://stackoverflow.com/questions/8746882/jquery-contains-selector-uppercase-and-lower-case-issue 

But, as I said, the Admin did not have the time to test that script yet :-)

[Dustin Sterkenburg]

This is great, thank you!  For anyone that is looking to make this filter work for a JIRA issues table, you can use the following modified javascript since the JQL tables do not include a 'thead' definding the header row.  Code includes a check looking for that row so that it works for both 'regular' and JQL (Jira issues) tables at the same time.

<script type="text/javascript">
AJS.$("#searchInput").keyup(function () {
var jqry = AJS.$
var rows = jqry("tr").hide();
var searchData = this.value;
if (searchData.length) {
        var data = searchData.split(" ");
        jqry.each(data, function (i, v) {
                rows.filter(":contains('" + v + "')").show();
        });
} else rows.show();
if (AJS.$('thead tr').length){
AJS.$('thead tr').show(); //show the header row for tables
}
else{
AJS.$('tbody tr').eq(1).show(); //show the header row for Jira-Issues Tables (no thead and first tr is empty, so show the second tr)
}
});
</script>

[Thomas Kreft]

Unfortunately, I know next to nothing about javascripting (and currently lack the time to change this). My pathetic attempts at making the filter case insensitive were all for naught.

So I'm hoping for some nice person to step in. ;-)

[Spadeto]

Nice work, thank you!

I made some improvements compiling the suggestions above.

Now we can:

1. Add multiple tables, each one has its own filter;

2. Filter by column or entire row;

3. Add a input label, for better usability;

4. Filter case-insensitive.

## Macro Name: my-table-filter
## Macro title: My Table Filter
## Category: Confluence Content
## Macro Bodty Processing: Rendered

## @param FilterID:title=FilterID|type=string|required=true|desc=ID [a-z,A-Z,0-9]
## @param Label:title=Label|type=string|required=true|desc=Label
## @param Class:title=Length of the input field|type=enum|enumValues=short-field,medium-field,medium-long-field,long-field,full-width-field|default=long-field
## @param ColumnNumber:title=Column Number|type=string|required=true|default=-1|desc=Specify the column number or "-1" for the entire row

<form class="aui">
    <div class="field-group">
        <label for="searchInput$paramFilterID">$paramLabel</label>
        <input class="text $paramClass" type="text" id="searchInput$paramFilterID">
        <div class="description">Filter parameters</div>
    </div>
</form>

<div id="tableFilter$paramFilterID">
$body
</div>

<script type="text/javascript">
    AJS.$("#searchInput$paramFilterID").keyup(function () {
        var jqry = AJS.$
        var rows = jqry("#tableFilter$paramFilterID tbody tr").hide();
        var searchData = this.value;
        if (searchData.length) {
                var data = searchData.toLowerCase().split(" ");
                jqry.each(data, function (search_idx, str) {
                rows.filter(function(row) {
                    var col_to_filter = $paramColumnNumber;
                    if (col_to_filter>=0) {
                        var td_to_filter = $(this).children("td").eq(parseInt(col_to_filter));
                        return $(td_to_filter).text().toLowerCase().indexOf(data) >= 0;
                    } else {
                        return $(this).text().toLowerCase().indexOf(data) >= 0;
                    }
                }).show();
                });
        } else {
            rows.show();
        }
    });
</script>

:)

[Thomas Schlegel]

Hi Aloisio,

thank you! That's great and very helpful :-)

[Thomas Kreft]

Thanks, Aloisio!

As I liked Thomas' original script for the very fact that it works automagically for every table on a page, and doesn't require to move tables into a macro body, I altered your script to specifically fix the only problem I had with the original script:

• It's now case insensitive

The rest is like the original - no table specific filter, no column filter. Just place it somewhere on a page containing one or more tables.

For those interested:

## Macro Name: filter-all-tables
## Macro Title: Filter all tables
## Category: Confluence Content
## Macro has a body: N
## Body processing: None
##
## Source: https://community.atlassian.com/t5/Confluence-articles/The-Admin-s-tale-User-Macro-filtering-a-Confluence-table/ba-p/459369
## Date created: yyyy-mm-dd
## Installed by: zzz

## @param 0:title=Lenght of the input field|type=enum|enumValues=short-field,medium-field,medium-long-field,long-field,full-width-field|default=long-field
 
<form class="aui">
  <input class="text $param0" type="text" id="searchInput" placeholder="Filter all tables (case insensitive)">
</form>

<script type="text/javascript">
AJS.$("#searchInput").keyup(function () {
    var jqry = AJS.$
    var rows = jqry("tr").hide();
    var searchData = this.value;
    if (searchData.length) {
        var data = searchData.toLowerCase().split(" ");
        jqry.each(data, function (search_idx, str) {
            rows.filter(function(row) {
                return $(this).text().toLowerCase().indexOf(data) >= 0;
            }).show();
        });
    } else rows.show();
    AJS.$('thead tr').show();
});
</script>

[Spadeto]

Hi Thomas Kreft, nice work.

"Simplicity is the ultimate sophistication". Leonardo Da Vinci .

:)

[Dmitry Ree]

Thomas, does the code work with multiple word search? I tried and it works with a single word only.

[Thomas Schlegel]

Yes, Dmitry, it should work with multiple word search. I've just tested it once again in my pages with two words. Columns are shown that contain the first word and also columns that don't have the first but only the second word.

[Dmitry Ree]

Thomas, it did not work for me, mb because I used the page properties macro. So I Googled and found an alternative similar solution.

How about highlighting search matches? I also found a couple of solutions in the internet however their performance was very poor especially in internet explorer.

[Rahul Savaikar]

I used this code provided by @Thomas Kreft, however, it did not work for multiple word search.

## Macro Name: filter-all-tables
## Macro Title: Filter all tables
## Category: Confluence Content
## Macro has a body: N
## Body processing: None
##
## Source: https://community.atlassian.com/t5/Confluence-articles/The-Admin-s-tale-User-Macro-filtering-a-Confluence-table/ba-p/459369
## Date created: yyyy-mm-dd
## Installed by: zzz

## @@param 0:title=Lenght of the input field|type=enum|enumValues=short-field,medium-field,medium-long-field,long-field,full-width-field|default=long-field
 
<form class="aui">
  <input class="text $param0" type="text" id="searchInput" placeholder="Filter all tables (case insensitive)">
</form>

<script type="text/javascript">
AJS.$("#searchInput").keyup(function () {
    var jqry = AJS.$
    var rows = jqry("tr").hide();
    var searchData = this.value;
    if (searchData.length) {
        var data = searchData.toLowerCase().split(" ");
        jqry.each(data, function (search_idx, str) {
            rows.filter(function(row) {
                return $(this).text().toLowerCase().indexOf(data) >= 0;
            }).show();
        });
    } else rows.show();
    AJS.$('thead tr').show();
});
</script>

Can somebody please help?

The below code works well for the single word search without whitespaces.

## Macro Name: filter-all-tables
## Macro Title: Filter all tables
## Category: Confluence Content
## Macro has a body: N
## Body processing: None
##
## Source: https://community.atlassian.com/t5/Confluence-articles/The-Admin-s-tale-User-Macro-filtering-a-Confluence-table/ba-p/459369
## Date created: yyyy-mm-dd
## Installed by: zzzz

## @param FilterID:title=FilterID|type=string|required=true|desc=ID [a-z,A-Z,0-9]
## @param Label:title=Label|type=string|required=true|desc=Label
## @param Class:title=Length of the input field|type=enum|enumValues=short-field,medium-field,medium-long-field,long-field,full-width-field|default=long-field
## @param ColumnNumber:title=Column Number|type=string|required=true|default=-1|desc=Specify the column number or "-1" for the entire row
 
<form class="aui">
 <input class="text $param0" type="text" id="searchInput" placeholder="Filter all tables (case insensitive)">
</form>

<script type="text/javascript">
AJS.$("#searchInput").keyup(function () {
 var jqry = AJS.$
 var rows = jqry("tr").hide();
 var searchData = this.value;
 if (searchData.length) {
 var data = searchData.toLowerCase().split(" ");
 jqry.each(data, function (search_idx, str) {
 rows.filter(function(row) {
 return $(this).text().toLowerCase().indexOf(data) >= 0;
 }).show();
 });
 } else rows.show();
 AJS.$('thead tr').show();
});
</script>

[Thomas Kreft]

@Rahul Savaikar,

I think the OP's script isn't meant to provide multi word search. I only altered it to be case insensitive,  which is good enough for our purposes.

If someone comes up with such an enhancement, though, I'd also gladly take it. ;-)

[Rahul Savaikar]

Thank you for the explanation @Thomas Kreft :)

For now, I will happily accept the available script.

[Monty Bhatia]

Hi all, thanks for this; really helpful.

I noticed this:

var data = searchData.toLowerCase().split(" ");

I removed the .split(" ");  method and it does appear to allow for spaces now.  Having said that, It would be great to know from the original coders what the split method was added for to ensure I didnt cause any new issues.

Works great btw :)

[Deleted user]

This looks like it would do what I need, but I cannot get site admin privileges.  Is there a way to make this work without that?