Cannot get derived fields to work properly

This is my first attempt at using Derived Fields in an IDO. I'm storing the Item number in my own table. Table alias is QITM (or qitm). I keep trying various permutations of this:

SELECT top 1 item.description from item WHERE item.item=QITM.Item

but no matter what I use, I keep getting syntax errors. I've looked at other Derived Fields like this one:

(select top 1 jobmatl.job from jobmatl where jobmatl.item = iloc.item and jobmatl.bflush_loc = iloc.loc)

so I think what I'm doing is right but I'm just not seeing why its failing.

Find more posts tagged with

General Discussions

Accepted answers

SXFlagMan

Try

(SELECT top 1 item.description from item WHERE item.item = Item)

Where Item is the property name (case sensitive) on the IDO for the item field on the qitm table. I can't remember but you may need the Item property on your form along with your new derived field.

0712131256598360.doc

All comments

unknown

Instead of making it a derived field, why don't you simply add your table QITM into this IDO. And then use the bound data for your column?

Or otherwise, you can make a database scalar function and in derived field use dbo.getQITMjob(itm.item)

Stephen Cena

I currently am storing the Description in my table/IDO. However, a user raised that the Description may change. The intent was to have it look up the correct Description real time.

Additionally, I want the practice writing a Derived Field

SXFlagMan

0712131256598360.doc

Rees Howel

I agree with 256334. SQL is not case sensitive but IDOs are. Also you need the surrounding brackets.

Stephen Cena

I swear I tried that combination, but can't argue with the results

Thank you! Hopefully I can carry this forward in other cases.

Expense_Management Roadmap - October 2020.pdf

unknown

Another option for lookup values is to use validators. For the description lookup of the item, you could use a validator similar to ItemDesc to return the value to a variable or component. This is used in many areas of CSI where an initial value is desired that can be overridden by the user. This blog posting ( http://www.jordanfey.co.uk/?p=1029 ) gives a good demonstration of how validators can be used to set a value on your form.

Copyright © 2025 Infor. All rights reserved.