I wouldn’t use scripting or turn the dataset on its side. We have a lookup function in our expression language that you can use. So your example:
SELECT Translation FROM table WHERE Code = 'W'can easily be the following expression:lookup({Path/To/Dataset}, "W", "", 0, 1)It will return an empty string if “W” is not found. I would recommend putting these translations into a Client dataset tag so it is available everywhere.
I believe your use of the word “script” is throwing me off. Typically in this context that refers to Python scripting, not expressions, or SQL queries.
No, it only works on bindings. In that case your best bet is to just loop though the dataset. You can make a global function (in the Script Modules) to do the lookup. In the designer, click on Project > Script Modules. Add a new package called “ds”. Click on the new package and add the following script:def lookup(dataset, code):
import system
data = system.dataset.toPyDataSet(dataset)
for row in data:
if row[0] == code:
return row[1]
return ""You can call this in your script:translation = app.ds.lookup(path.to.dataset, "W")Just substitute the path.to.dataset to either a path to the dataset on the window or getting a tag value if you made a client dataset tag.