Data format in output as "Accounting" type

[amyfshe]

Hello - I've looked through past answered threads & tutorials on how to solve my problem but haven't been successful in finding a solution. 

I have a workflow that has a report input where date field headers change every week. I am looking for a way to configure the output to change the data format of records that show "0" to show as "-" (dash). In Excel, the equivalent of selecting a record (or a range of records), right click --> Format Cells --> Number tab --> Accounting

The tricky part is because the date field headers change every week, if I were to put in a "If else" formula for every column, that formula would need to be updated every week to account for the new date column.

I attached the output report - Sheet1 is the current output, Sheet2 is the desired output. But the column headers change weekly so I'm having trouble finding a solution that takes changing columns into consideration.

Thank you in advance for your guidance & advice!

[estherb47 · Accepted solution]

Hi @amyfshe 

You can try a multi-field formula tool, which looks at all of the columns, regardless of their headers.

Use an expression like: iif([_CurrentField_]=0,"-",[_CurrentField_])

and set the data type to String (otherwise you can't use a dash).

Just be sure to nest a tonumber() function around any of the remaining numbers in any tool if you want them to act as numbers.

Cheers!

Esther

[ddiesel · Accepted solution]

Hi @amyfshe !

@estherb47 is spot on with the multi-field formula. I just wanted to add that I have struggled with this same issue, and I wanted to share a workaround that works for me. 

First I create an Excel template that has a hidden "data" sheet. The main sheet (named "Formatted" ) holds all the formatting. Each cell on the "Formatted" sheet contains a formula that points to the corresponding value on the hidden "data" sheet. Then I execute the workflow to Overwrite Sheet (drop) to the "data" sheet. This allows me to control the formatting, print preview, headers/footers, add a logo, etc.

In my use case, I overwrite the same file each time I run the workflow, then the attachment is emailed out to the subscribed recipients but you could use a bat file to copy over a new template each run.

Hope that helps!

[ddiesel · Accepted solution]

Here is an example of a template file.

[amyfshe]

THANK YOU both! I will try this today & report back. Really appreciate it!

[amyfshe]

---

@EstherB47 Thank you for the Multi-Field formula - it worked! However, the non-zero numbers are now String type... is there a way I can apply another multi-row tool that converts only those non-zero records into Numeric data types? I'm a little stuck on if that's possible.

Either way, thank you for the help! I am 99% there which is way farther than I was before posting!

[amyfshe]

@ddiesel Awesome idea! I will be implementing this for myself. It's a little harder if I share this workflow with my colleagues to have them use the same template, but this is such a wonderful idea for myself. Thank you!!

[ThizViz]

Genius. I will undoubtedly make use of this technique in the future!