Computed Fields and Picklists

[ThomasRambach]

I have this computed field value:

CASE WHEN (CHARGE_NUMBER) THEN ((CHARGE_NUMBER_PROJECT) || ' ' || (CHARGE_NUMBER_DESCRIPTION))
ELSE ''
END

The issue is CHARGE_NUMBER_PROJECT is a derived field that is also a picklist from the CHARGE_NUMBER workspace. So the result is the Item ID# gets returned for CHARGE_NUMBER_PROJECT instead of the actual Project string value.

How can you convert a picklist in a computed field to the string value?

[tony.mandatori]

Have you tried creating another field in the workspace to hold the string value?

You could do this by using a Derived Field which points to the string values.

[philipfrench]

If you're using static picklists, you get an integer returned.

It looks like the index of the entry in the picklist, but it's not actually. The first time a picklist value is chosen and saved on an item, at this point it seems to get an index (one more than the last one used).

Anyway, you can use this index to extract out a value from a delimited string. See the following example, using the COMPLETENESS picklist index value to extract eg "RED" and "20%" from the two strings:

'<span style="background:'||split_part('RED~RED~YELLOW~YELLOW~GREEN~GREEN', '~', COMPLETENESS)||'; font-weight:bold; color:black; width:30px; float:left; height:25px;">'||split_part('0%~20%~40%~60%~80%~100%', '~', COMPLETENESS)||'</span>'

For a picklist with a workspace source, you get the dmsid of the item.

If it's a very small reference-type workspace (behaving almost like a static picklist) you can use the above technique, with a delimited list of dmsids in the split_part() argument. But its a limited case, not much use.

Perhaps you can use an on-edit to copy the referenced value into an admin field, and then have the formula reference that?

Derived fields should work? You get a string passed into your formula.

Hopefully something above helps.

[ThomasRambach]

There is 3 workspaces involved. Projects, Charge Numbers and "Project Items". 

In the charge number workspace, their is a picklist to pick the associated project. So in the Project Items workspace, their is a field to pick the charge number and another derived field that shows the associated project. I don't see an oppurtunity in the Project Items workspace to get a string value of the project name from the selected project in the charge numbers workspace.

[ThomasRambach]

The picklist is of projects which would number in the 100's to 1000's and constantly grow.

[tony.mandatori]

If you can get the values from derived fields, I would then use a script to collect the values that are needed (I think Phil was suggesting the same thing). It would look something like item.picklist.attributeFromOtherWorkspace.

[ThomasRambach]

So I actually was already doing this through a script. The reason why I was trying to change to a computed field based on a  derived value was because the project name and other source fields could change names. It then gets extremely complicated to find the project items that need to get the string based fields updated to match the new values. 

It's now hours of work because FLC doesn't allow something simple as a convert a picklist value to a string. 

[ThomasRambach]

It's also worth mentioning this is all a workaround because I want to be able to have a single picklist for items in multiple workspaces in a gridview. Currently not possible in FLC. 

[philipfrench]

I think you might get doubly-derived values populating two levels, perhaps with some clever use of a descriptor on the intermediate item, but I suspect you'll be pushing your luck even if it appears to work. Just my 10 cents.

I think scripting is your best bet. And if you're doing scripting, then perhaps you could use the script also to avoid your computed field with it's case statements.

I had a customer that built something like you're describing. And it sort of worked, but occasionally got out of sync for reasons that we could not understand. They bolstered the deployment with a utility to batch-run a on-demand script on all the items, to navigate and fix any discrepancies. They ran that every night on a windows schedule.

Not saying this is ideal, but it was a 'get out of jail' card, and at least we could debug it.

[tony.mandatori]

You can get a combined report by using something like PowerBI to query FLC.

PowerBI is included in Office365.

Would that help?

[ThomasRambach]

This is what I'm trying to accomplish. I'm 95% there, just a few FLC quirks to work out. It's a timecard workspace for workers to pick items from FLC and enter the number of hours they worked on it. After the worker submits, it pulls in the last column which is the actual charge number to use. The Ledger items are in a separate workspace that combines and filters all the items they can choose from throughout the entire FLC system.

It all needs to reside within the confides of FLC.

[tony.mandatori]

The PM tab might provide a more out-of-the-box solution for this.

You can select items from various workspace to include on the PM tab.  Tasks have a duration as well (although not hours).