Question

CONCAT function and If then expressions

Forum|Forum|11 months ago
January 31, 2025
3 replies
927 views

Hi,

I'm just getting started with custom expressions in text mode so my questions may be basic.

I have 2 fields on an hour report that I am having trouble with.

The first is that I have a project # column and a project name column that I would like to concat into one column that displays the project number and the name together. I feel like this should be fairly simple but I must have part of my syntax incorrect. It just comes up blank. I'm struggling with what the listmetod and value format should be and wonder if that is where I am going wrong.

displayname=Project 
listdelimiter=<div>
listmethod=nested(projectUserRoles).lists
type=iterate
valueexpression=CONCAT({project:Project #},":",{project:name})
valueformat=customDataLabelsAsString

The text mode of the 2 fields I am trying to concat are:

linkedname=project
namekey=view.relatedcolumn
namekeyargkey.0=project
namekeyargkey.1=Project #
querysort=DE:project:Project #
valuefield=project:Project #
valueformat=customDataLabelsAsString

linkedname=project
namekey=view.relatedcolumn
namekeyargkey.0=project
namekeyargkey.1=name
querysort=project:name
valuefield=project:name
valueformat=HTML

The second thing I am wanting to do I think would involve an if..then. There is an hour type column that either shows an hour type name or "Project Time" or "Task Time".

I would like a column will show the hour type name unless hour type is "Project Time" or "Task Time". If it is project time or task time, it would display the concat Project # and Project name that I am trying to do above.

The text mode for the hour type field is:

listsort=nested(hourType).HTML(name)
namekey=hourtype
querysort=hourType:name
valuefield=hourType:name
valueformat=HTML
width=95

I appreciate any guidance!

Thanks

Heather

Workfront

Madalyn_Destafney

Community Advisor

Hi there, you actually don't need a value expression in a column at all to combine columns. You just need to add a bit of text mode to one of the columns to share the columns so that the values can be presented together in 1 column.

Put the project # and the project name columns side by side in your report. Then in the left-side column between the two, go into text mode of the column and add:
sharecol=true
If you want to take it further and have them together with a linebreak in between, check out this article.

Regarding the hour types, you could simply add a filter to your hour report (assuming you're making an hour report to remove the hour types you don't want to see. If you don't want to weed them out from the report, then you could just sort the report by hour type and for all the project/task time rows you refer to your project name/# column?

If this helped you, please mark correct to help others : )

H

HeatherTeAuthor

Hi Madalyn,

Thanks so much. The merging of columns works but I will be exporting this to excel and it separates once it is exported. That is why I was thinking concat may work better.

We will be using this report to populate a PBI dashboard so I'm trying to get as much information setup in the export itself.

For the Hour type, we won't be filtering by that. I do currently have it sorted by type but the information that I am trying to get is currently in 3 different columns. The hour type will show the 3 types I mentioned, then the source shows the actual task if it is 'Task Type' or the project name if it is 'Project Type'. I then have to use the project name field if it is a task type to get that information. I'm just trying to piece those 3 options into one for the export. I'm not sure if that makes sense without seeing it.

I appreciate the suggestions!

Heather

Madalyn_Destafney

Community Advisor

You could have a calculated field that lives in a custom form that's on all your projects (or at least the projects you're capturing - if you already have an existing project custom form on all of them, add this field to it) but simply concats the project name + project number (assuming the reference number, otherwise you'd use your custom # field in place of the ref #). Use the text format and it would look like this:

Then you'd use this calc field as your field of your column in your report. You won't be able to add hour type in this way bc unfortunately there isn't a custom form option on hour types. But this can get you 2 of the 3 combined, then you use that as your field that shows them together and won't break when exporting to excel bc WF sees it as 1 field (try testing it on 1 project first and see if it handles the export ok, don't quote me on that).

If this helped you, please mark correct to help others : )

KellieGardner

Community Advisor

@heatherte -

For your first formula to combine the name and reference number it should be formatted like this:

valueexpression=CONCAT({project}.{DE:Project #}," : ",{project}.{name})

For your second formula you can use this:

valueexpression=IF({hourType}.{name}="Task Time" || "Project Time",CONCAT({project}.{DE:Project #}," : ",{project}.{name}),{hourType}.{name})

https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/calculated-custom-data/calculated-data-expressions

https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/calculated-custom-data/condition-operators-calculated-custom-expressions

H

HeatherSc2

Thanks so much. That works to add the project if project or task but the hour type is showing blank if it is not project time or task time. Any ideas there?

Thank you!

Heather

KellieGardner

Community Advisor

Sorry about that, I left off part of the formula when I typed it out.

valueexpression=IF({hourType}.{name}="Task Time" || {hourType}.{name}="Project Time",CONCAT({project}.{DE:Project #}," : ",{project}.{name}),{hourType}.{name})

kautuk_sahni

Community Manager

@heatherte just checking in! Were you able to get this resolved? If one of the replies above helped—whether it completely solved the issue or simply pointed you in the right direction—marking it as accepted can make it much easier for others with the same question to find a solution. And if you found a different way to fix it, sharing your approach would be a great contribution to the community. Your follow-up not only helps close the loop but also ensures others benefit from your experience. Thanks so much for being part of the conversation!

Kautuk Sahni

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded