Profound Logic Forums

Hello Scott

I am trying to use this option of course with a twist. Can i use the choices options field for both SQL and displaying a selection drop down list? currently we have RTRIM(VNAME) || ' {' || VEND# || '}' in choices options field, we would like to keep this feature. I have 2 tables one for the vendor name and one for the vendor status.
A R $VENDRM TEXT('VENDOR MASTER FILE')
A VEND# 6 0 COLHDG('NUMBER')
A VNAME 35 COLHDG('NAME')

A R $VENDRME TEXT('VENDOR EXTENDED')
A VEVEND# 6 0 COLHDG('NUMBER')
A VESTAT 1 COLHDG('STATUS A/I')

i would like to only select those records that do not have a status of "I", i would also like to present a drop down list of "Active" vendors.
here is what i came up with but it does not seem to work

Choice DB file : VENDRM AS A
choice options field: RTRIM(VNAME) || ' {' || VEND# || '}'
choice value field: (SELECT B.VESTAT FROM VENDRME AS B WHERE A.VEND# = B.VEVEND#)
choice selection criteria: B.VESTAT <> 'I'

i did check PUISSNVP and no new records were added to that table, it leads me to think i did not do it right.

any insight you have would be helpful.

You've coded a subselect, but you are trying to treat it as a join.

I don't think it's possible to do a join inside the fields for a dropdown. If its important to use a join, you'd have to create a view that does the join and use that in your db-driven field properties.

Or, instead of using a join, use another subselect in the 'selection criteria'.

Hello Scott

sorry I wasn't thinking clearly yesterday. Very simple change when I thought of it, use the power of SQL grasshopper!

choice database file: VENDRM, VENDRME
choice options field: RTRIM(VNAME) || ' {' || VEND# || '}'
choice value field: VEND#
choices selection criteria: VESTAT <> 'I'

very flexible!

i forgot important part of code
choices selection criteria: VESTAT <> 'I' and VEVEND# = VEND#

Thanks for your insight Scott very helpful!!