Jan 07 2021 10:11 AM
Jan 07 2021 10:11 AM
I have an issue in that I need to display a custom text field as if it were a number. To give some background we have a global currency set to GBP in our Project Online environment. However we want to have a custom field that shows a conversion to USD based on the value in another field which is the conversion rate (ROE). We can't use another cost field and a number field doesn't allow the display of "$" symbol. So I thought it might be possible to show this in a Text field concatenating the "$" with the calculated value.
"ROE" is the Rate of Exchange Value (typically 1.3)
"OV Budget (£)" is a custom cost field for entering an overall budget value (in GBP)
"$" & ([ROE] * [OV Budget Estimate (£)]) is the formula I am using in another custom text field to display the converted value in US dollars.
However, the format doesn't show the number of decimal places correctly and it doesn't show the "," in the same way that a normal cost field would. And that's where I am getting stuck and need some help! How would I resolve this please ?
Thank you! :)
Jan 07 2021 02:43 PM
Hi John, Thank you so much for getting back to me.
However I could not get this formula to work. When I set up a new Enterprise Custom Field in PWA, and create the new field as a Project level, text field and go down to the formula section and paste in the formula you indicated, I get an error "The formula contains errors. Please correct the formula and try again"
I also tried simply dropping the "Text1=" part of the formula since I don't believe that's required in the initial part of the formula.
Can you advise further ? Have you tested this in PWA yourself ?
Thanks so much
Jan 07 2021 03:00 PM
That's right, the "Test1 =" is not part of the formula. Also, I assume you renamed a Number field as "ROE" and a Cost field as "OV Budget..." and not simply changed the column title. To be sure, you might try using the actual field names in the formula (e.g. NumberX and CostX) and see it that works.
Unfortunately I use Project desktop so I can't help with PWA. If you did rename the fields and removed the "Text1 =" but it still fails, then I suspect the issue has to do with how you set up the formula in the enterprise environment, and for that, I'll have to call in someone with Project Online experience.
Jan 07 2021 03:16 PM
Actually after a bit of playing I did get it to work, sort of....
Project Online requires a specific reference to the named custom fields so that's what I have in the formula (doesn't work quite the same way as Project Desktop in that sense).
So now the format of the resulting value shows as $546,000. instead of $546,000.00 - Any other thoughts ?
Jan 07 2021 04:02 PM
I got it working in the end. I couldn't get this part of the formula to work properly "$#,#.##" because it resulted in a value in the form $589,000.
but when I used "$#,#.00" it resulted in the format $589,000.00 which is good enough.
Thanks so much for your help.