I have two columns called RIGHTS_HOLDER and CLEARED_BY
The RIGHTS_HOLDER value is linked to another table in order to allow a drop-down selection upon entry. (I know I can do this within the column editor, but the external list is inherited as a table).
I am trying to write a transform for the CLEARED_BY column that will refer to the RIGHTS_HOLDER column. But as the latter is a linked column, the script never resolves correctly.
return "X" if $RIGHTS_HOLDER == "Y" else $CLEARED_BY
This formula works if I reference a non-linked column. But I think the fact that $RIGHTS_HOLDER is a linked column, I need to escape the “Y” somehow.
I should add that the above script does not return any errors. It simply doesn’t recognize the Y value in the referenced column and so it resolves to the [else] clause.
First, is a transform your desired outcome? A transform is basically a one-time transformation of the data in a column. IE if you wanted to add 10 to each cell value and only have the resulting value stored in that cell. The transform won’t happen again unless you reinput a formula in the transform. I have a feeling you’re wanting to use a trigger formula.
Values in a reference column are stored in a list of lists, meaning you have to use python list comprehension to do any operations on a cell value. There are a number of different discussions on reference columns and how to work with the data stored in them.
yes, the transform is being used to clean up imported data one time. I’ll keep looking for the discussions on reference columns and how to work with data stored in them… but I certainly didn’t see anything when searching from the side of a Transform.
In a formula, a reference column will resolve to a Record object, not to the value that you see, visually, in the cell. This means you have to point out explicitly which field in the referenced record you want. Suppose $RIGHTS_HOLDER is a reference column pointing to the RightsHolders table, and that table has three columns last_name, first_name, holds_rights; then your formula would have to read:
return "X" if $RIGHTS_HOLDER.holds_rights == "Y" else $CLEARED_BY
Now, I just wish the Transform tool had a larger text box… anything longer than a couple of lines becomes quite cumbersome to edit in the scrolling window. But alas, it’s a minor complaint.