Calculated Column – hypelink to filtered view

I was playing around SP one day when someone asked me if there was a way to create a link that would filter a view. Once I Started playing with filtering a view I realized that additional parameters (query string) are added to the URL.

FilterField1 (FF1) and FilterValue1 (FV1) are part of one set of parameters and for each additional filter they increment by 1.

?FilterField1=LinkTitle&FilterValue1=Chevy&FilterField2=Color&FilterValue2=Red

I realied I was able to paste a query string value in the address bar and it would filter. I began working on how to implement this in a calculated field. Thats right, I’m using Christophe’s HTMLcc script again.

The original requirements were that there was a target list. One of the fields in this list [Client] is a lookup field that pulls from the source list. What we want to do is go to the source list select the client name, maybe another variable, click the link and my target list is filtered.

Currently my source list contains car data

Make

Model

Color

So how can I convert this data into a dynamic link that filters my source list?

I started by adding 3 Yes/No fields

FV1

FV2

FV3

This method is used if you have several columns you want to apply the filter for. Also the Yes/No can be choise fields with your specific values. The calculated formula below would change some, but the results would be the same.

I ordered the fields in my view and form so it was easy to identify which FieldValue went to which field. Field names can be anything. I choose these names initially because it was less confusing and I made them Yes/No so I can turn on/off the filters.

My first formula only worked with one filter. The problem is you can’t have FV1 and jump to FV3. Now my formula had to take into count that my fields Make or Model may not be part of the filter. Then I also had to watch I didn’t exceed the character limit in the calc field.

After much tweeking I was able to come up with this

="<DIV><a href='/sites/globalmp/Lists/SourceList/AllItems.aspx?"
&IF(AND([FV1]=TRUE,[FV2]=TRUE,[FV3]=TRUE),"FilterField1=LinkTitle&FilterValue1="&[Make]&"&amp;FilterField2=Model&FilterValue2="&[Model]&"&amp;FilterField3=Color&FilterValue3="&[Color],
IF(AND([FV1]=TRUE,[FV2]=TRUE,[FV3]=FALSE),"FilterField1=LinkTitle&FilterValue1="&[Make]&"&amp;FilterField2=Model&FilterValue2="&[Model],
IF(AND([FV1]=FALSE,[FV2]=TRUE,[FV3]=TRUE),"FilterField1=Model&FilterValue1="&[Model]&"&amp;FilterField2=Color&FilterValue2="&[Color],
IF(AND([FV1]=TRUE,[FV2]=FALSE,[FV3]=TRUE),"FilterField1=LinkTitle&FilterValue1="&[Make]&"&amp;FilterField2=Color&FilterValue2="&[Color],
IF(AND([FV1]=TRUE,[FV2]=FALSE,[FV3]=FALSE),"FilterField1=LinkTitle&FilterValue1="&[Make],
IF(AND([FV1]=FALSE,[FV2]=TRUE,[FV3]=FALSE),"FilterField1=Model&FilterValue1="&[Model],
IF(AND([FV1]=FALSE,[FV2]=FALSE,[FV3]=TRUE),"FilterField1=Color&FilterValue1="&[Color],"")))))))&"'>"&[Client]&"</a></DIV>"

Looks like this will only go 3 filters deep and it maxed out the nested IF’s.

The formula starts out with the anchor to my source list which also included “?”

="<DIV><a href='/sites/globalmp/Lists/SourceList/AllItems.aspx?"

This is followed by 7 IFs. checking if my FV fields are Yes “TRUE” or No “FALSE”.

First statement all fields are Yes or TRUE, the query string will return all three filters. looking at the string in details.

FilterField1=LinkTitle is the Title field link to item – otherwise this is the column name.

FilterValue1=”&[Make] – is the value of [Make] if FV1 is TRUE

&IF(AND([FV1]=TRUE,[FV2]=TRUE,[FV3]=TRUE),"FilterField1=LinkTitle&FilterValue1="&[Make]&"&FilterField2=Model&FilterValue2="&[Model]&"&FilterField3=Color&FilterValue3="&[Color]

This layout provided the perfect query string layout and when the results are applied the view is filtered.

Explore posts in the same categories: JavaScript, WSS