Mass update custom report view

[LukaszMatyszewski]

Hi All,

I’m looking for a solution or step-by-step guide on how to mass update a custom report view. Is this possible?

Case:
I have a custom report built with 100+ text mode columns. Each column uses multiple valueexpression=IF statements (as per the attachement), which returns specific project/product data based on a reference code associated with a project. The reference code is part of a dropdown list within a project’s custom form. The report works perfectly.

What I want to achieve is a smart and quick way to update the report view every time we add a new code or amend the result data for an existing code.

Any ideas would be appreciated!  I thought Kick-Start would be a preferred method, but I can’t quite wrap my head around it. The excel export doesnt provide any meaningfull data and Im not sure what to do with .properties files which come along the export.

Kind regards,

Lukasz

[Doug_Den_Hoed__AtAppStore]

Hi @LukaszMatyszewski 

To maintain similarly complex views (such as dozens of columns each with multiple custom formatting rules to colorize each resulting data cell in Red, Yellow, or Green), I sometimes create a "view builder" in Excel: for each such view column; gradually concatenate options and logic row by row in text mode format, then copy/paste the latest column (or even all columns) into Workfront.

Although it takes a bit of effort to set up, it is time well spent. For some of my most complex work, short of writing a program, this Excel View Builder technique was not only efficient: it was key.

Regards,

Doug

P.S. I use a similar approach for complex custom data formulas

[LukaszMatyszewski]

Thanks Doug,

This is exactly what I managed to do so far. I built a formula builder in sheets.  Works great, but I worry about the amount of copy pasting I might need to do on change.

[Doug_Den_Hoed__AtAppStore]

Sounds like you're on the right path @LukaszMatyszewski,

To extend my parenthetic "or even all columns" comment...with each column's view defined in Excel, provided each has its column number defined, if you then concatenate all of those columns together, copy the result, and paste it into the textmode of the first column in an existing view, it will then overwrite all subsequent columns (i.e. in a single copy/paste action).

Regards,

Doug

[LukaszMatyszewski]

Hi Doug

The solutiuon you propose would solve the problem. Can you point me out to an excample on how to hard code the 1st column? Im thinking of "sharecol" but Im not sure this will work when the merged columns disappear from the view. Or do I just add concatenated data into a blank col while ensuring correct numbering for subsequent columns in the expressions?

[Doug_Den_Hoed__AtAppStore]

Hi @LukaszMatyszewski,

For an example of creating (or in your case, updating) and entire view (with LOTS of columns) in a single copy/paste action, I invite you to follow these instructions, then -- seeing the pattern -- use the Excel View Builder technique we've been discussing to create something similar for your situation.

Regards,

Doug

[LukaszMatyszewski]

I figured it out. Your other comment "or even all columns" got me digging into this and the solution works perfectly. Ive exported a test report, amended the text file for view and loaded it back into the report. Happy as a clam! I suppose this will also work for views alone I just need to match the IDs.

Back to building a formula :):):)

[Doug_Den_Hoed__AtAppStore]

Excellent @LukaszMatyszewski,

There's no stopping you now

Regards,

Doug

[LukaszMatyszewski]

If only it were that simple!

Before diving into building the formula, I realized I need to ensure proper formatting for the view/columns. The report I’m creating should provide accurate results in HTML and export correctly to sheets. However, I’ve noticed that XLS and XLSX exports present issues, as numerical results from calculated columns are treated as text, which sheets doesn't recognize as numbers. I really want to make it simple for users who need to manage some of the data outside of workfront.

Despite trying various format options, none worked as expected. While TSV exports perform better with fields formatted as number, I’ve encountered a few unresolved issues that I can’t seem to fix.

Here are some issue excamples: 

• Singel line text field "Barcode" valueformat=customDataLabelsAsString results as 5.03691E+12 in TSV
• Calculated column wher IF statement returns text "2 - 6" results as "02-Jun" in TSV

I apreciate some help with this, idealy a point to avialable format options for text data so I can put this to bed and move on

[Doug_Den_Hoed__AtAppStore]

Hi @LukaszMatyszewski,

Yes, saving as Excel can be finnicky. In the past, I've resorted to a couple of tricks (namely converting to text, and even baking a single quote prefix into the formula to force Excel to treat the content as text), but for situations where the data needs to be Just So (e.g. for uploading data into an accounting system that expects positional CSV file), use our Magic Reports solution to gain total control and generate such well-formatted files.

Good luck with the tricks, and I look forward to hearing how you make out.

Regards,

Doug

[skyehansen]

General rule of thumb: Kickstarts are for adding new things. They aren't for updating existing things.

One of the properties files should be your view. It should look like your view does, if you convert every column to text mode, and then string it all into one text file. To change a column is pretty easy because you just locate the column you want to change, and then make the change. If you were going to add a column that would be a little more difficult. 

After you're all done, you could re-zip and upload it as a net-new report. I think there's also a way to upload the view file separately, as a custom view -- if this is the case, you can apply that view to your existing report and re-save.

If you have to update this report often, it's well worth investigating how to do it as a kickstart.