Need help with a Calculated Field Expression

[SarahSm]

I'm trying to create a custom artwork file name based on the name of the project name entered into our system and reference number. Is there a custom expression I can use to remove the spaces in a project name or replace the spaces with an underscore?

[Ali_Pentico · Accepted solution]

Hi Sarah. You can use Replace to remove the spaces. The bolded section in the equation below is what I used on a project custom form that will replace spaces with an underscore. The equation would need to change a bit if you are adding the custom field to a different object or if you are just creating a calculated column in a report/view.

CONCAT(Reference Number,REPLACE(Name," ","_"))

The result for a Project A with a reference # of 123 would be: 123Project_A

Hope this helps!

[SarahSm]

Thank you so much!

[Kundanism]

Thanks a lot @Ali Pentico‚ for sharing above expression. But it is possible to restrict until first 10 letters or first two words only. Could you share the expression for similar request? Also, if this creates a clickable link that will be excellent.

Mvh

Kundan.

[Ali_Pentico]

Hi Kundan. To get the first 10 characters, I would use the LEFT function. Using LEFT(Name,10) on a Project would give you the first 10 characters of the project name. Below is the text mode I used to create a column in a project report that would show the first 10 characters of the project name and then still link to the project. I bolded the part that does the link.

displayname=Shortened Name

linked.valuefield=name

linked.valueformat=HTML

namekey=name

querysort=name

textmode=true

valueexpression=LEFT({name},10)

valueformat=HTML

Hope this helps.

-Ali

[Kundanism]

Thanks a lot @Ali Pentico‚ .

I was trying to merge the above two, i.e. reference number together woth project name but no success.

Any idea?

Have a nice weekend!

mvh

Kundan.

[Ali_Pentico]

Hi Kundan.

You can use the CONCAT function to combine the reference # and project name. Below I have the two separated by " - " but you can change that to what works for you in the valueexpression line.

displayname=Reference + Name

linked.valuefield=name

linked.valueformat=HTML

namekey=name

querysort=name

textmode=true

valueexpression=CONCAT({referenceNumber}," - ",{name})

valueformat=HTML

I am not sure if you still want to restrict to the first 10 characters as well.

• This option restricts to 10 characters including the reference #. This results in very little of the actual project name.

valueexpression=LEFT(CONCAT({referenceNumber}," - ",{name}),10)

• This option would restrict only the project name to 10 characters and then add the reference number to the front.

valueexpression=CONCAT({referenceNumber}," - ",LEFT({name},10))

Hope this helps.

-Ali

[Kundanism]

Excellent and a great help @Ali Pentico‚ .

I am testing this and will inform the result soon.

Have a nice evening!

Mvh

Kundan.

[Kundanism]

Hi @Ali Pentico‚

Thank you, it works as you mentioned.

Have a nice day!

Mvh

Kundan.