Apply Column Rules to Multiple Columns in the Table Tool

[RodLight]

@HenrietteH had a great post at ... https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Apply-Column-Rules-to-Multiple-Columns-in-th... for formatting dynamically changing columns! 

This is a great trick, but now I'm trying to apply it to be able to format the cell background colors conditionally based on their values, and yet do it on dynamically changing week dates. An example would be...

...where, for example, the color should be RED if less than 9.1, YELLOW if between 9.1 and 9.3, etc.

I deselect the columns and then set a Column Rule where..."IF ToNumber([_CurrentField_]) < 9.1 THEN 1 ELSE 0 ENDIF" (with the Background Color coded as RED).

I assume I should be using the "CurrentField" variable (since it is dynamic) and I'm setting it to either 1 or 0, where I believe what should happen is if it is 1, then the color I pick will apply. What I found was to avoid an initial error, I needed to convert the "CurrentField" to a number. 

I go back and reselect my columns (which does apply the Column Rule to each weekly column), but now when I run it, I'm getting an error on the Table..."String variable switched type".

Any thoughts on how to get my columns to conditionally format on value but dynamically (since the weeks change)?

Thanks,

Rod

[MarqueeCrew · Accepted solution]

Hi Rod (@RodLight)!

I achieved the results by configuring the table with a single text column.  Then copied the tool and it works for me with dynamic data.  Check out my solve and if you have any questions, give me a call.

Cheers,

Mark

[RodLight]

@MarqueeCrew, thanks for the reply!

I got your technique to work when the one column of "base" data was numeric to begin with. However as soon as I add in a "label" column, I go the error "String variable switched type". 

So I accommodated for that by including in the Report Table formula...

[_CurrentFieldName_] != "Label" AND ToNumber([_CurrentField_]) < 9.5

That works then. I've attached an example.

The funny thing is that when I transfer this technique over to my "real" data, I'm still getting the "String variable switched type". From what I can gather, that occurs when a value in a field designated by a Select tool switches type. Just can't seem to find out where that is. 

But anyway, thanks for the idea!

[Sam7]

Can I have an image of the formula/edit row bit.  I have downloaded the results, but can't see what it does, where the rules/colours can be seen? Thanks

[PuffinPanic]

I was trying to get all fields which say 'fail' to have a red background. I managed to get the effect I needed with this set-up

Using the hint about [_CurrentFieldName_] in RodLight's post (I didn't realise I could use these field place-holders in a table tool formula) and the process of adding only one field (making sure the rule is set up on the dynamic fields option) then copying the tool to add back in the rest of the fields. 

Just thought I would add this in case it helps anyone else.