I've a question about comparing against picklist fields in computed fields.
The TLDR version: It appears I can get the *index* of a picklist field (that uses a user defined picklist) for use in a computed field. Can I get the picklist field's *value* instead?
Here's the context:
In the default tenant workspaces there are several instances where a field's background color is changed depending on other fields. This is done using a CASE statement, like so:
WHEN (NEXT is null) THEN
[do something here]
What if we want to evaluate multiple conditions as part of the CASE statement? That's pretty straightforward, we can just add in an AND keyword and the additional condition, like so:
WHEN (NEXT is null AND [additional condition]) THEN
[do something here]
But what if the condition statement wants to check against the value of a picklist? What we finally puzzled out is that the 'value' of a picklist field actually reports as an integer value. This value appears to indicate the index of the selected value.
The index appears to be granted in the order the value was added to the picklist, not the currently sorted order. What we were specifically working on is the suppliers workspace, which has a field called CYCLE_MONTHS. The first value in the list, '00' is index 5. The second value, '12' is index 3. This was consistent in two different tenants. The default picklist has 10 total values. When I added an 11th value, its index became 11.
I'm confident I can use this for computed fields, but.... what if the picklist changes? Adding values would be no problem. But what about when values are removed? After a bit more testing, it appears that the indexes *do not* change - at least not immediately. As an example, I removed the value 18 (with index 1) from the cycle_months picklist. It *did not* change the index of value '00' (index 5).
Ok, all that being said...is there a way I can directly access the *value* of the picklist with a computed field? I'd really rather check against what I expect the field will show, not its index. I'm afraid that the index might change over time, while I would expect its value to be constant.
Solved! Go to Solution.
One thought I just had is if I could use a SELECT statement in the computed field to derive the actual value based on the index, but I don't know:
In a computed field you can only get access to the index. You are correct that these don't change so with a little investigation you can reverse the indexes back out to the values you need.
If you need something more complex you should use scripting. It has access to the values as well as a much more elaborate set of functions that can be used.
Thanks for the quick feedback Brian. Is there a good reference somewhere for syntax and what we can access via computed fields? I'm an experienced programmer, but I had to do a fair amount trial and error to get the syntax of the logic statements to work out in this case.
There isn't an exhaustive list of what's supported - You can basically say it's more limited in it's syntax than rich. Meant to do simple calculations for the most part (like LENGTH * WIDTH). But some additional things are supported. Here is an article that someone on the team came up with that might help
Log into access your profile, ask and answer questions, share ideas and more. Haven't signed up yet? Register
Start with some of our most frequented solutions to get help installing your software.