Table Tool - String Variable Switched Type Error

[CTubbs]

I have a batch macro that is creating summary tables that get written to multiple Excel workbooks along with the associated raw detailed data. It is a batch macro because I am creating 1 summary/detailed output for multiple different groups of data.

I am trying to create some conditional formatting rules with the summary table tools to color cells based on different values within those particular fields, however, those field names change with each batch! So I can't just create the rules based on the 1st batch of data I am building the macro with, I need the rules to be dynamic.

I found this article in the community (https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-Color-Column-values-in-Alteryx-...) that was looking like it was going to work, but now I keep getting this error on the table tool: "String variable switched type".

I've checked the incoming data types and those columns are all set to V_Strings, so really not sure how/why they are switching types within the table tool itself...anyone else have experience with this error and/or a solution?

[CharlieS]

Hi @CTubbs, whomever you are.

I would suggest building some dynamic field re-mapping into the batch macro. Start out by taking your input fields, and renaming them to a consistent set of fields for all your formatting to work, then renaming those fields with the original names in the Table output after the formatting is complete. This would work if you have a consistent field schema, just different names, between all the outputs. 

"but Charlie, how can I replace field names in a table field that's already created?" I'm glad you asked. This is possible by editing the raw PCXML of the Layout field. 

Let me know if this seems like a path that could work for you and we can dive into that process. 

[CharlieS]

Also, the other thread mentioned setting up a "Apply" Formula using the [_CurrentField_]=="x" condition. This is a string test, which may be an issue. I would try and use [_CurrentFieldName_] variable to define when to apply a Column Rule. This was you could set up one rule of each type, then update the list of fields it applies to each time by replacing the target this [_CurrentFieldName_] is set to. 

[CTubbs]

@CharlieS - that [_CurrentFieldName_] tweak mostly worked..

I added "[_CurrentFieldName_] in (...)" to my [_CurrentField_]=="x" logic and the error is no more!!! However, the conditional formatting is still not applying correctly, it seems to be coloring the values all willy-nilly. Interestingly enough, there appears to be a pattern where the formatting is only being applied to the latter half of my columns...like if I were to shift the coloring to the beginning of these columns in the table it would be accurate. Looking into that a bit more now.

So, I was able to figure out the dynamic rules based on that 1st article I linked, your recommendation here worked to stop the table tool from throwing that "String field switched type" error, BUT the coloring/formatting is still not accurate. At least we are making steps in the right direction?

Appreciate all your help on this! Would be huge to figure this out as it would save the recipients of this report the time in having to manually format ~25 different report workbooks.

[CharlieS]

---

@CTubbs wrote:

Would be huge to figure this out as it would save the recipients of this report the time in having to manually format ~25 different report workbooks.

---

How about, if we can't figure out the formatting in the Reporting tools, using Alteryx to kick off the application of those formatting rules in Excel? Check out the article below to see how a Run Command tool could use CScript.exe to run VBA macros that could execute that formatting automatically. 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-Run-Command-Executing-Excel... 

[attilajozsefhegedus]

@CTubbs  @CharlieS 

I was struggling with the same problem for hours and I realised that the report tool seems to be working with all fields no matter I specifically select one column. So if you have multiple types of fields in the input of the table tool and you want to apply condition with "[_CurrentField_]" on numeric field, it will not work. However it will work if you only have numeric type of data in your input table. So my question now whether I can somehow join the other types of columns into the same table once I generated the table only with numeric fields. Any idea? :)