Conditional show/hide columns based on contents of another column

[shinnickr]

Hi there,

I have a bit of a weird situation and I'm hoping someone will be able to help.

Right now I have a straight table with multiple columns that have conditional formatting to show different columns based on a selection.  The code for this is:

if(isnull(GetFieldSelections([Rule Description])), 1, 0) // Always shows column if no selection is made

OR

if(wildmatch(getfieldselections([Rule Description]),'*1)*'), 1, 0) // Shows column if selection is like 1)

OR

if(wildmatch(getfieldselections([Rule Description]),'*2)*'), 1, 0) // Shows column if selection is like 2)

This is working as intended, showing or hiding the different columns based on the [Rule Description] selection.

The problem I'm having now is getting the columns to also show or hide if the [Rule Description] column only has one DISTINCT value in it.

For example:

If someone makes a selection of a different filter ( [Code Center] ) and the selection filters the data to only have values of "1)....", I would also like to show the columns as if the [Rule Description] "1)....." was filtered upon.

Anyone have any ideas on what I can do to get this to work?  I have tried FieldValue() but I could not get it to work as intended.

[Anonymous]

try this ryan.... if u want sumthing dynamic

if(wildmatch(getfieldselections([Rule Description]),   '*'&only({1} [Rule Description]) &'*'   ), 1, 0)

[tamilarasu]

Hello Ryan,

Thank you so much for your kind words. Have a look at the attached file. Fingers crossed.

[shinnickr]

Wow Tamil thank you so much.  I think this is working as intended!  Gosh, you're so amazing.

A little curveball though.... I have some default page filters that are applied when someone flips to the sheet.  With the code for rule 1:

If(Isnull(GetCurrentSelections()),1 (which works completely fine if I hadn't neglected to mention the pre-set filters earlier....)

How can I exclude fields from the GetCurrentSelections?  So like IsNull(GetCurrentSelections(-[Date] & [Productivity]))

The 2 fields I want excluded are [Date] and [Productivity], but I'm sure how to exclude them.

[tamilarasu]

Hi Ryan,

Great!! Finally, we did it. Unfortunately, we can not exclude fields in GetCurrentSelections () function. But we have a workaround to exclude those two fields. Not elegant but it's working as per your requirement. Though, i'll try to simplify this expression when I get time. Please have a look at the attached file and let me know if you need any further information.   Let us finish this thing today itself.

[shinnickr]

Tamil we're so so close!

For rule 1 the column is showing when either Service Date OR Productivity is picked, but disappears when both is picked.

I don't want to take any more of your time.  You've been immensely helpful getting this almost done and I can't thank you enough.  I will take it and run with it, and I'll do my best to get it finished.

Thank you again!!!  You're just amazing.

[tamilarasu]

Hi Ryan,

Sorry. I was waiting for your reply till 1 am. Now the time is 3 am here and I'm about to sleep. Actually, I like the way of your interaction and enjoyed it after a long time. Please don't thank me this much time. I'm just trying to share what I knew. By the way, we both will find a solution and close this thread. I alŕeady added this as my task in "To do" list. Actually, the productivity field values are scarambled so I was not able to test it properly. I'll have a look again tomorrow morning and get back to you. Enjoy your rest of the day.

[tamilarasu · Accepted solution]

Here you go..

[shinnickr]

Tamil.....you're my hero.  I think this is working 100%!!!

You're amazing, thank you so so much for your help.  I was banging my head against the keyboard for a while trying to get it to work.  And it should be easy for me now to change the expression for the other columns that need to show/hide based on the rules.

Thank you thank you thank you!!!!!!

[tamilarasu]

Ha ha ha. I'm glad you're happy. Please test with all the possible scenarios and let me know if still there are any issues. By the way, if you worry about the file that posted here, i'm ready to remove it. Kindly let me know.

[shinnickr]

Thank you for offering.  I think actually, yeah, just for the safety of any possible sensitive information I would probably like it to be removed.  I'll go ahead and copy/paste the code into this comment though, so in the future if anyone else comes across this problem they'll have a head start!

=If(Len(Trim(Replace(Replace(GetCurrentSelections(),'Service Date: ' & GetFieldSelections([Service Date]),null()),

           'Is Productivity Stat?: ' & GetFieldSelections([Is Productivity Stat?]),Null())))=0 ,1,

If(Count(DISTINCT [Rule Description])=1 and Wildmatch(Concat([Rule Description],','),'*1) This rule sums*','*2) This rule sums*')>0,1,0

)

)