Modifying Data Cleanse tool to dynamically select all text fields?

[sean_bolte_dup_544]

Hello -

I have the Data Cleanse tool embedded into another macro that will process files with a multitude of changing text fields. Since the tool exists as a macro, what are the setting modifications to the base actions that would always be defaulting to "All" of those incoming text fields to be selected?

Understood that first dealing with text fields only is accomplished changing the List Box - 

Field Type ListBox(11) from the macro

but what are the modifications so that all fields coming into the tool will be utilizing the "All" selection choice as a default? Comments, Ticket Type and Applications Impacted would be a sample of another set of data coming into this tool with these columns not previously seen...

Text fields not selected

It appears that upon the field selection, the IN statement is crafted from the fields that would be manually selected, but I need them to dynamically always select all text fields to be cleansed.

Check Box (109) from the macro

Here is the info on the tool Tool Mastery - Data Cleanse.

Thanks for help on this!

[atcodedog05]

 Hi @sean_bolte_dup_544 ,

I have answer to one of your questions.

If currently i have 10 columns all are selected later 2 more columns are added it should select them too.

This can be done by checking the "unknown dynamic field" which comes as the last field.

But yeah it might also be True that there is a bigger picture.

[sean_bolte_dup_544]

Hello @atcodedog05 , thanks for the response. I certainly see that behavior in the Select tool, but not within the base behavior of this tool. Actually, in the Mastery section where this is discussed, those were pretty much the extent of the comments in terms of usability of the tool.

Wish Dynamic was an option!

If there's something else you're referencing, can you clarify? 

[atcodedog05]

Nope that was what I am referring too

I thought it existed 

I guess since Data cleanse works only on string it doesn't have that unknown dynamic field option.

Anyways got to know  something.

Hope you can figure out something.

Cheers and happy analysing : )

[morr-co]

Hi @sean_bolte_dup_544 - the Data Cleanse tool is actually just a macro. When you right-click on it in the menu, you have the option to open and view the workflow.

My recommendation would be to copy the functions you need from the macro and populate them in Multi-Field formula tool. The Multi-Field Formula can be configured to account for Dynamic or Unknown Text fields.

[atcodedog05]

@morr-co 

You are a genius !!!

This is amazing !!

Maybe this will turn things around.

[sean_bolte_dup_544]

@atcodedog05and @morr-co thanks for the input. I was actually trying to uncover how modification of the underlying tools representing the macro (these are in the screenshots shown above) would need to be manipulated to ensure the Boolean "All" value would always be in place. In terms of manipulating the underlying actions/settings, this is where I was trying to go with this for better understanding the Interface tool suite changes needed to make this happen.

In terms of raw, getting it done, tying to a Multi-Field formula tool, lighting up "Dynamic or Unknown" fields and performing REGEX_REPLACE([CurrentField],'\s+',' ') - @morr-co you are spot on with your answer. I was attempting to get at the modification of the macro to understand the actions of either 1) adding in a function/action that would have a default "Dynamic or Unknown" field included in the  'Select Fields to Cleanse' selectors, or mentioned above 2) get "All" to be my default.

Looking to the app guru's that might be able to lend some guidance in this area.

[atcodedog05]

Hi @danilang 

Sir can you shed some light on this scenario.

[morr-co]

Does it work if you modify the List Box configuration in the macro to be "All Checked by Default"?

[morr-co]

Sorry, I just looked and my recommendation above is actually how the macro is configured. Since the List Box limits the records that get cleansed, you could delete it from the macro so that all fields passed in are cleansed. You could also use a Dynamic Select tool to ensure the field type is a string in order for it to get sanitized.