Set content item field value based on another field value in the same content item

We’re using v6.5.2, in case that makes a difference.

The scenario:
[li]We have a content type Product defined that has two fields:
[li]ProductID is a sys_DropDownSingle that has choices coming from an external datasource
[/li][li]ProductID also has a ReadOnly rule set to Modify Only
[/li][li]ProductName is an EditBox
[/li][li]ProductName has a Visibility rule set to Modify Only

When the user creates a new content item of this type, the ProductID field shows the IDs read from the external datasource, and they do not see the ProductName field, as desired. The user selects a value from the ProductID drop down, and clicks Insert.

The question:
What I’d like to have happen is that at some point, the system looks up the current ProductName value from the same external datasource using the ProductID as the lookup key, and populates the ProductName field with that value. I’d like that to only happen if the ProductName field is empty. Is there any way to have that happen either a) when the content item is created or b) when the content item is modified/saved? Solution can be either out-of-the-box or not; I’d like to know the “right” answer(s) either way

I’ve tried searching around these forums for a solution, and I feel like I’m getting bits and pieces of what I need to know, but not a whole answer. Thanks.

Just looking at it really quickly…

You could try the field input transformer “sys_TranslateJexlExpressionValue.”

Set the value parameter to the ProductName field.
Set the expression parameter to something like:

$rx.cond.choose( $value, $value, $rx.db.get("[i]ExtDatasource[/i]",
"select * from [i]ExtTable[/i] where ProductID = '$sys.item.getProperty('rx:sys_contentid').String')

You’ll probably need to fiddle with that a bit to make is solid under error conditions and take care of any string-to-number conversions appropriate for your database backend.

Ah… I just noticed you’re on 6.5… um… this exit may not be available as I took my reference materials from the 6.7 docs.

Let me know how it works, as I’ve never used this one before.

Thanks for the quick and clear response. As you say, I don’t know if this is available on 6.5.2, but it gives me a new direction to look at this from nonetheless. I’ll post back with what I find as I look into this more.

If anyone has other ideas, that are known to be supported in 6.5.2 or that take another approach to this, I’d be glad to hear them as well.

Well here’s one odd thing…

The sys_TranslateJexlExpressionValue is available under the Extensions section of input transformations in 6.5.2. When creating one, you fill in the value and the expression parameters as described. However, when you click Add to add the transform to the field, it just turns it into a SetField transform with the expression parameter as the parameter. Whatever was put in as the value parameter is lost.

Is this standard behavior, or is this a bug? If it is standard behavior, what’s the logic here? And what, if anything, am I doing wrong here?